Blog Page

Using a ROWVERSION Data Type Variable as an SSIS Input Parameter

sqlsafety 0 Comments

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


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 @rv VARCHAR(25) = ‘0x000000000007A904’

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.

SQL Saturday #192 Tampa Download

sqlsafety 3 Comments

     SQL Saturday Tampa surpassed all my expectations!!  The new venue was awesome and the entire event coordination was outstanding, as always.  Having this event housed so close to me is both a curse and a blessing.  Tampa is only a two hour drive from door step to doorstep for me, which in terms of a free training event of such a high calibre is truly a blessing.  The only downside is that Tampa is only a two hour drive for me, yup a double edged sword, and coordinating my family and business responsibilities makes it difficult to attend the event from open to close.  I have several SQL Saturday events coming up that will requre me to travel to and stay over, at least, Friday and Saturday nights.  After realizing the quality sessions I missed I decided that next year I will fore go the morining commute and get a room to insure that I do not miss a minute!

     I want to thank everyone who attended my session on “Dealing With Errors in SSIS“.  As promised all source code has been posted on my session site for SQL Saturday #192 and also can be downloaded from my site here.


Selecting Vertical Areas in SSMS

sqlsafety 0 Comments

     While fine tuning my presentation for SQL Saturday #192 in Tampa tomorrow I was trying to complete an insert statement of data that I had copied and pasted from the internet.  This appeared to be easy enough at first, but I soon found that I had to modiy the copied data to place commas and single quotes down a straight line in the query tab.  With over 2,000 rows this would be quite intensive to copy and paste the characters one row at a time as I manually stepped down each row one by one.  As I prepared for the tedious and mind numbing task of manning the down arrow and ctrl-v keys to paste the appropriate characters I remembered that SSMS 2012 provides the ability to highlight areas in the query tab from top to bottom, very much like highlighting a column in Excel.  By holding the ALT key down and dragging you mouse you can select a vertical region.

     This worked out perfectly as I could not highlight the vertical area and past in the characters required.  The data appeared in the format displayed in the diagram below:

 alt select

      It is obvious that in order for the INSERT statement to complete successfully a comma needs to be inserted in the column between the number and character values in rows 22 to 30.  If there were only a few rows to add this character the task would be easy enough using the down arrow key, but with over 2,000 rows it becomes much more tim econsuming.  By highlighting the column from top to bottom holding down the ALT key and dragging the mouse down highlights this column of data amd allows the ability to enter the “,” in all selected rectangular areas.

alt select complete

      This functionality may seem to provide a very specific and limited use, but in such cases it can provide a real time saver!

Limit SSIS Package Execution At Any One Time

sqlsafety 5 Comments

           It is a common requirement to insure that only a single instance of an SSIS package can be running at one time.  This would appear to be simple and my first idea was to check to see if a package assigned to an SSIS step in a SQL agent job is running by querying the msdb database.  OOOOPPPPPSSSS!!  I forgot!  SSIS packages can be run using several methods including SQL Server agent.  What if a job is being run from an SSIS package, batch file, and directly from the command line?  The msdb database, and even the SSIS catalog for SSIS 2012, does not capture when a package is run outside of a SQL Server agent job. 

               After some consideration I initially considered using .NET to query the running services looking for DTExec and attempt a WMI query to obtain the running package, but I realized that I was making this much harder on myself.  In the Microsoft.SqlServer.Dts.Runtime.Package.Applcation namespace there is a method, GetRunningPackages, which can be used to return metadata about each running package on the server/workstation in which the method is run. 

               To demonstrate how this works create a package that contains a package scoped variable called IDCount that is an integer data type.  Drop a script task onto the control flow design surface that uses assigns the IDCount variable as ReadWrite and the System:PackageID as a Read variable. 

              My current code sample uses VB.NET, but I will update this to include C# in the near future.  The script task code will utilize the GetRunningPackages method to iterate through all running packages and when the packageid of the running packages matches the current packageid then the IDCount variable will be incremented by one.
Public Sub Main
‘ Declare and instantiate objects here.
Dim app As Application = New Application‘…
‘ Create a RunningPackages collection, named pkgs, and fill it
‘ with the running packages from the application object.
Dim pkgs As RunningPackages = app.GetRunningPackages(Nothing)

‘ Enumerate over each package in the collection and display some data.
For Each package As RunningPackage In pkgs
If package.PackageID.ToString().ToUpper = Dts.Variables(“PackageID”).Value.ToString().Replace(“{“, “”).Replace(“}”, “”) Then
(“IDCount”).Value += 1
Catch ex
As Exception
End Try

End If

MessageBox.Show(“There are ” + Dts.Variables(“IDCount”).Value.ToString() + ” running”)
Dts.TaskResult = ScriptResults.Success
End Sub

           The short translation is that a new DTS application and runningpackages is instantiated in the code and then, using a for each loop, the packageids are retrieved and compared against the current packageid.  You will notice that the Replace and ToUpper functions are applied to insure that the package GUIDs match when compared.  If the packageid’s are equal then the IDCount variable is incremented by one. 

          The script task acts as the gateway to the package and a precedent constraint can then be used to insure that if the package is already running the package exits.  In order to exhibit how this works drop another script task onto the control flow design pane that accesses the IDCount as a read only variable.  In the code of the task simply call a messagebox show method to display the IDCount:
MessageBox.Show(“There is ” + Dts.Variables[0].Value.ToString() + ” instance of this package running”;

           Using a precedent constraint from the gateway script task to one or all other tasks you can evaluate the IDCount to insure that the package is not currently running, if the IDCount is > 1 then there are multiple instances of the same package running.


          When the package is executed the first script task evaluates what packages are running and compares the packageid’s.  The IDCount will always be at least one, but if more the precedent constraint evaluates as to whether the next step(s) should be execute.  If the count is not equal to one than the package stops at the precedent constraint.

          The sample package referenced in this example can be downloaded here.

Does Case Matter?

sqlsafety 0 Comments

     Over the past couple of weeks I have responded to several forums in regards to collation settings specific to searching tables and referencing objects.  The most common question in regards to collation is “does case matter” and the answer is maybe.  The detailed response is that collation defines:

  1. Sort order for Unicode characters
  2. Sort order for Non-Unicode characters
  3. Code page used to store Non-Unicode characters

The short answer is that collation defines how your data is stored, sorted, and compared.

Collation, in regards to SQL, can be set at different scopes:

  1. Instance
    1. Defines collation for system databases
  1. Database
    1. Defines collation for user defined database
  2. Column
    1. Column level collation can be different than the collation defined on the database
  3. Query
    1. The COLLATE clause can be used to define the collation used at the query level.

     To demonstrate collation at the database scope create a database using a case sensitive collation, create a table using all lower case characters, and attempt to select from the table using mixed case characters.

( NAME = N'Colate', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Colate.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
( NAME = N'Colate_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Colate_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
COLLATE Latin1_General_100_CS_AS

col1   INT);

USE [Colate];

FROM Allower;

      The above query results in an error saying that “Allower” is an invalid object.  The issues is not that the tabe does not exist, but rather that the table is referenced using mixed case characters while the table name is defined using  all lower case characters.  Changing the table name to reflect the proper case, “allower”, would complete successfully and return an empty result set.

FROM allower;

      This demonstrates object resolution issues that can arise with collation, but searching character data can also be affected based upon collation.  Consider a table that contains character data that again is case sensitive.
CREATE TABLE characters(
col1   CHAR(5));

INSERT characters

      When attempting to compare character data when the collation is set to case sensitive requires that the character case match. The below query returns an empty result set as the character case in the predicate differes from the value stored in the case sensitive database:
FROM characters
WHERE col1 = 'upper';

      There are several ways to insure that case sensitivity is met in such a query.  The first is to use the UPPER or LOWER string function:

FROM characters
WHERE LOWER(col1) = 'upper';

 This method changes the column character case to all lower case which is then compared the the loser case string literal in the predicated.  Another method is to insure that the the collation of the column is set to a compatible collation of the string literal in the predicate using COLLATE:

FROM characters
WHERE col1 COLLATE Latin1_General_100_CI_AI = 'upper';


 The above query changes the collation from Latin1_General_100_CS_AS to case insensitive and accent insensitive.  The result is the single matched row with the col1 value being UPPER.

     It is important to know the collation settnigs at the various scopes and to keep in mind just because a database is set to a specific collation you should not take for granted that the column collations will be the same.

Assigning SQL TIMESTAMP To An SSIS Variable

sqlsafety 5 Comments

            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.



sqlsafety 0 Comments

Since I began working with SSIS I have always been asked, “Why would I use that data transformation task when I could just include it in my query at the data source“.  A typical example of this is using the Sort transformation in SSIS rather than using an ORDER BY clause in the query supplying the data source.  SSIS is not meant to replace T-SQL functionality by any means, but is meant as the primary ETL tool in SQL Server.  After working 22 years in public safety I cannot say enough that NOTHING AFFECTS PRODUCTION!!  Our production environment is responsible for insuring a quick response to those needing help, NOT to serve up a report on the total calls for service by zone, district, time, etc.

               Realizing that production is sacred a separate reporting environment was created some years ago to insure that analysis did not interfere with production.  In order to present relevant and accurate information obviously the reporting solution must be updated by production, but the overhead must be minimal.  Consider a set based solution in which ordering of results is required in a VERY large result set.  If the ordering is contrary to any clustered index than the overhead that will be incurred using ORDER BY from the source could be dramatic, but if the source is on separate physical resources than where the SSIS package is running the contention for resources can be dramatically reduced by using a Sort transformation.  The diagram below illustrates how the workload can be distributed efficiently away from production in these cases:

 SSIS distributed environment

              In my case we had a separate virtual server on separate physical hardware for the source, SSIS installation, and destination.  By using SSIS transformation, like sort, rather than complex queries, the need for resources was reduced on production and moved to the SSIS server.

Hit Counter provided by Sign Holders