question

TadishettySandeep-8327 avatar image
0 Votes"
TadishettySandeep-8327 asked JeffreyWilliams-3310 answered

Convert dd-mmm-yy flat file to SQL table where column has datetime as data type using SSIS


I am getting the data in source file as dd-mmm-yy and I need to load the data to SQL table where the column has datetime datatype. Please let me know the conversion.

Flat File Source Row:

08-AUG-21

OLEDB Destination Row:

Datetime column

Thanks

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered ZoeHui-MSFT commented

Hi @TadishettySandeep-8327,

Use a Derived Column with below expression and rename the column as 'yourdatestring'.

 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(yourdatecolumn,"JAN","01"),"FEB","02"),"MAR","03"),"APR","04"),"MAY","05"),"JUN","06"),"JUL","07"),"AUG","08"),"SEP","09"),"OCT","10"),"NOV","11"),"DEC","12")

And then add the resulting column(yourdatestring) to a new Derived Column with expression.

 (DT_DBTIMESTAMP)(SUBSTRING(yourdatestring,7,4) + "-" + SUBSTRING(yourdatestring,4,2) + "-" + SUBSTRING(yourdatestring,1,2))

The result is like shown.

118921-2.png

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October




2.png (30.7 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image ZoeHui-MSFT TadishettySandeep-8327 ·

Hi @TadishettySandeep-8327,
Do you have further question on this , could we offer more support?
If this helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

0 Votes 0 ·
RyanAbbey-0701 avatar image
1 Vote"
RyanAbbey-0701 answered TadishettySandeep-8327 commented
 (DT_DBTIMESTAMP)("20"+SUBSTRING(<date col>,8,2) + "-" + RIGHT("00"+(DT_STR,2,1252)TOKEN("JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC", "|", SUBSTRING(<date col>,4,3)), 2) + "-" + SUBSTRING(<date col>,1,2))
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Good answer, +1 from my side!

1 Vote 1 ·
JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered

That format converts implicitly to a datetime data type in SQL Server. You could just define it as a string of 9 characters and pass it directly to the datetime destination - and it will file correctly. Adding a transformation when the data can be natively interpreted is just adding CPU cycles that are not necessary.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.