Logging in SSIS

Question

Logging in SSIS

dharti

SSC Enthusiast

Points: 193
More actions
September 28, 2007 at 11:17 am

#118841

how to create log detail for each task in ssis. for example
in data transfer i need to get how many rows inserted in my table
in my log file.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Roger Scott SSC Veteran Points: 223 More actions · Answer 1

I would also like to hear any ideas about this issue. The task-level logging provided by DTS was helpful for not only evaluating past history, but also for determining which task was actually being executed in real time. How do we do this in SSIS? I've been reviewing the SSIS logging literature and so far, it appears to be much more limited than what we had in DTS. Any comments? Any ideas or suggestions for implementing this functionality?

Andy Leonard SSChampion Points: 10144 More actions · Answer 2

Logging in SSIS is different from DTS.

There are a few ways to obtain the row count from a Data Flow Task. The OnPipelineRowsSent event will provide information in the Message column of sysdtslog90 that looks like [font="Courier New"]"Rows were provided to a data flow component as input. : : 21 : OLE DB Source Output : 16 : Flat File Destination : 17 : Flat File Destination Input : 397"[/font].

One OnInformation event (provided you are logging OnInformation events) will log a Message similar to [font="Courier New"]"component "Flat File Destination" (16)" wrote 397 rows."[/font].

You could add a RowCount to the Data Flow Task that pushes the number of rows into a variable, then use a Script Task to fire an OnInformation event (provided you are logging OnInformation events) with code similar to:

Dim iRowCount As Integer = Convert.ToInt32(Dts.Variables("RowCount").Value)

Dts.Events.FireInformation(-123, "", iRowCount.ToString & " rows sent", "", 1, True)

You could create a custom logging solution as well. I blog about that here, and you can also use concepts and code here and here.

Hope this helps,

Andy

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

Roger Scott SSC Veteran Points: 223 More actions · Answer 3

Thanks, Andy. I suspected that a custom solution was probably the way to go, but I haven't seen any details anyone was willing to share. I appreciate the links to your blogs...they look excellent and I'll pass these along to our ETL developers. I'm also going to encourage them to start tracking your blogs, in addition to SQLServerCentral.com.

dharti SSC Enthusiast Points: 193 More actions · Answer 4

Thank You Andy

for Sharing your knowledge with us.

Andy Leonard SSChampion Points: 10144 More actions · Answer 5

You're most welcome! Thanks to Steve and the Red-Gate folks for this forum.

:{> Andy

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics