CODEWITHSUNDEEP
sqlsql-serverssisdatetimeoffset
Mayo
Mayo

Reputation: 10802

SSIS getdate into DateTimeOffset column - data value overflowed the type

I have an SSIS package. The source is a SQL query. The destination is a table. The package worked until I changed a column in a destination table from datetime to datetimeoffset(0).

Now, all records fail with a "Conversion failed because the data value overflowed the type used by the provider" error on this particular column.

The value in the source query is getdate(). I tried TODATETIMEOFFSET(getdate(),'-05:00') without success.

In fact, the only thing that has worked so far is to hard code the following into the source query:

cast('3/14/12' as datetime)

The only other interesting piece of information is that the package worked fine when running the source query against another server implying that maybe a setting is involved - but I see no obvious differences between the two servers.

Upvotes: 1

Views: 2651

Answers (2)

Mayo
Mayo

Reputation: 10802

In case anyone else is looking, we found an alternative solution that works if the source is in SQL 2005 (no support for datetimeoffset).

select dateAdd(minute,datediff(minute,0,getutcdate()),0)

The intent is to reduce the precision. Granted I also lose seconds but if I try the above line with seconds I get an overflow error.

Upvotes: 0

Diego
Diego

Reputation: 36146

I was going to suggest to add a "data conversion component" to deal with it, but since you changed only on the destination, it means that you can change your source query to do:

select cast(YOUR_DATE_COLUMN as datetimeoffset(0))

Upvotes: 2

Related Questions