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. :-)

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.

Show the system variables by activating the Show System Variables button

“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:

Execute SQL Task: Parameter Mapping

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:

The Property Expressions Editor with an expression specified for the SqlStatementSource property

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:

Our test table contains one timestamp!

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.

Choose DATE as Data Type when passing a DateTime package variable into the Execute SQL Task

Now give the package another run.  Look at that, it colors green and there’s an extra record in the table:

An extra timestamp was written to 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

Share

Tags: , ,

  1. Ranjit Kumar’s avatar

    Thanks Buddy, you saved my 20 minutes or more in potential juggling with date/time options.

    Reply

    1. Valentino Vranken’s avatar

      Glad to hear that my explanation on saving a datetime parameter saved you some time! :-)

      Reply

  2. Angel’s avatar

    Thanks man! I thought all was lost, but then I found your post :) Thanks again!

    Reply

    1. Valentino Vranken’s avatar

      You’re welcome. Helping the lost get back on track, exactly what I’m trying to do here! :)

      Reply

  3. Abdul Basit’s avatar

    Excellent Article and I like the humorous touch..

    Reply

    1. Valentino Vranken’s avatar

      Thanks, that was my intention! :)

      Reply

  4. Ulf L’s avatar

    I’m also trying to pass System::StartTime to my SQL.statments however isn’t System::StartTime a DateTime(?) and if so how to one get all of it to the SQL?

    (The problem is the “000” at the end of “2011-06-08 16:07:41.000”.)

    Sincerely,

    Ulf (Using SQL-2005)

    Reply

    1. Valentino Vranken’s avatar

      Hello Ulf,

      System::StartTime is indeed a “DateTime”. However, as you’ve noticed, the “DateTime” package variable type is different from the “datetime” data type in the SQL Engine. It cannot contain milliseconds, unfortunately.

      If you really need to get the timestamp up to the millisecond into the database, the easiest will be to use GETDATE() or SYSDATETIME() (which is even more precise, but doesn’t exist in 2005) somehow. It depends a bit on where you’re needing this (Control or Data Flow).

      One way would be to use the Execute SQL Task, but make sure that you don’t put the result in a package variable because you’ll lose the milliseconds once more.

      For the Data Flow, a couple of options are adding GETDATE() to your source query, or using the Derived Column Transformation to add a new column to your flow. In the Derived Column you can also use a GETDATE() function which will return a timestamp with milliseconds.

      Hope this helps a bit?

      Thanks for your question, I should probably revise my article a little to incorporate this additional info! :)

      Best regards,
      Valentino.

      Reply

  5. Trilok’s avatar

    I appreciate for your efforts to work on it and publishing. It has saved a lot of my time. I worked for me.

    Reply

  6. Sandesh’s avatar

    Thanks dude for your post. It saved my considerable amount of time.

    Reply

  7. Guy’s avatar

    Many, many thanks. I am continually amazed whenever SSIS does what I want. It is so complicated.

    Reply

  8. Josh’s avatar

    This article helped me out of a tricky situation. I was looking at an SSIS package that called a stored proc that used a smalldatetime input variable. It had been noticed that the process in production was producing the wrong results. I noticed in SSIS that the parameter type was set to long. Even though I confirmed that in the SSIS package, the variable was correctly being set (in this case to 2012-09-03), a trace confirmed that SSIS was passing an integer value of 41155 to the proc (2012-09-05), which accounted for the issue we were seeing in production. The type on the parameter in the SSIS package was set to LONG (no idea why). Changing the type on the parameter to DATE produced the correct results.

    Reply

  9. Big Mighty Troll’s avatar

    Excellent article!
    Helped a lot with this stuck :)

    Reply

  10. DB Gal’s avatar

    Thanks!!! I was so stuck on that!

    Reply

  11. George’s avatar

    Nice one! Greatly appreciate the time you took to write it.

    Reply

  12. TEju’s avatar

    thanks for this lucid article,
    my point of question is how to pass the User defined variable to SQL Statement?

    I tried -> ? and also @[User::Month]

    Both did not work for me.

    Reply

  13. Matthew’s avatar

    Good post, saved me a lot of time! Thanks.

    Reply

  14. Vijeth’s avatar

    Thanks a lot for the article!!! I always tried various options like DT_DATE, DBDATE, DBTIMESTAMP and it would always give the error that you have mentioned. My workaround was that i used to create a new variable which was of the type string and this variable would be having the value of the Datetime variable in string format which would be sent as Input Parameter and then converted it to datetime in the DB side. I never thought of using DATE as a parameter as I thought it would remove the TIME portion of the parameter. Thanks again :)

    Reply

© 2008-2014 BI: Beer Intelligence? All Rights Reserved