I seriously can’t remember the number of times that I have had to lookup date conversion logic in SSIS. Decided to create a cheatsheet for it.
|Date in Flat File||SQL Data Type||Data Conversion Transformation||Derived Column Transformation|
|yyyymmdd||date||DT_DBDATE (FastParse = True)|
|mm/dd/yyyy||date||(DT_DBDATE)(SUBSTRING([Col],7,4) + "-" + SUBSTRING([Col],1,2) + "-" + SUBSTRING([Col],4,2))|
|yyyy-mm-dd hh:mm:ss||datetime||DT_DBTIMESTAMP (optional)|
|yyyy-mm-dd hh:mm:ss.nnnnnnn||datetime2||DT_DBTIMESTAMP2 (scale = count(n)) (optional)|
Most solutions on stackoverflow would suggest that you use a derived column transformation to insert dashes to form a yyyy-mm-dd string and then cast it to DT_DBDATE. A much simpler way is to turn on a custom property called FastParse. (Thanks to my co-worker Lynn for passing on this tip)
FastParse can be found in Show Advanced Editor -> Input and Output Properties -> Data Conversion Output -> Output Columns
This is the simplest format to handle.
A data conversion transformation will do. It can be implicitly loaded into a date column as is.
This is locale-specific so
a data conversion transformation can easily handle it it can be implicitly loaded given that we are in Australia.
I have to say this is the most hated date format in the database world (this part of the world at least). Now that we are in Australia and it is a US date format, it cannot be converted directly using a data conversion transformation. Instead we need to rely on a derived column transformation to pull apart the date components and reconstruct into the yyyy-mm-dd format.
EDIT: Actually I later found that SSIS can handle both the dd/mm/yyyy and mm/dd/yyyy format as long as the days are larger than 12, even for dates with mixed format within the same column. Will write about this in a future post.
This is a perfect date time format that can be converted using a data conversion transformation. Just need to remember to use the DT_DBTIMESTAMP data type. Or, simply load into a datetime column as is.
This is the format for a SQL datetime2 data type. In order to do a direct data conversion, the scale needs to match the digits in the fractional seconds. Basically, no fractional seconds = no scale, 1 digit = scale of 1, 2 digits = scale of 2, and so on up to 7. Again, simply load into a datetime2 column as is.