Post reply

Running SSIS packages from a Foreach Loop Container

  • June 5, 2008 at 1:52 pm

    #186524

    Hi,

    I have a set of 8 SSIS packages which are working fine when are executed individually.

    I was asked to create a SSIS package driver to execute and control each package in a certain sequence.

    For this I create a recordset which has the name and extension for those 8 packages in order that I need.

    The record set is assigned to a variable of the type object and passed to Foreach Loop Container. I also assigned a variable which get the curent package name during the looping. This variable is used by an Execute Package task which is inside the Foreach Loop container.

    To be sure I executed the package driver and evetring run fine but....

    I want to validate the current package execution after each loop because sometime data are wrong and I have to stop the loop when an error is found.

    What task may I use to validate if Execute Package Task was successfully before a new loop occurs.

    NOTE: I want to avoid Script Task.

    Thank you,

    Marian

  • June 6, 2008 at 6:15 am

    #825379

    If the SSIS package you are running fails, the execute SSIS package task itself will error. If you have not increased your max errors properties, the main package will stop executing if one of your sub packages fails.

  • June 6, 2008 at 7:52 am

    #825449

    Hi Michael,

    Thank you for your answer.

    I notified this when I forced one of the pcakge to fail, there the entire package drive failed but, how can I log which package is failing?

    Thank you,

    Marian

  • June 6, 2008 at 8:25 am

    #825480

    Marian,

    In each package (master and child packages) you can use the On Error event handler to write a row to an audit table, storing the error code, error description, error source, etc. I also include a task at the end of processing for each package to write a row to the audit table with run statistics. That way I can track both success and failure of the packages as well as performance characteristics. The format of my audit table is shown below.

    Hope this helps,

    Bryan

    Column_Name Data_Type

    ProcessingDate DateTime

    ModuleName Varchar(200)

    StartTime DateTime

    EndTime DateTime

    SourceMinEndDate DateTime

    SourceMaxEndDate DateTime

    RunStatus Varchar(50)

    TableLoaded Varchar(50)

    RowsInserted Integer

    RowsDeleted Integer

    RowsUpdated Integer

    RowsRejected Integer

    RowsWithErrors Integer

    Comment Varchar(1000)

    ErrorCode Integer

    ErrorDescription Varchar(1000)

    ErrorSourceID Varchar(1000)

    ErrorSourceName Varchar(1000)

    ErrorSourceDescription Varchar(1000)

  • June 6, 2008 at 9:19 am

    #825539

    You can also right-click on the control flow and choose logging to use the supplied log providers. One of them is a SQL provider that will log to a table.

  • June 9, 2008 at 12:55 pm

    #826280

    Hi,

    Yes On Error event handler solve my dilemmas.

    Thank you for everything.

    Marian

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

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