Post reply

Execute SQL script from file in for each loop - SSIS package

  • July 8, 2010 at 8:29 am

    #387301

    I'm trying to loop through a folder which contains *.sql script files and i want to execute these one at a time in an SSIS package.

    I have a Foreach Loop Container set to Foreach File Enumerator and i'm storing he fully qualified file name in a local variable call User::FileName.

    Where I'm stuck is transfering that file to the Execute SQL Statment control within the Foreach loop, and getting it to execute.

    I have the SQLSourceType set to File Connection on the Task, but I cannot seem to get the FileConnection to set dynamically based on the one selected in the Foreach loop.

    Can somebody help? I think I'm very close, just missing one small piece.

    thanks.

  • July 8, 2010 at 9:34 am

    #1190329

    The piece you're missing is to place a property expression on the File Connection Manager.

    Select the File Connection Manager, then open the Properties pane (F4). You'll see a "ConnectionString" property where you'll see the "static" file name you typed in to design your package. You'll also see an Expressions property. Select that and press the ellipsis (...) button. Pull down the property list and find the ConnectionString property. In the expression area, press the ellipsis button. In the editor, open the Variables node, find your FileName variable and drag it into the expression area. Press "evaluate" to test, then "OK" twice to exit. You may want to set the DelayValidation property of the File Connection Manager to true as well to prevent validation errors if your initial "static" filename won't be in the folder all the time.

    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.

  • July 8, 2010 at 10:55 am

    #1190380

    thank you....that worked.

    the piece i was missing was setting the DelayValidation property.

    much appreciated.

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

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