SSIS - insert system date variable as a execute sql task parameter

Question

I am trying to insert the system:starttime variable into a table by passing a parameter into an execute sql task.

I dont have much knowledge of SSIS data types, and I am currently having trouble making this functionality work. When I set the parameters datatype to nvarchar, the date is always inserted as 1/1/1900. When I use the DBDATE type, the insert fails with the following error

unsupported data type on parameter binding 0

So my question is how do I pass a SSIS date variable into an execute sql task so that SQL Server can interpret it correctly?

• Aasim Abdullah: Perfect answer here blog.hoegaerden.be/2011/06/11/…
• David Spillett: @aasim.abdullah: It would be a good idea to summarize the content of that link in an answer, that way if the link dies at a later date (and StackExchange doesn't) the information is still available here. Include the link in the answer of course for people who want more detail and for giving credit to the source.
• Neil P: @aasim.abdullah - Thanks for the link, as recommended I tried using the "DATE" datatype (not "DBDATE") and it worked like a charm!
• Aasim Abdullah: @David sure buddy

Answer 1

According to BOL DT_DBDATE is "A date structure that consists of year, month, and day". Which can't be used to store System::StartTime output as its DATETIME by data type, so try to use datatype DT_DATE instead of DBDATE. As datatype DT_DATE "A date structure that consists of year, month, day, hour, minute, seconds, and fractional seconds. The fractional seconds have a fixed scale of 7 digits."