Converting Data in SSIS

Question

This is probably an easy task in SSIS but having little experience with the tool and having failed to find the answer in the documentation, I turn to the experts here. I appreciate any and all help I might get.

I've got an SSIS package that reads the contents of an Excel spreadsheet and dumps the information into a database table. That's simple enough. However, the columns in the spreadsheet that are for dates will sometimes have a '0' zero in them. If the value is zero I need to transform it to the following date; '12/31/9999' and stick that value in the corresponding column instead.

I've currently got a Data Flow Task defined with an Excel Source and an OLE DB Destination. My assumption is that I need to put a Data Flow Transformation between those two tasks. I just can't find one that wants to work. I'm using SQL Server 2005 and Visual Studio 2005.

Thanks,

Andrew

Answer 1

I'm going to assume that your Excel data source has the column set up as a string. From there you should be able to use the Derived Column transform.

The syntax for it is a little screwy. This is what I was able to come up with off of the top of my head. I'll see if there is an equivalent to an ISDATE() function, since that would be more robust than just checking for "0".

my_string=="0" ? (DT_DBDATE)"12/31/9999" : (DT_DBDATE)my_string