CODEWITHSUNDEEP
c#vb.netssis
vivek_bye
vivek_bye

Reputation: 7

Logging number of rows transfered using script task without using SSIS logging

I have over 40 independent data flow tasks inside a sequence container.

Is there a way to log to a flat file the number of rows transferred to the destination within each of the data flow tasks inside the sequence container?

I tried this method:

  1. Store the rowcount value to a variable using a rowcount task.
  2. Execute the destination table task.
  3. Execute the script task to write the rowcount variable to a flat file after the data flow task.

This method worked, but I’ll have to keep adding script task for every dataflow task inside that container.

Is there a way to have this script execute automatically after each data flow task completes, to write the number of rows each dataflowtask transfers, with custom text saying "xyz data flow task transfered xxxx rows"?

Upvotes: 0

Views: 1268

Answers (3)

milivojeviCH
milivojeviCH

Reputation: 1598

We use http://pragmaticworks.com/Products/BI-xPress - it takes the package and inserts the auditing framework which is configurable. Does cost a bit, but it's a good practice to evaluate what's available out there.

Upvotes: 0

Dominic Goulet
Dominic Goulet

Reputation: 8103

You need a master package, a "package handler package" and a child package per data flow.

First of all, in your master package, call the "package handler package" as many times as you have dataflows. For each, set a scoped variable that will have the package file path.

In the "package handler package", call a child package (using the parent package variable that holds the file path) that represent the dataflow and on success of the execute package task, call a script that will write down a variable that holds the number of rows in a file (or table, or wherever you want).

In each child packages, simply add a row count transformation to your package and write the value to the parent package variable.

So the sequence of packages looks like this :

               MasterPackage.dtsx
                 |             |
                 |             |
 PackageHandler.dtsx         PackageHandler.dtsx
        |                           |
        |                           |
 ChildPackage_1.dtsx         ChildPackage_2.dtsx

Note : There will be an error in your child package telling you that the parent package variable does not exists in this package, and it's all right. It will work at run-time.

Upvotes: 1

rvphx
rvphx

Reputation: 2402

If you dont want to keep adding the script task again and again, I would suggest creating a custom data flow component.

This would be a one time work but it can be reused again and again over variety of SSIS projects you may have.

Upvotes: 1

Related Questions