ssis package logging

[GShen]

Hi,
With sql2000 DTS, you used to be able to view the number of records which got transferred in the job log history. This would help when a user said they ran the job and I could verify how many records were transferred while viewing that particular step in the job.

Now comes bigger and better SSIS. When I view the job log history and look under the column MESSAGE. There is no place to see this count. It tells me that the job ran successfully. Great, useless, information. Is there some magical way to view the counts? I saw something about adding a log provider to the package. Is this what has to be done?

I am praying there is an easy resolution to this.

Thanks...

Remember when... everything worked and there was a reason for it?

 

[RiverGuy]

Add one or more Row Count transformations, and log them however you wish (to a table for example).

 

[GShen]

Are you telling me to modify every SSIS and somehow add a row count or add another step? I tried seeing how to do this. Is this something simple? Not sure how I would do it.

Before I start on that laborious task, is there any other way of getting this?

How come everytime Microsoft makes something better, they take away critical things?




Remember when... everything worked and there was a reason for it?

 

[RiverGuy]

There's a Row Count transformation you can place between your source and destination in your data flow. This is the way I have always done it. I add them to each flow, set a variable with them, and I have several components which log the information to my own, custom metadata tables. I add these to every package I create (except for the occasional one-offs).

I have package logging turned on as well, but it's more of a log of events in the package. You might go ahead and turn in on anyways, it's pretty painless to do so.

 

[GShen]

I added the data flow box for row count. What type of connection do I make it? Also, why do I need a variable? I just want it to display in the history log.
Thanks.

Remember when... everything worked and there was a reason for it?

 

[MDXer]

The way this is typically implemented is by implementing a customized logging solution. You can make the solution as wide or as narrow as you need. I log everything that takes place in my packages to allow me to target performance bottle necks or troubleshoot where a failure occurs.

Fron what it sounds like you are after you can get away with.

1) Variable for the record count. If you have multiple record counts you need to track in a package then you will need multiple variables. A row count transform in your dataflow where you assign the value to your variable. Finally you will need an execute SQL task that will execute the code or stored procedure that will insert the rowcount and other needed information into your audit table.

Micorsoft has taken very little away. In regards to what you need they simply changed how you go about getting it and allow the developer to tailor the logging to their needs.

The of SSIS packages as building an application. Yes you can sit down and start thrown things into it and end up with something that runs. Or you can figure out exactly what you need and then develop a framework within your packages that efficiently do what you require.