Post reply

Logging in SSIS

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

  • October 9, 2007 at 8:38 am

    #740392

    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?

  • October 10, 2007 at 5:08 am

    #740691

    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

  • October 10, 2007 at 6:24 am

    #740726

    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.

  • October 10, 2007 at 8:27 am

    #740837

    Thank You Andy

    for Sharing your knowledge with us.

  • October 10, 2007 at 5:25 pm

    #741024

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

    :{> Andy

    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

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

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