x

SSIS RowCount

I am trying to use a rowcount in an ssis package. I want to use the row count result (pacakge level variable) and write it to a logging table.

right before I load the table with results, I am using a mutlicast to duplicate my destinations - one goes to the ole db destination , the other goes to the rowcoount variabble. this is intended to give me a int value to be used in a second data flow task that will be used to populate a logging table.

There are no rows that get logged so i assume that my variable is not getting populated

I am also doing the same thhing as an error handler if the records inserted violate a constraint.

more ▼

asked Jun 15 '12 at 12:14 AM in Default

siera_gld gravatar image

siera_gld
1k 74 80 83

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

![![alt text][1]][1]I got it - it takes aggregates - and we can keep it in the same DFT

[1]: http://ask.sqlservercentral.com/storage/temp/230-ssc.png
ssc.png (30.2 kB)
ssc.png (30.2 kB)
more ▼

answered Jun 16 '12 at 06:07 AM

siera_gld gravatar image

siera_gld
1k 74 80 83

What kind of row counts you are calculating using the Aggregate? If you really need to log only row counts, than you should do this in s SQL Task or second data flow. As Aggregate is a fully blocking transformation and will have to keep all records in memory (or will be swapped to disc if there is not enough memory) and will slow down the overall ETL performance.
Jun 17 '12 at 08:38 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
  1. How you are using the variable inside the second data flow for logging? Generally to log a counts after first data flow has finished, use a SQL Task after the data flow to log that variable value into a logging table. If you want to use it in a data flow, you need to have some source which will produce source rows to be written into the destination othewrise no rows will be written.

  2. You do not need to do a multicast and direct one part to the row count component and the second to destination. The Row Count component can be part of a dataflow. YOu can connect output of Row Count component to othr transformation inside the Data Flow.

more ▼

answered Jun 15 '12 at 05:30 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

(comments are locked)
10|1200 characters needed characters left

The first DFT writes it to a package variable.

then the 2nd DFT uses a oledb source, then i am using a derived column then picking the package level variable and then using the derived colmn (variable count) to use to write to the log

I would show the screenshot but it is not working in SSC...
more ▼

answered Jun 16 '12 at 04:26 AM

siera_gld gravatar image

siera_gld
1k 74 80 83

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x900
x5
x5

asked: Jun 15 '12 at 12:14 AM

Seen: 1799 times

Last Updated: Jun 17 '12 at 08:38 PM