August 14, 2020 at 12:51 pm
Hello,
In my source file having DateCreated column as ISO format string like '2020-08-05T05:50:49.844Z'. I want to convert from ISO date string to DateTime2 in SSIS. Please advice best way to achieve this.
Thanks,
Sabarish
August 14, 2020 at 2:05 pm
What's wrong with the all the existing conversion functionalities already within SSIS and SQL Server? Why didn't what you've already tried work? Where are you trying to perform this conversion, in a variable, a data flow task, within SQL, in a script task, elsewhere?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 14, 2020 at 2:45 pm
Thanks Thom!!
I am trying to convert in the DFT task, however I could see the below default data conversion, I am not sure how to convert from ISO format.
DT_DBDATE
yyyy-mm-dd
DT_FILETIME
yyyy-mm-dd hh:mm:ss:fff
DT_DBTIME
hh:mm:ss
DT_DBTIME2
hh:mm:ss[.fffffff]
DT_DBTIMESTAMP
yyyy-mm-dd hh:mm:ss[.fff]
DT_DBTIMESTAMP2
yyyy-mm-dd hh:mm:ss[.fffffff]
DT_DBTIMESTAMPOFFSET
yyyy-mm-dd hh:mm:ss[.fffffff] [{+|-} hh:mm]
Note : I can convert in SQL query however I am not sure how to achive in SSIS DFT task.
SELECT CONVERT(DATETIME2, CONVERT(DATETIMEOFFSET,'2020-08-05T05:50:49.844Z'))
Thanks,
Sabarish.
August 14, 2020 at 4:38 pm
Since you do not include an offset - you can convert directly to DATETIME2. For these types of columns - I would just define them as a string in SSIS and pass it through to the table as that string. The column would be defined as a datetime2 and SQL Server will convert it on insert.
If the values in the file cannot be converted - the package will fail and you can then identify the bad rows and address the issue with the sender.
Another option is to pass the data as a string into a staging table - then attempt to perform the conversion in SQL from the staging table to the final table. For those that fail you can then identify the bad rows and fix them in the staging table as needed. However, I prefer going back to the sender to make sure they fix the problems so I don't have to fix the bad data every time they send me a new file.
If the data actually contains an offset value - then in the SSIS connection manager for the file define the field as a string and use a data conversion task. In the data conversion task - replace the 'T and 'Z' with a ' ' and define it as DT_DBTIMESTAMPOFFSET. You can then map that new field to a datetime2 column in the destination table and SQL Server will convert it to the appropriate value.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply