How to: Load DATE Type Data by SSIS

We have discussed Implicit conversion from varchar to datetime by SQL Server, today I will show you how to take advantage of this kind of implicit conversion when loading date type data using SSIS. 

Problem

Every business needs time dimension to measure its profit or growth. Date-related columns can be birth date of persons, order dates or ship dates of products, prescription date, etc. However, date-related columns can be tricky to load because there seems always some data issues with them. For example, suppose we have a source file with PRESCRIPTION_DATE column in format of DD-MMM-YYYY  in source files. Some values are just empty or set as a dot (that might come from SAS) while the destination table defines this column as date type or datetime type.

How to load date-type columns?

Step 1. Define all date-related columns as VARCHAR in flat file connection manager and load all data  as VARCHAR first in Flat File Source.

Step 2.  Use derived column to clean up those dot values or other issues and to "transform" the data.

	Solution 1:	Define a new derived column [Conv_DateColumn] with expression set as below:  LEN(TRIM(PRESCRIPTION_DATE)) < 2 ? NULL(DT_DBDATE) : (DT_DBDATE)(SUBSTRING(PRESCRIPTION_DATE,1,2) + "-" + SUBSTRING(PRESCRIPTION_DATE,3,3) + "-" + SUBSTRING(PRESCRIPTION_DATE,6,4))
	Solution 2:	Define a new derived column [Conv_DateColumn] as a (DT_STR,10,1252), in format of "YYYY-MM-DD".

             

Step 3. When you do Mappings for OLE DB Destination Editor, map the [Conv_DateColumn] to the dateColumn with date or datetime as column type.

That's it!