Convert String Lines SSIS 2008 - Return String Count 0

Question

It should be pretty simple, but I don’t know why I get Row Count as Zero when I use ROW COUNT to Data Flow Task conversion. I created a variable (NoOfRecords) with the scope of the package.

The variable name given by the NoOfRecords variable in the conversion of the number of rows.

Use the Derived column to designate the number of rows.

The package runs successfully and shows the number of records 265

But the Derived column shows the number of records as 0 instead of 265 rows.

Answer 1

After counting the lines, add the "Aggregate rate" parameter and select the "Number" parameter on the "Operation" tab in the "Aggregate task" properties. Then you can use the line count variable for further work, where it contains the total number of lines of the input file.

Answer 2

The number of rows is processed after the rows have passed.

You add a variable to each row when they go through the step of the derived column, but at that time the variable is not updated (as it happens after all the rows have passed), so the value 0 is correct.

You can achieve this by using an asynchronous task in front of your derivative (but I'm not sure if this will work, it just appeared on my mind). Add a Sort or Aggregate step in front of your Derived and try again.

Answer 3

I used this in the query as an efficient way to get the number of rows:

count (all SnapshotDate) over () as nRowCount

Answer 4

Here is a good line recording technique that worked in my situation. The scenario is that I want to register rows wrapped between tables. RowCount does not populate until you exit DataFlow. [Control flow] 1. Data flow task a. read source data - source control b. Add a RowCount transformation. Link from a to b. Right-click RowCount and map to UserVariable (int64) c. Add destination to load the table. e. Link b to s. 2. Add the Execute SQL task to ControlFlow. right-click, edit the SQL INSERT statement: Insert into LogTable (rowcount) Values ​​(?) Display of parameters Direction of variable DataType ParameterName ParameterSize User :: RowCount INPUT LONG 0 -1