Post reply

I want to use multiple Inputs into one Script Component

  • March 19, 2009 at 2:14 pm

    #155443

    I just read the SQL Server Central article called "Error Handling in SSIS" By Jack Corbett, 2009/04/03 (first published: 2008/04/21). I created a Script Component like the one he describes in the article so all my errors are fed into one error table with a meaningful description and an XML column with the details of each column.

    However, I would like to call the Script Component from three different Lookups in the same Data Flow Task. If any of the Lookups have an error, I want the row redirected to the same Script Component. Can I do this? Or do I need to have the Script Component three times in my Data Flow Task because there are three Lookups? I tried to do an Add Path but it is giving me an error because the input is not defined.

    Any help/suggestions would be appreciated.

    Thanks!

  • March 19, 2009 at 2:42 pm

    #963147

    The Script Component supports multiple outputs, but unfortunately does not allow for multiple inputs.

    What I've done with this in the past is to do a UNION ALL to merge all of my errors into a single data stream and then send it to my output (usually either a flat file or an Error table). The caveat to this approach is that you must ensure that the columns are similar enough for each of the inputs to allow you to merge them together. You may have to use a Derived Column transform in between your error output and the UNION ALL to get the output columns set up properly.

    hth,

    Tim

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • March 19, 2009 at 2:44 pm

    #963149

    In case it's not clear, when I refer to UNION ALL above, I mean the Union All transformation in SSIS, not the UNION ALL T-SQL statement.

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • March 19, 2009 at 3:02 pm

    #963170

    That worked perfectly! Just what I wanted. I just needed to think outside the box I was in.

    Thanks for your help!

  • July 3, 2018 at 1:25 pm

    #1996218

    I'm dealing with a similar problem.

    Would it be acceptable to use a view, or create a staging table, and use stored procedures to populate it with input for the data flow task?

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

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