SSIS SQL 2016 failing to import date value into a timestamp column!

Question

Post reply

SSIS SQL 2016 failing to import date value into a timestamp column!

datsun

SSCrazy

Points: 2397
More actions
September 3, 2019 at 5:39 pm

#3676966

Hello,
Why am I getting this error? I have a CSV file with a date field in yyyy-mm-dd hh:mm:ss
Importing in the table via Fast load to a fieldtype: datetime column (called timestamp) is failing.
The input is DT_STR (50) (string) - ouput is a datetime.
This happily works on SSIS 2008 R2 but not on SSIS 2016.
[OLE DB Destination [92]] Error: There was an error with OLE DB Destination.Inputs[OLE DB Destination Input].Columns[timestamp] on OLE DB Destination.Inputs[OLE DB Destination Input]. The column status returned was: "The value could not be converted because of a potential loss of data.".
[OLE DB Destination [92]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "OLE DB Destination.Inputs[OLE DB Destination Input]" failed because error code 0xC0209077 occurred, and the error row disposition on "OLE DB Destination.Inputs[OLE DB Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
Many thanks,
Vinay

Viewing 15 posts - 1 through 15 (of 15 total)

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

What is the default language for the server?

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

Thom A SSC Guru Points: 99130 More actions · Answer 2

Jeff Moden wrote:

What is the default language for the server?

Based on the OP's prior question here, looks like it is BRITISH, which means that the date string the OP has (yyyy-MM-dd hh:mm:ss) will be interpreted as yyyy-dd-MM hh:mm:ss; because datetime doesn't follow the same rules as the rest of the newer date/time data types.

Several solutions here:

The first is use an unambiguous format, such as yyyy-MM-ddThh:mm:ss (yes simply adding the T makes it unambiguous).
Change from a datetime to a datetime2(0), as the format you are using is unambiguous with the newer data types (and you don't appear to need accuracy to 1/300th of a second).
Use American (ENGLISH) as your language for running the SQL, as then the value will be interpreted as yyyy-MM-dd hh:mm:ss with the datetime datatype.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Heh... in other words, "Duplicate post". Thanks, Thom.

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

Thom A SSC Guru Points: 99130 More actions · Answer 4

Jeff Moden wrote:

Heh... in other words, "Duplicate post".

Pretty much. 🙂

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

David Burrows SSC Guru Points: 65142 More actions · Answer 5

Jeff Moden wrote:

What is the default language for the server?

Don't you mean login as the default language of the login dictates date conversion as described by Thom and could be different from the server default.

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

datsun SSCrazy Points: 2397 More actions · Answer 6

Hi Thom,

This is not exactly a duplicate post. The previous one was for t-sql related for another server 2016.

This issue is of SSIS (Integration - import from a CSV).

My login is British English. Server default lang. is English (USA).

I can't change the field type from datetime to datetime2 as the import is to be continued on the original table after we move to SQL 2016.

datsun SSCrazy Points: 2397 More actions · Answer 7

To all,

Apparently, the import failure had nothing to do with dateformat. Although the error was showing timestamp related, the issue was completely a different one.

The CSV file when copied from one source to destination folder using COPY batch command adds an end-of-file marker called SUB control character. (ASCII 26) . SSIS was attempting to import this end marker line with nothing in the timestamp column and a blank/null value was being rejected.

To resolve this, use COPY with /b switch at the end of the command line. This will not add the SUB character. Still retains LineFeed/newLine chars.

Why the newer SSIS is reading end of file marker for import, is itself a mystery.

SQL 2008 R2 SSIS is happily importing the same file with the marker in.

Vinay

Thom A SSC Guru Points: 99130 More actions · Answer 8

Why were you using the COPY command at all? Why not use a File System Task for copy a file?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

datsun SSCrazy Points: 2397 More actions · Answer 9

This reply has been reported for inappropriate content.

Thom,

It's all part of a big batch file. A zip file comes in from FTP, extracts it, copies it where SSIS filemanager reads it from and then the processing continues. never knew a simple copy command can have a bigger implication at the SSIS CSV import.

Vinay

David Burrows SSC Guru Points: 65142 More actions · Answer 10

I am not aware of a single copy causing this unless it is propriety.

I know DOS COPY using + to concatenate files certainly does (and always has done)

ie COPY file1+file2 file3 will cause a SUB to be added to end of file3.

As you stated /b after the destination file will prevent this

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

datsun SSCrazy Points: 2397 More actions · Answer 11

David,

DOS/Windows commandline 'COPY' does the end of marker addition (SUB). which is then used in the Batch file.

Vinay

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 12

Just an FYI - but everything that batch files does can be done in SSIS.

For FTP/sFTP - you can use WinSCP and a script component to download files.

For ZIP files - you can use .NET 4.5 or higher in a script component.

To copy files - you can use the built in task or a script - I prefer using a script to 'move' files. In a script I copy the file and then delete the original for archive processes. If the destination folder is compressed - and you move a file to that compressed folder - it will not be compressed as it retains the values, but a copy is compressed.

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: 1004363 More actions · Answer 13

Jeffrey Williams wrote:

Just an FYI - but everything that batch files does can be done in SSIS.
For FTP/sFTP - you can use WinSCP and a script component to download files.
For ZIP files - you can use .NET 4.5 or higher in a script component.
To copy files - you can use the built in task or a script - I prefer using a script to 'move' files. In a script I copy the file and then delete the original for archive processes. If the destination folder is compressed - and you move a file to that compressed folder - it will not be compressed as it retains the values, but a copy is compressed.

Why do you have to write all the script components? I thought SSIS was supposed to make it so you basically wouldn't have to write anything.

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

Thom A SSC Guru Points: 99130 More actions · Answer 14

Jeff Moden wrote:

Why do you have to write all the script components? I thought SSIS was supposed to make it so you basically wouldn't have to write anything.

I don't know if it's "supposed" to Jeff, but the truth is far from that. I have plenty of Script tasks and Components in my projects.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk