SSIS: is there a way to generate dynamically some SQL task?

Question

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 7-Oct-15 22:28pm

Member 9907735

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Kuthuparakkal · Answer 1 · 2015-10-31T01:46:00

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