Using a ROWVERSION Data Type Variable as an SSIS Input Parameter

The sample package outlined in this post can be downloaded here.

             I recently posted about retrieving and using a TIMESTAMP data type in a variable in SSIS.  I first want to apologize for any confusion between the ROWVERSION and TIMESTAMP data type.  ROWVERSION is the synonym for TIMESTAMP, and you should use ROWVERSION in your DDL statements wherever possible.  This outlines how to retrieve the binary value and converting it to a VARCHAR, using a system defined function, and stores it in an SSIS variable insuring that the binary representation is maintained.  I further showed how a dynamic query can be created using a variable expression using the ROWVERSION stored in the variable. 

              This works well enough until you attempt to use an execute SQL task creating a query that uses the ROWVERSION variable as an input parameter.  I was at first completely taken aback at the errors I was receiving.  By replacing the SQL string with the input parameter with a variable expression and concatenating the ROWVERSION variable to the expression string works so WHAT IS THE PROBLEM!!

To best illustrate this lets walk through the process. 

  • A string variable is used to hold the ROWVERSION
  • An Execute SQL task is used to get the ROWVERSION mapped to the variable as either an output parameter or a result set
  • The result set or output parameter MUST have a comparable SQL data type
  • ROWVERSION is a binary data type that does not provide an easy conversion
  • The system function, master.dbo.fn_varbintohexstr(rowversion), is used to convert ROWVERSION from binary to its equivalent VARCHAR value
  • Once the Execute SQL task completes the VARCHAR ROWVERSION value is stored in the SSIS string variable

              The difficulty working with this value is not within SSIS, but rather in SQL as the value must first be converted to a SQL data type that can be stored in the comparable SSIS variable data type.  Once the accurate value is retrieved then it is stored in the variable and it truly is what it is, a string representation of that binary value.

              When creating a query variable the entire query is a string and can concatenate SSIS variables together, conversions to string must be done where necessary.  The query is passed, from SSIS to the defined connection, as a string.  Once the string is received then SQL treats it as a query in total and parses the whole and not the parts.

SSIS Query Variable Expression

“SELECT * FROM ExampleTable WHERE [timestamp] = ” +  @[User::TimeStamp]

What SQL Sees

SELECT * FROM ExampleTable WHERE [timestamp] = 0x00000015E1CE4950

               Comparing a query variable to a query using input parameters the difference lies in the fact that SSIS variables are mapped to “?” within the query string.  Each input parameter must be mapped to an SSIS variable that MUST have a defined SQL data type that is comparable to the SSIS variable data type. 

The query is no longer treated as a string, but rather more like a stored procedure with input parameters.

SSIS Query DirectInput

SELECT * FROM ExampleTable WHERE [timestamp] = ?

What SQL Sees

DECLARE @TimeStamp AS VARCHAR()

SELECT * FROM ExampleTable WHERE [timestamp] = @TimeStamp

              SQL server evaluates the query and sees that a VARCHAR() value is being used in the predicate to compare the value of a ROWVERSION column and now we have an implicit conversion error.

Msg 257, Level 16, State 3, Line 2

Implicit conversion from data type varchar to timestamp is not allowed. Use the CONVERT function to run this query.

              One way around this is obviously to port the query over to a variable that uses an expression, as outlined above this will work.  If you still wish to use DirectInput for your query string then you must make the conversion where necessary, BUT YOU MUST INSURE that the proper binary value is converted from VARCHAR to BINARY.

               The new DirectInput query string should look like this:

DECLARE @RowVers VARBINARY(8)
DECLARE @rv VARCHAR(25) = ‘0x000000000007A904’
SET @RowVers = CONVERT(VARBINARY(MAX),@rv,1)

SELECT * FROM ExampleTable WHERE [timestamp] = @RowVers

I have used two different variables just to try to step through the process and outline the conversion.  The @RowVers variable is a VARBINARY(8) data type and then used to hold the converted ROWVERSION data type.  You will notice that the CONVERT function is passed the optional style parameter of “1”, which in this case is specific to the binary style.  The new query insures that an explicit conversion is done to the input parameter, which is defined as a VARCHAR data type, to a binary format that can then be used in the predicate to compare the input value against the ROWVERSION column.

Leave a Reply


Hit Counter provided by Sign Holders