Loading a varchar/datetime value from a pipe delimited file to SQL table
Hey all, I have a pipe delimited file that I get, and I am trying to load it in to a SQL table. The file looks like this: 19,23,A,20130615062429 I have created my SQL table with 4 columns with int,int,char,datetime datatype respectively. I am not sure, if the value that I am getting in file is datetime/varchar. The package runs fine when I use a varchar datatype in my table (and not datetime). I run in to an error, If I use "datetime" as my datatype. Any ideas/suggestions?
November 7th, 2012 1:52pm

You must implement logic to convert '20130615062429' value into '2013-06-15 06:24:29' format then it should work datetime datatype column http://uk.linkedin.com/in/ramjaddu
Free Windows Admin Tool Kit Click here and download it now
November 7th, 2012 2:21pm

Add a Derived Column Transformation and in it override the column in question with the following expression (SUBSTRING( [MyColumn], 1,4) + "-" + SUBSTRING( [MyColumn], 5,2) + "-" + SUBSTRING( [MyColumn], 7,2) + " " + SUBSTRING( [MyColumn], 9,2) + ":" + SUBSTRING( [MyColumn], 11,2) + ":" + SUBSTRING( [MyColumn], 13,2)) You may need to add a conversion to date time. e.g. (DT_DATE) in front. Arthur My Blog
November 7th, 2012 2:48pm

Right Sir ! This is what i used: (DT_DATE)((SUBSTRING([Column 1],1,4) + "-" + SUBSTRING([Column 1],5,2) + "-" + SUBSTRING([Column 1],7,2) + " " + SUBSTRING([Column 1],9,2) + ":" + SUBSTRING([Column 1],11,2) + ":" + SUBSTRING([Column 1],13,2))) Thank you so much.
Free Windows Admin Tool Kit Click here and download it now
November 7th, 2012 4:23pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics