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.

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

siera_gld
936 ● 52 ● 70 ● 74

(comments are locked)

3 answers:

<img src="http://ask.sqlservercentral.com/storage/temp/230-ssc.png" alt="alt text"/> I got it - it takes aggregates - and we can keep it in the same DFT

ssc.png (30.2 kB)

answered Jun 16 '12 at 06:07 AM

siera_gld
936 ● 52 ● 70 ● 74

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)

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.
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.

answered Jun 15 '12 at 05:30 AM

Pavel Pawlowski
20.3k ● 5 ● 10 ● 20

(comments are locked)

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...

answered Jun 16 '12 at 04:26 AM

siera_gld
936 ● 52 ● 70 ● 74