text column in date format in excel to datetime column in sql server via SSIS

Question

Post reply

text column in date format in excel to datetime column in sql server via SSIS

kirti

Old Hand

Points: 387
More actions
July 10, 2021 at 3:20 pm

#3906456

Hi,
I am trying to import an excel with a date column in it ( format - dd.mm.yyyy hh:mm:ss) . The column does not have any date specific custom formatting. It is general format only. The sql destination column is datetime. I have tried using data conversion as well as derived column to convert STR into datetime but it throws me error:
"INDUCED TRANSFORMATION FAILURE ERROR". Any idea how to resolve this?
Is it not possible to convert a simple STR column with date in it into datetime for the destination?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 1

I see a couple of options:

Use SSIS to modify the string to match the format expected in SQL Server. The ideal format would be a non-ambiguous format such as 'yyyymmdd hh:mm:ss' or 'yyyy-mm-ddThh:mm:ss'. To do that in SSIS you need to parse each part and then put it back together - which can be done using TOKEN.
Insert the data into a staging table - then use convert in SQL Server to convert the value. This can be done using CONVERT(datetime, yourDate, 104).

In SSIS - the expression would be something like: TOKEN([DateColumn], ":", 3) + "-" + TOKEN([DateColumn], ":", 2) + "-" + TOKEN([DateColumn], ":", 1) + "T" + TOKEN([DateColumn], " ", 2)

This assumes that all values in that column follow the expected format - if just one of those is actually set as 'mm.dd.yyyy hh:mm:ss' then the import will fail and/or you will get invalid results.

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

I don't know much about SSIS but there MUST be some easy way to do the following in SSIS just like it's easy in T-SQL.

    SET DATEFORMAT DMY;
 SELECT CONVERT(DATETIME,'15.03.2021 14:23:59');

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

I just checked the solution using TOKEN and my solution is almost a mirror of Jeffrey's:

left(token( @[User::strDate],".",3),4) + "-" + token( @[User::strDate] , ".", 2) + "-" + token( @[User::strDate] , ".", 1) + "T" +  token( @[User::strDate] , " ", 2)

Put this formula into a Derived column, which should have a datatype of string. Then map the column to your destination column.

(Replace @[User::strDate] with your column name.)

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 4

Phil Parkin wrote:

I just checked the solution using TOKEN and my solution is almost a mirror of Jeffrey's:
left(token( @[User::strDate],".",3),4) + "-" + token( @[User::strDate] , ".", 2) + "-" + token( @[User::strDate] , ".", 1) + "T" +  token( @[User::strDate] , " ", 2)
Put this formula into a Derived column, which should have a datatype of string. Then map the column to your destination column.
(Replace @[User::strDate] with your column name.)

I did not include the LEFT in mine - so mine wouldn't work as desired, but we can make a minor change and it will work:

TOKEN(@[User::strDate], ". ", 3) + "-" + TOKEN(@[User::strDate], ".", 2) + "-" + TOKEN(@[User::strDate], ".", 1) + "T" + TOKEN(@[User::strDate], " ", 2)

Notice in the first TOKEN we are using a period and space as the delimiters - TOKEN then breaks the string up so we have token 1 as DD, token 2 as MM, token 3 as yyyy and token 4 as the rest of the string following the space (since there are no more periods in the string). Using this construct - we can use TOKEN in the following manner:

TOKEN(@[User::strDate], ". ", 3) + "-" + TOKEN(@[User::strDate], ". ", 2) + "-" + TOKEN(@[User::strDate], ". ", 1) + "T" + TOKEN(@[User::strDate], ". ", 4)

Jeff Moden wrote:

I don't know much about SSIS but there MUST be some easy way to do the following in SSIS just like it's easy in T-SQL.
    SET DATEFORMAT DMY;
 SELECT CONVERT(DATETIME,'15.03.2021 14:23:59');

Since SSIS is not SQL - it isn't the same - and the code is not processed as SQL code. Since the data is in the file - you need to apply the function to each row in the pipeline (in the data flow task) and there we don't have the ability to call out to a SQL Server system for each row in the file.

In fact - we cannot assume that the source and destinations are SQL Server or in fact a database system at all. We would not want to build a process that calls out to a database system and runs SQL code once for each row in a file, that would definitely introduce performance issues.

As I stated before - the options are to either pass the string as-is to a staging table and parse it in SQL, or parse it into a string that SQL can natively convert to a datetime (or datetime2).

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Jeffrey Williams wrote:

Since SSIS is not SQL - it isn't the same - and the code is not processed as SQL code. Since the data is in the file - you need to apply the function to each row in the pipeline (in the data flow task) and there we don't have the ability to call out to a SQL Server system for each row in the file.

I'm certainly no expert on SSIS but that just adds another notch in my gunbelt against the use of SSIS. You mean to tell me that you cannot, in a blanket fashion, tell SSIS to use the DMY format for date import formats like you can for virtually everything else in SQL Server at the session level?

That just sucks. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frederico_fonseca SSCoach Points: 16167 More actions · Answer 6

Jeff Moden wrote:

Jeffrey Williams wrote:
Since SSIS is not SQL - it isn't the same - and the code is not processed as SQL code. Since the data is in the file - you need to apply the function to each row in the pipeline (in the data flow task) and there we don't have the ability to call out to a SQL Server system for each row in the file.
I'm certainly no expert on SSIS but that just adds another notch in my gunbelt against the use of SSIS. You mean to tell me that you cannot, in a blanket fashion, tell SSIS to use the DMY format for date import formats like you can for virtually everything else in SQL Server at the session level?
That just sucks. 😀

you can't - and if date format changes for any reason (and I do receive files from a system that keeps changing the format depending on which server generates the extract (US vs British format) then SSIS breaks.

and format also dependent on the regional settings of the user under which SSIS executes (Sql Server Agent Account normally - but not always)

see https://techcommunity.microsoft.com/t5/sql-server-support/date-format-issues-in-ssis-packages-between-mm-dd-yyyy-dd-mm/ba-p/318870

David Burrows SSC Guru Points: 65143 More actions · Answer 7

Jeff Moden wrote:

Jeffrey Williams wrote:
Since SSIS is not SQL - it isn't the same - and the code is not processed as SQL code. Since the data is in the file - you need to apply the function to each row in the pipeline (in the data flow task) and there we don't have the ability to call out to a SQL Server system for each row in the file.
I'm certainly no expert on SSIS but that just adds another notch in my gunbelt against the use of SSIS. You mean to tell me that you cannot, in a blanket fashion, tell SSIS to use the DMY format for date import formats like you can for virtually everything else in SQL Server at the session level?
That just sucks. 😀

It's just one of many limitations as with any software. In the past I created a derived column that rearranged the input into ISO format YYYY-MM-DD and got SSIS to cast that to DB-DATE, yeah it's a PITA but it works 🙂

Far away is close at hand in the images of elsewhere.
Anon.