Using Row Count data flow transformations

Posted by Jeff Rix on January 19, 2012 under SSIS | Be the First to Comment

Create an Int32 user variable with a package scope. Configure the Row Count property Custom Properties>Variable Name to the user variable.

You cannot use the Row Count variable in the same dataflow it is set because it is processing the records and sending them to the destination at the same time.

to use in the variable in an Execute SQL Task use an ADO.Net Connection Type and map the variable to an input parameter with the same data type using a parameter name that starts with the @ character.

In your query reference the variable using the parameter name and you will be all set!

User Variables in SSIS

Posted by Jeff Rix on August 16, 2011 under SSIS | Be the First to Comment

To create a User Variable you need to bring up your variable window. One way to access this is to go to View > Other Windows > Variables. 

To add a variable click add variable (blue arrow). The scope will be set to the name of the package if you are in control flow and the name of the data flow if you are in a dataflow.

To set the variable using a Execute SQL Task item set the result set to single row. Make sure your SQLStatement only returns one row.

The Result Set then needs a name mapped to the user variable name

To use the variable in a data flow use a derived column and set a derived column name equal to the user variable.