Import String Type Datetime through SSIS.(dd-MMM-yy hh.mm.ss.fff )


When you import a string type datetime to a SQL datetime column, you have to manipulate input string to follow ISO format of YYYY-MM-DD to pass the value through a derived column.
You can find a detailed posting from this posting:
http://toddmcdermid.blogspot.com/2008/11/converting-strings-to-dates-in-derived.html

Sometimes, the expression can get really complicated.
The expression looks like:

((“20” + SUBSTRING(dt,8,2) + “-” + (UPPER(SUBSTRING(dt,4,3)) == “JAN” ? “01” : UPPER(SUBSTRING(dt,4,3)) == “FEB” ? “02” : UPPER(SUBSTRING(dt,4,3)) == “MAR” ? “03” : UPPER(SUBSTRING(dt,4,3)) == “APR” ? “04” : UPPER(SUBSTRING(dt,4,3)) == “MAY” ? “05” : UPPER(SUBSTRING(dt,4,3)) == “JUN” ? “06” : UPPER(SUBSTRING(dt,4,3)) == “JUL” ? “07” : UPPER(SUBSTRING(dt,4,3)) == “AUG” ? “08” : UPPER(SUBSTRING(dt,4,3)) == “SEP” ? “09” : UPPER(SUBSTRING(dt,4,3)) == “OCT” ? “10” : UPPER(SUBSTRING(dt,4,3)) == “NOV” ? “11” : UPPER(SUBSTRING(dt,4,3)) == “DEC” ? “12” : “00”) + “-” + SUBSTRING(dt,1,2)) + ” ” + REPLACE(SUBSTRING(dt,11,8),”.”,”:”) + “.” + SUBSTRING(dt,20,3))

There is another option to use a stage table or a stage column in your target table.
First, you import the string to varchar column and update the target column with converted the datetime string through T-SQL.
In this case dd-MMM-yy hh.mm.ss.fff (i.e. 14-DEC-2016 06.23.37.000):

  
  
 ---The update statement:

update targettable
Set dt = cast(Replace(dtstring,'.',':') as datetime) ; 

 

We design two steps within SSIS package,

Use a Sequence container 1 to use Data Flow Task to import text file to target table directly;
Use second Sequence container to Execute T-SQL Statement Task with the above Update statement.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f576e695-c398-4301-8708-6d5dc5463228/14dec16-062337000-string-is-in-csv-file-and-insert-as-datetime-in-sql-table-using-ssis?forum=sqlintegrationservices#e1aa5e09-07e9-45c5-aa26-2853831f3f45

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s