When you’ve used SSIS for a while, you may have run into the following situation already. Or maybe today is your first time and that’s the reason that you’ve arrived here.
“Huh, what’s he talking about?”, I hear you thinking. Read on then. 
The Scenario
You’ve got a stored procedure or another SQL statement that needs to get called from the Execute SQL Task in the Control Flow of your package. So far so good. One of the parameters that needs to get passed into the statement is of the DateTime type.
How would you do that?
Parameter Mapping – Take 1
Following the KISS principle, let’s say we’ve got the following really complex table in our database:
create table dt ( dtVal datetime );
And in our Execute SQL task we have this extremely complex INSERT statement:
insert into dt values (?)
The statement is expecting one parameter. The parameter that I want to pass into it is System::StartTime which is of type DateTime as shown in the screenshot below.
“Hang on, how did you get the Variables window to display the system variables?”
Ah, good question, by clicking that Show System Variables button, indicated with the red rectangle.
So you set up the Parameter Mapping as follows, specifying DBTIMESTAMP as Data Type and zero as Parameter Name because it’s the first parameter in the statement:
Then you decide to give it a test run. But alas, it throws you the following error:
Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query “insert into dt values (?)” failed with the following error: “Invalid time format”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
So we’ve got a datetime column in the table and we’ve got a DateTime package variable. But alas, the Execute SQL Task is not happy with passing this value to the query.
Now what?
The SqlStatementSource Expression
Let’s try another method then. Instead of passing the parameter’s value through the Parameter Mapping page, we’ll set up an expression that constructs the whole INSERT statement, including the parameter’s value.
Have a look at the following expression:
"insert into dt values ('" +
(DT_STR, 4, 1252) DATEPART("yyyy", @[System::StartTime]) + "-" +
(DT_STR, 2, 1252) DATEPART("mm", @[System::StartTime]) + "-" +
(DT_STR, 2, 1252) DATEPART("dd", @[System::StartTime]) + " " +
(DT_STR, 2, 1252) DATEPART("hh", @[System::StartTime]) + ":" +
(DT_STR, 2, 1252) DATEPART("mi", @[System::StartTime]) + ":" +
(DT_STR, 2, 1252) DATEPART("ss", @[System::StartTime]) + "." +
(DT_STR, 3, 1252) DATEPART("ms", @[System::StartTime]) + "')"
It uses the DATEPART function to fetch parts of the System::StartTime variable and feed it into the INSERT statement using a format that works all the time (YYYY-MM-DD HH:MM:SS.MIL). Here’s what it generated when I clicked the Evaluate Expression button in the Expression Builder:
insert into dt values (’2011-5-31 17:59:37.0′)
So where exactly would you specify that expression? In the Execute SQL Task editor, open up the Expressions page. Then click the Expressions item in the Misc list so that the button with the ellipsis appears. Now click that button, select SqlStatementSource as property and click the Ellipsis button in the Expression field to get to the Expression Builder.
Then you’ll end up with something like this:
Give the package another run. If everything has been set up as expected, the Execute SQL Task should color green and a select on the table should give one record:
Hang on, does it really have to be this complicated?
Well, maybe not…
Parameter Mapping – Take 2
So let’s give the Parameter Mapping another go.
Set up the Execute SQL Task just like in Take 1 above, with one small difference: select DATE instead of DBTIMESTAMP as Data Type for the parameter.
Now give the package another run. Look at that, it colors green and there’s an extra record in the table:
DATE doesn’t seem like the most logical type to choose in this scenario, which is why most people won’t even consider it. But it works! Actually, “DATE” is not really the best name that could be given to this particular data type. Here’s the description of DT_DATE (not to be confused with DT_DBDATE!) according to MSDN:
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.
The DT_DATE data type is implemented using an 8-byte floating-point number. Days are represented by whole number increments, starting with 30 December 1899, and midnight as time zero. Hour values are expressed as the absolute value of the fractional part of the number. However, a floating point value cannot represent all real values; therefore, there are limits on the range of dates that can be presented in DT_DATE.
On the other hand, DT_DBTIMESTAMP is represented by a structure that internally has individual fields for year, month, day, hours, minutes, seconds, and milliseconds. This data type has larger limits on ranges of the dates it can present.
What this means is that you have to be careful when using this type. Even though it works fine today, it may not run fine in a similar scenario that required different date ranges. But obviously you’ve got that covered by your unit test scenarios!
Conclusion
In this article I have demonstrated how a DateTime package variable can be passed as parameter into the Execute SQL Task in more than one different way. My method of preference is the one using the DATE type in the Parameter Mapping.
Have fun!
Valentino.
References
SSIS Execute SQL Task
SSIS DatePart function
KISS Principle
SSIS Junkie: Datetime variables don’t always do what you expect
