Post reply

SSIS - Can write a var in post-execute but can't use it in Input0_ProcessInputRow

  • March 5, 2010 at 7:12 am

    #219645

    I'm processing text files with about 1500 rows of text. For each row, I perform a lookup to find a foreign key in another table. If the lookup fails, I would like to write the file name to a table, just once, not once for each row. I thought I could accomplish this by setting an R/W variable to True after addidn an output row and writing the file name to it. Then when I process the next row, I would check the variable and if it's true, I don't do an output from the script. However, I can't read the value of the variable when I process the next row since it is only available in post execute. So, it seems to me that I can set the value of the variable in the post execute, but I can't use it the next time the script runs.

  • March 5, 2010 at 10:48 am

    #1129315

    If you're trying to use a Script - UR doing it wrong 🙂

    It even sounds like you're using the script to do the lookup - and you shouldn't.

    Use the Lookup component to do your lookup. In 2008, you can instruct the component to route rows that don't find matches to a "No Match" output. Do that. Take the No Match output and push it into an Aggregate component to either aggregate on the filename (if it happens to be in the flow) or just the "*" to get a single "count all" row. Put a Derived Column after that to set up your logging columns as you wish, and then send that to a Destination to write to your table.

    Todd McDermid - SQL Server MVP, MCSD.Net, MCTS (SQL08BI)
    Find great SSIS extensions - most are open source - at SSIS Community Tasks and Components[/url]
    Are you doing Data Warehouse ETL with SSIS? Don't use the slow SCD Wizard, use the Kimball Method SCD component instead.

  • March 5, 2010 at 11:32 am

    #1129351

    The script is just pulling out 4 columns for me, and then I have a lookup task to find the key. I'll give this a try right away. As soon as I saw the word aggregate, the light came on. Thanks.

  • March 16, 2010 at 12:06 pm

    #1134702

    I got back to this issue early yesterday. It worked as you said. Simple too. Thanks.

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

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