Passing A DateTime Parameter Into The Execute SQL Task
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.
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.
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!
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.
- T-SQL Tuesday 50: Automation, Automation, Automation!
- The "Select ALL" parameter option [SSRS]
- Book: SQL Server 2012 Reporting Services Blueprints
- SQL Server Days 2013 – Data Cleansing: Download
- SQL Server Days 2013
- Exploring the System.Object Package Variable [SSIS]
- T-SQL Tuesday 46: Contraptions!
- Creating Multi-Column Reports: The Top-Down Version [SSRS]
- Formatting Dates [SSRS]
- Community Day – Data Visualization Tips & Tricks: Download
- January 2014 (1)
- December 2013 (1)
- November 2013 (2)
- October 2013 (1)
- September 2013 (2)
- July 2013 (2)
- June 2013 (2)
- May 2013 (3)
- March 2013 (3)
- February 2013 (2)
- January 2013 (2)
- December 2012 (2)
- November 2012 (3)
- October 2012 (2)
- August 2012 (2)
- July 2012 (2)
- June 2012 (2)
- May 2012 (2)
- April 2012 (3)
- March 2012 (4)
- February 2012 (4)
- January 2012 (2)
- December 2011 (2)
- November 2011 (2)
- October 2011 (1)
- September 2011 (3)
- August 2011 (2)
- June 2011 (2)
- May 2011 (3)
- April 2011 (3)
- March 2011 (3)
- February 2011 (2)
- January 2011 (5)
- December 2010 (1)
- November 2010 (3)
- October 2010 (3)
- September 2010 (2)
- August 2010 (4)
- July 2010 (2)
- June 2010 (4)
- May 2010 (6)
- April 2010 (3)
- March 2010 (3)
- February 2010 (11)
- January 2010 (9)
- December 2009 (2)
- November 2009 (3)
- October 2009 (3)
- September 2009 (4)
- August 2009 (6)
- July 2009 (2)
- June 2009 (3)
- May 2009 (7)
- April 2009 (3)
- March 2009 (3)
- February 2009 (5)
- January 2009 (4)
- December 2008 (2)
- November 2008 (3)
- October 2008 (1)
- September 2008 (1)
- August 2008 (4)
- July 2008 (3)