To do so, I set up a test environment that includes a comma-separated text file, a table in a SQL Server database, and an SSIS package that retrieves data from the text file and inserts it into the table. Person table in the Adventure Works2008R2 database.
To populate the file, I ran the following bcp command: After I created the file, I manipulated the first row of data in the file by extending the Last Name value in the first row to a string greater than 50 characters.
A SQL Server Integration Services package can use row counts to update the variables used in scripts, expressions, and property expressions.
(For example, the variable that stores the row count can update the message text in an e-mail message to include the number of rows.) The variable that the Row Count transformation uses must already exist, and it must be in the scope of the Data Flow task to which the data flow with the Row Count transformation belongs.
You can use the updated variable in a separate data flow. The Advanced Editor dialog box reflects the properties that can be set programmatically.
For more information about the properties that you can set in the Advanced Editor dialog box or programmatically, click one of the following topics: For information about how to set the properties of this component, see Set the Properties of a Data Flow Component.
When data is moved from one table to another, the On Information event will give me a message such as: I tried using Row Count Transformation which worked for me.
Here is a snapshot of the Data Flow in question: I'm not sure if its inclusion in a Conditional Split may be causing this error, but none of the other Row Count components seem to be throwing this error. Variable names are case sensitive in SSIS so Could you post a screenshot of this variable?
What I'd like to verify is the Name, Scope and Data Type are expected.
Any time there's text in the variable name field, it gets returned in the error, regardless of whether or not the variable exists. Try comparing the XML of the between one of the Row Counts that works, and the one that doesn't.
Try setting the Validate External Meta Data property False and then try, quite possible due to run time allocations.
The transformation stores the row count value in the variable only after the last row has passed through the transformation.