CODEWITHSUNDEEP
ssis
Lorraine Irion
Lorraine Irion

Reputation: 31

Update SQL Table with SSIS Rowcount

I have a flat file that I am saving to a SQL Table. I want to count the rows that inserted and write the count to another table.

Upvotes: 3

Views: 7229

Answers (1)

billinkc
billinkc

Reputation: 61211

The simple answer is to create an SSIS Variable and drop a RowCount transformation onto your dataflow.

Create a variable

On the Control Flow, click in the background. Do not click on any tasks or your variable would be created at the wrong scope (this caveat does not apply to 2012). Right click and select Variables. In the Variables window, click Add button and name it as RowCounts with a data type of Int32 (unless you need Int64 (more than 2M rows))

Add a row count transformation

Inside your data flow, add a Row Count transformation after your data source. Configure it to use the variable we created above. The resulting data flow might look something like this

row count data flow

It is important to note that the row count component does not assign the row count into the @User::RowCount variable until after the data flow completes.

Saving the row count value

Once the data flow finishes, you would then need to use an Execute SQL Task in the Control Flow to write the value into your table.

control flow

The Execute SQL Task would look something like this, depending on what your table is defined as.

INSERT INTO 
    dbo.RowCounts
(
    rowcounts
) 
SELECT 
    ? AS rowcounts

In the Parameter Mapping tab, it would look like

User::RowCount  Input  Long  0  -1

Upvotes: 7

Related Questions