Load data to multiple tables in SQL Server using SSIS

0
votes

I'm trying to find out how to load data from/to multiple tables in SQL Server.

I have 30+ tables in source and target database (both have the same table names and columns), so I used a foreach loop container to loop through each of the table with 1 DFT. I also have variables (SourceQuery & TargetQuery) that holds the query to use in ole db source/destination.

But when I use the TargetQuery in ole db destination I'm getting this error:

error message

Anyone know a proper way to use a variable in the ole db destination? Or any other solution would be great as well.

1
sqlsql-serverdatabasessisintegration
Does ehs.asm_completion exist in your database? - Amira Bedhiafi
You can't load 30 different tables with one DFT. You need 30 seperate DFT's for that. Metadata must unforuntately be explicit in SSIS. Your error actually looks like it can't find the table though. - Nick.McDermaid
BTW if this is the target, you just put the target in: ehs.asm_completion, not select * from ehs.asm_completion. You should have really just got this working for one table before you tried making it dynamic. - Nick.McDermaid
@Nick.McDermaid What do you mean you can't load 30 tables in a data flow task? I'm pretty sure that's the most efficient way. You can't use a single source/target obviously, but you can add multiple. They will run in parallel. - Jacob H

1 Answers

0
votes

To copy 30 tables I recommend using the "Transfer SQL Server Objects Task" component that is much more efficient than using a data flow

here you can see how to pass variables

Transfer SQL Server Objects Task