none
How do I create tables using Foreach Loop Container?

    Dotaz

  • I got a bunch of .sql files each containing a script that would create a certain table.

    I want to create tables using these scripts in said files (each table to be created using one file).

    I got a foreach loop container that specifies the path and which files to use.

    I don't know how to configure the Execute SQL Task to execute the script in each one of these files in order to create a table.


    • Upravený Suq77 16. května 2018 8:01 Wrote .csv instead of .sql
    16. května 2018 6:45

Odpovědi

Všechny reakce

  • Assuming what that file is the create table script you can set SQLSourceType property value to File connection

    and point to a file connection manager created in your SSIS package

    The file connection will have an expression set for the connectionstring property and it will be mapped to the variable you create inside the loop to fetch the filename each time.

    This will ensure for each iteration of the loop, file connection manager will point to the corresponding file and execute sql task will execute the script inside it to create the tables.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    16. května 2018 7:23

  • The file connection will have an expression set for the connectionstring property and it will be mapped to the variable you create inside the loop to fetch the filename each time.

    This will ensure for each iteration of the loop, file connection manager will point to the corresponding file and execute sql task will execute the script inside it to create the tables.

    Thanks for the reply Visakh, but how do I do this exactly?
    16. května 2018 7:38
  • Hi Suq77,

    Please follow following article to learn how to use Foreach Loop Container to iterate files in a folder then assign it to variable.

    SSIS ForEach Loop File Enumerator  

    Then you should be refer to Visakh's step to execute the script file using the variable (it should be full path of script file) in Execute SQL Task inside ForEach Loop Container.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    16. května 2018 9:55
  • Could I get a step by step guide to executing the script file using the variable?
    16. května 2018 12:00
  • Hi Suq,

    Please follow this tutorial.

    How to Execute .SQL files by using SSIS Package - SSIS Tutorial

    Hope this helps.

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Označen jako odpověď Suq77 17. května 2018 3:45
    17. května 2018 2:29
  • Perfect! Thanks a lot
    17. května 2018 3:45