Click here to Skip to main content
14,384,785 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have a single sql server on which there are multiple databases and inside all the databases there are tables.

So the situation is like below:-

DB1> table1
DB2> table2

I want a package to dynamically pick the db and inside that the table and push data to flat file.

What I have tried:

I have created a master table consisting of servername,tablename,databasename and the entire connectionstring and I have stored the result of the above in object variable.

below this I have a foreachloop container which has been mapped to the enumerator configuration variable and under this to the same object variable.

Also the variable mappings has also been done.


Inside foreach loop container I have placed a dft and created one more connection manager which takes connection string as expression and in that I map the complete connectionstring variable.

when I run this the loop runs twice but the data is copied for the default connection only.
Posted
Updated 4-Nov-19 6:11am

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Write a stored proc to build/execute the dynamic sql, and call the stored proc from your package. Then, the package can save the returned data as a file.
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100