Click here to Skip to main content
15,942,345 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear All,

I'm a beginner in SSIS.
I have a list of tables name for which I have to create a specific SQL Tasks. Each SQL Task has to run the same store Procedure, that accepts the table name as parameter.
So my question is: is there a way to read each name contained in this list and for each one create a new SQL Task dynamically?

Many thanks in advance for your kindly support,
Giuseppe
Posted

1 solution

Please try this.
1. Create an Object Type Variable at Package Level
2. Create SQL Task with SQL Statement as
SQL
SELECT 'YourTable1' AS [TableList]
UNION
SELECT 'YourTable2' AS [TableList]
UNION
SELECT 'YourTable3' AS [TableList]
UNION
SELECT 'YourTable4' AS [TableList]

Note: Change YourTable* with your table names.

3. Set the SQL Task Result Set to "Full result set" and map this results to the variable created in step 1
Ref: https://msdn.microsoft.com/en-us/library/ms141689(v=sql.120).aspx[^]

4. Create a String Type variable at Package Level (ParTableName).
5. Create a Foreach Container, and set it as ADO Enumerator and set ADO Object Source variable to variable created in Step1
Under Variable mappings select the variable created in Step 4 (its index will be = 0)
Ref: Using the Foreach ADO Enumerator in SSIS[^]

6. Now create a SQL Task inside Foreach Container. This will execute your store proc against tables
SQL Statement will be:
EXEC [YourStoredProc] ?
Now map a parameter your SQL Task
Ref: https://msdn.microsoft.com/en-us/library/ms140355(v=sql.120).aspx[^]

Let me know how it goes!

Thanks,
Kuthuparakkal
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900