SSIS Flat File Mapping - DATETIME Conversion Error

Question

SSIS Flat File Mapping - DATETIME Conversion Error

ShuaibV

SSCrazy

Points: 2001
More actions
May 12, 2017 at 6:14 pm

#337835

Hi
I have a flat file (pipe Delimited) which contains Datetime field (Format: 2017-04-04T21:46:17.931+00). I have defined this column "database timestamp with precision [DT_DBTIMESTAMP2]" in Source Flat File Connection. For the empty rows, i see the Data as "0001-01-01 00:00:00 +00:00" and this date value is not valid in SQL. Can some provide input how to handle (define proper datatype in source file) this scenario to successfully load the data? The destination table data type for this field is DATETIME.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

ShuaibV - Friday, May 12, 2017 6:14 PM
Hi
I have a flat file (pipe Delimited) which contains Datetime field (Format: 2017-04-04T21:46:17.931+00). I have defined this column "database timestamp with precision [DT_DBTIMESTAMP2]" in Source Flat File Connection. For the empty rows, i see the Data as "0001-01-01 00:00:00 +00:00" and this date value is not valid in SQL. Can some provide input how to handle (define proper datatype in source file) this scenario to successfully load the data? The destination table data type for this field is DATETIME.
Thanks

You could use a Derived Column to send a NULL or something else instead of that value using a conditional expression.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

drew.allen SSC Guru Points: 76985 More actions · Answer 2

ShuaibV - Friday, May 12, 2017 6:14 PM
Hi
I have a flat file (pipe Delimited) which contains Datetime field (Format: 2017-04-04T21:46:17.931+00). I have defined this column "database timestamp with precision [DT_DBTIMESTAMP2]" in Source Flat File Connection. For the empty rows, i see the Data as "0001-01-01 00:00:00 +00:00" and this date value is not valid in SQL. Can some provide input how to handle (define proper datatype in source file) this scenario to successfully load the data? The destination table data type for this field is DATETIME.
Thanks

That is a valid DATETIMEOFFSET value in SQL. Try using DT_DBTIMESTAMPOFFSET instead.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ShuaibV SSCrazy Points: 2001 More actions · Answer 3

i have set source file column data type as DT_WSTR (50) and destination table (SQL) data type as DATETIMEOFFSET. i have tried using Derived column to convert to datetimeoffset and getting the error.
i have the value in source file as "2017-04-04T21:46:17.931+00:00", the value could be empty for some rows in the input file. i want to store the same value in SQL server.
Any input is greatly appreciated...

Tom_Hogan Hall of Fame Points: 3895 More actions · Answer 4

I find this piece of documentation extremely helpful when dealing with SSIS data types.
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/integration-services-data-types

Check out the sections on converting. I think you need to define it as DT_DBTIMESTAMPOFFSET.

ShuaibV SSCrazy Points: 2001 More actions · Answer 5

Thanks Everyone. I have set the Datatype to VARCHAR(37) at destination and able to resolve this issue.