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!


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!



SSIS Execute SQL Task

SSIS DatePart function

KISS Principle

SSIS Junkie: Datetime variables don’t always do what you expect


Tags: , ,

  1. Ranjit Kumar’s avatar

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


    1. Valentino Vranken’s avatar

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


  2. Angel’s avatar

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


    1. Valentino Vranken’s avatar

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


  3. Abdul Basit’s avatar

    Excellent Article and I like the humorous touch..


    1. Valentino Vranken’s avatar

      Thanks, that was my intention! :)


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


    Ulf (Using SQL-2005)


    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,


  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.


  6. Sandesh’s avatar

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


  7. Guy’s avatar

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


  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.


  9. Big Mighty Troll’s avatar

    Excellent article!
    Helped a lot with this stuck :)


  10. DB Gal’s avatar

    Thanks!!! I was so stuck on that!


  11. George’s avatar

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


  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.


  13. Matthew’s avatar

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


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


  15. Sadfeen’s avatar

    Awesome, first hit on my search :)


  16. Eric Lawson’s avatar

    Good grief. I have literally been in my shed (summer office) for over an hour trying to get this damn Execute SQL Task to store a LastChangeDT I am chucking out from a CDC tracking script. I thought the CDC was the hard part but getting the Execute SQL Task to like the datetime value has been a nightmare.

    Thanks for the blog post. I know have nice green arrows in my ETL. Its Friday afternoon, shortcut to Beer Time.


  17. Alexander’s avatar

    I was searching a simple way for passing a DateTime parameter without any concatenations and building from parts. Bingo! You have showed me how I need to do that. Thanks a lot


  18. QuickDraw’s avatar

    This worked for me trying to pass a datetime from VS2013 SQL 2014 via SQLNCLI11.

    I was getting ‘the scale is invalid’ errors while using DBTimestamp when passing a parameter. Changed this to Date type and bingo :-)



  19. Simon’s avatar

    I fixed this problem by changing the parameter type to DATE


  20. Lee’s avatar

    Thank You :-) would have never thought to use this data type it solved my issue


  21. babar’s avatar

    how can i store date in sql server through ssis. I am trying to get date from my file name for instance this is my file name numn_2008_08_05.csv so how can I get only this date and store in sql server. I alread made package that storing my all files through for each loop and also I transfered my all files in achived folder when its store in sql server.


  22. greg’s avatar

    Good article which definitely covers the in’s and out’s of the date type. Here is an end case that I haven’t solved. We store milliseconds on the datetime value on our sql svr db. This is important to capture data that may occur in that last second of the day. I set a date in the first exec sql task in my SSIS package that is in the form ‘yyyy-mm-dd 23:59:59.997′. The problem is that when I store this value as a datetime variable type (set through the result set of my first sql task), it is rounded up to the next day eg ’2015-09-29 23:59:59.997′ becomes ’2015-09-30 00:00:00.000′.
    It is just the millisecond portion that causes the issue. A value without the milliseconds is handled correctly, but I always need .997 milliseconds. I can use a dateadd() within each sql statement to add the milliseconds, but I would have thought there would be a more elegant solution so that the datetime datatype is a ‘true’ datetime datatype reflecting the sql server datetime datatype.
    If anybody has insight into this issue, I offer you a toast!…Hoegaarden preferred!


  23. Ali’s avatar

    Thanks a lot :)


  24. T’s avatar

    Thank you! Excellent and educational article.
    We solved the issue by changing, in the SSIS package parameter mapping, the type to DATE.


  25. uwe’s avatar

    many, many thx, I just want to stop reading at the “expression” solution but then scrolled down and that’s it.
    such people as you are the heros of the programmers and it’s not only the solution but also a wide description of what’s pro and contra.


  26. Trey’s avatar

    5 years after the article was published, I find it in a Google search and it helps me resolve my issue. Thanks!


    1. Valentino Vranken’s avatar

      Glad to hear this Trey, thanks!


  27. Kathie’s avatar

    Ditto the above. Thanks for a solution that is still working. :)


  28. Aravinda’s avatar

    After juggling with various options, I found this master piece in my online search. Things are explained well and it solved my problem. Thank you so much for publishing this.


  29. jan sandesh online’s avatar

    Do you mind if I quote a few of your posts as long as I provide credit and sources back to your site?
    My blog site is in the exact same area of interest as yours and my users would definitely
    benefit from some of the information you present here.

    Please let me know if this ok with you. Many thanks!


  30. gps sonar combo’s avatar

    All in 1 app. I enjoy this fishfinder.


  31. Ron’s avatar

    Thanks a lot – saved me a lot of time. I was really puzzled why it does not want to work. Not logical at all M$ – shame on you :)


  32. Missy Dano’s avatar

    Great information. Luckky me I found your site by chance (stumbleupon).
    I’ve saved it for later!


  33. Roselee Rokicki’s avatar

    Do you have anny video of that? I’d like to find out
    some additional information.


  34. Eusebia Jeffries’s avatar

    Article writing is also a fun, if you know after that you can write or else
    it is complex to write.


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