INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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:

CODE -->

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...

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

(OP)
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:

CODE -->

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close