question

TadishettySandeep-8327 avatar image
0 Votes"
TadishettySandeep-8327 asked Monalv-msft edited

Data conversion for DateTime column from excel to SQL in SSIS

Hi,

I am trying to load data from excel source where the date column has data like "Sun Mar 07 00:17:07 PST 2021" and I need to load it to SQL table with date time data type and my value supposed to be in following format mm/dd/yyyy hh:mm:ss. Please let me know the solution.

Excel Source Data:

Sun Mar 07 00:17:07 PST 2021

SQL Destination Table with datetime column:

mm/dd/yyyy hh:mm:ss


Thanks,
Sandeep

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.

1 Answer

Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @TadishettySandeep-8327 ,

We can use Derived Column and Data Conversion in ssis package.

Please refer to the following pictures:
76110-df.png

76148-excelsource.png

76167-derivedcolumn.png
Expression:
(DT_WSTR,10)(TOKEN(Col1," ",2) == "Jan" ? 1 : (TOKEN(Col1," ",2) == "Feb" ? 2 : (TOKEN(Col1," ",2) == "Mar" ? 3 : (TOKEN(Col1," ",2) == "Apr" ? 4 : (TOKEN(Col1," ",2) == "May" ? 5 : (TOKEN(Col1," ",2) == "Jun" ? 6 : (TOKEN(Col1," ",2) == "Jul" ? 7 : (TOKEN(Col1," ",2) == "Aug" ? 8 : (TOKEN(Col1," ",2) == "Sep" ? 9 : (TOKEN(Col1," ",2) == "Oct" ? 10 : (TOKEN(Col1," ",2) == "Nov" ? 11 : 12))))))))))) +"/"+(DT_WSTR,10)TOKEN(Col1," ",3) + "/" +(DT_WSTR,10)TOKEN(Col1," ",6) + " " + (DT_WSTR,10)TOKEN(Col1," ",4)

76168-dataconversion.png

76193-sqltable.png

Best regards,
Mona


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.





df.png (65.6 KiB)
excelsource.png (19.7 KiB)
derivedcolumn.png (19.2 KiB)
dataconversion.png (15.0 KiB)
sqltable.png (20.1 KiB)
· 1
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.

Thank you very much Monal and really appreciate your help. The above code works perfectly for me.

--Sandeep

0 Votes 0 ·