CODEWITHSUNDEEP
sql-serverdatetimessis
Prabhat G
Prabhat G

Reputation: 3029

storing datetime2 in ssis variable

I'm using SQL Server 2012 Enterprise. I have a requirement where I've to store data with DATETIME2 datatype in SSIS Variable. Unfortunately, SSIS variables don't have that data type.

If I'm storing it into datetime data type, I'm losing information. Can anyone help in giving workaround?

PS: My source system is SQL Server 2012 as well and I'm reading the data from column with datetime2 datatype.

Upvotes: 2

Views: 5424

Answers (1)

Thom A
Thom A

Reputation: 95554

SSIS, at least currently, has a "known" shortfall in that the variable value type, DateTime only has a precision to the second; effectively the same as a datetime2(0). If you therefore need to store anything more accurate that a second, such as if you are using datetime and the 1/300 of a second is important or if you are using datetime2 with a precision of 1 or more, the value type DateTime, will not serve your goal.

A couple of different options are therefore to store the value of as a String or numerical value. This does, however, come with it's own problems; most and foremost that neither of these datatypes are date and time datatypes.

It therefore depends what your goal is. I would most likely make use of a String datatype and ensure it has the ISO format ('yyyy-MM-ddThh:mm:ss.nnnnnnn'). If you're then using something like a T-SQL Task you can pass your variable as normal to the task and the data engine will interpret the literal string as a datetime2(7) (or whichever literal precision you used).

Upvotes: 2

Related Questions