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

Question

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

Answer 1

Hi @Tadishetty, Sandeep ,

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.

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

Answer 2

(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))

Answer 3

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.