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
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
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
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. :)
(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))
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.
8 people are following this question.
SSIS Scale Out and its services on local group policies
Where do I download SSDT July 2016 Update (ver. 14.0.60629.0) from?
we hope Microsoft can develop databases like mysql
integration services ne figure pas dans la liste des fonctionnalités partagées de sql server
SSIS - getting data from Oracle - not working preview and metadata refresh