SSIS data-conversion from string in txt to timestamp in table

[Michael843]

Hello!

I am experiencing a problem with the conversion of my DTS packages to SSIS packages (not really a conversion since I am just remaking them from scratch). I succeeded in converting the DTS packages into SSIS but when I went ahead and copied and adjusted it to a different txt and tbl I ran into a error. This specific txt had a timestamp inside it wich had to be converted into the table.

The problem:Some of the rows inside the timestamp column do not contain a timestamp but instead they are filled with spaces by the txt generator, if I try to convert this to timestamp to sent it to the table I get a conversion error on the rows filled with spaces!
In the DTS package this problem was solved using this ActiveX script:

If (IsDate(Trim(DTSSource("statdate")))) Then DTSDestination("statdate") = DTSSource("statdate") End If Main = DTSTransformStat_OK

If on the Flat File Source - Error Output I put the error output for the timestamp column on Ignore Failure it works perfectly fine! It sends the rows filled correctly to the table but the rows with spaces wich create a error he skips them and thus the value is replaced with NULL (wich I want!).

I need a method of using a expression/script/TRIM() function inside the Data Flow! I can't place a Script Task inside the Data Flow! This is only possible on the Control Flow but I need it to be in between my import and export in the Data Flow! - Please provide a solution to do this...

 

[Michael843]

I solved it Myself.

In the Flat File Source when making a new connection in the advanced tab I fixed all the data types according to the table in the database EXCEPT the column with the timestamp (in my case it was called "statdate")! I changed this data type to a **STRING** because otherwise my Flat File Source would give me a conversion error even before any scripts would have been able to be executed and the only way arround this was setting the error output to ignore failure wich I don't want. (You still have to change the data type after you set it to a string in the advanced settings by right clicking the flat file source -> show advanced editor -> going to the output colums and changing the data type there from Date to string.)

After the timestamp was set to a string I added a Derived Column with this expression to delete all the spaces and give it then "NULL" value:

TRIM(<YourColumnName>) == "" ? (DT_STR,4,1252)NULL(DT_STR,4,1252) : <YourColumnName>

Next I added a Data Conversion to set the string back to a timestamp. The Data conversion is finally connected to the OLE DB Destination.

I hope this helps anyone with the same problem in the future.

End result:

http://imgur.com/nJdqW39