Assigning SQL TIMESTAMP To An SSIS Variable

            I know that my posts seem to have become quite brief, but since moving my blog I have been posting all sample files for download.  I have done my best to insure that all downloads fully document the steps and processes I discuss in each post.  The sample files for this post can be downloaded here.  

            I recently had to modify a package in order to retrieve and later use a TIMESTAMP.  The job specifics were that I had to retrieve the current TIMESTAMP, using @@DBTS, and later use that in an execute SQL task to store the value to filter future data sets in several data flow tasks.

              As I jumped to the task I quickly decided that the easiest way to capture the value was to use an execute SQL task and assign the value to an SSIS variable of a data type string.  I used the query SELECT ? = @@DBTS and mapped the package variable as an output parameter, data type of VARCHAR, in the execute SQL task. 


     I was surprised to find that the SSIS variable was assigned an empty value upon completing the task.  I verified the results by using a script task that used the TimeStamp variable value in a message box?  I was thoroughly confused and decided that it must be the fact that the value was being assigned as an output parameter rather than a result set.  After some quick modifications to the execute SQL task I assigned the result set of the query to the TimeStamp variable and once again used a script task to display the results.  I was once again surprised to find that the results were less than accurate.  The message box with the value is show below:


               This appears to be a good start, but when verifying the result I found that this value was once again short of the actual value of 0x0000000000069F6C.  After brushing up on the TIMESTAMP data type I discovered that this is actually a binary value and cannot be directly converted to VARCHAR, which is the data type of the parameter or result set.  I immediately began to explore using a script task with a data reader to obtain the value and provide an accurate conversion, but did not wish to make this package all the more complex.

               After some more research I discovered a built in function that will provide the conversion of a TIMESTAMP to NVARCHAR.  After some experimenting I discovered that the function master.dbo.fn_varbintohexstr provided me with the results that I wanted assigning an accurate value to my SSIS variable.  As any good child I wanted to take this function apart to see how it works so I used the OBJECT_DEFINITION() function to view the functions body.  I was surprised to see that this function actually calls another function, sys.fn_varbintohexsubstring, which does the heavy lifting behind the scenes.

               I modified my query in the execute SQL task to use the scalar valued function, master.dbo.fn_varbintohexstr, and assign the returned value to my SSIS TimeStamp variable.  I then was able to create SQL command variables that utilized the TimeStamp variable in several execute SQL and data flow tasks that used built dynamic commands in SSIS.

               I created a sample package that demonstrates the processes that I outlined which can be downloaded here.  I have also posted on using SSIS variables to replace dynamic SQL which helps in understanding the processes described.


Comments ( 5 )

  1. Replyandre

    TITLE: Microsoft Visual Studio ------------------------------ There were errors while the package was being loaded. The package might be corrupted. See the Error List for details. ------------------------------ BUTTONS: OK ------------------------------

    • Replysqlsafety

      Can you provide any of the error details?

  2. ReplySiva

    hi, can you the package without any error, while trying to open the package I am getting some XML error.

    • Replysqlsafety

      What is the error that you are receiving?

    • Replysqlsafety

      The package is an SSIS project and if you are attempting to open it in an earlier version this would be the reason for the error.

Leave a Reply

Hit Counter provided by Sign Holders