When converting a string of numbers to a date you will need to enter the dashes or hyphens in the string before you run the convertion through a derived column transform. Here is the string I am passing in: 20090321 The derived columns has the following code: (DT_DBDATE)(SUBSTRING(stringdate,1,4) + "-" + SUBSTRING(stringdate,5,2) + "-" + RIGHT(stringdate,2)) The end result is a date as a date datatype.
Read More
I was recently asked how to convert a string to a date and if the date is invalid make it a null date value. This can be done with one derived column transform in SSIS. Here I have a table with one varchar column named StringDate, one row with a date, one without. In my SSIS package I have an OLEDB source in a data flow and a derived column next with a terminator destination. This destination is available in Task Factory . The derived column has one expression in it to create a new column called...
When working on an SSIS package you may need to go back to the source of a Data flow and make changes. When you do this the other transforms may get errors about the column names not matching. You may have to update each transform. Most of the time you can just open the transform and click the option to map to the new column names. But you may get an error of "No Object Exist with Lineage ID of XXXX" even after fixing the metadata issues. To fix this simply place the square brackets around the c...
When you need to convert a date in SSIS, the expression language can do this for you. I was working with a client and I needed the date to be a string and an integer due to some date type issues on with other tables. I converted the date to an integer with the format of YYYYMMDD with the following expression: (year(@[User::dtStartDate]) * 10000)+ (month( @[User::dtStartDate]) * 100)+ Day( @[User::dtStartDate]) Notice I am multiplying the year and the month by 10,000 and 100 respecfully, this add...