Loading data from multiple db to another server us

Question

I have used SSIS package to move table columns and data from one database to another database.

SERVER A, Database A, Table A

SERVER B, Database B, Table B (but only with selected columns from Table A)

I have used Data flow task with OLEDB source and OLEDB destinations. Derived columns as well to create new date columns in Table B in SERVER B. I need to do this automatically to load 50 databases Table A from SERVER A to Table B (with selected columns from Table A) into SERVER B using SQL Job. Please can anyone suggest me how to nake database load as a loop.

Thanks.

Answer 1

I'm going to show you how to grab ALL the columns from the 50 tables of DatabaseA and load them into DatabaseB using SSIS. That package will look something like this.

I'll explain the logic flow.

The first Execute SQL task is going to get a list of tables from the Information Schema.

TABLE_SCHEMA|TABLE_NAME
MySchemaA   |MyTableA1
MySchemaA   |MyTableA2

We're going to take that list and save it as a ResultSet variable. Once saved, we can loop over the ResultSet using a Foreach Loop ADO Enumerator task. For each loop we're going to map values from one row into the TABLE_SCHEMA and TABLE_NAME SSIS variables. Then, we will use those values in dynamic SQL statement. The second Execute SQL task is getting it's command from the SQLStatement variable. We've set that variable by the following Expression ...

"SELECT * INTO DatabaseB." + @[User::TABLE_SCHEMA] + "." + @[User::TABLE_NAME] +" FROM DatabaseA."+ @[User::TABLE_SCHEMA] + "." + @[User::TABLE_NAME]

So for each loop, the schema and table name is injected into the SQL statement, and the table is SELECT * INTO a destination table of the same name.

Look here and here for more detailed examples about how to use the Foreach Loop ADO enumerator to map the variables.

Now...

I did read that you wanted

with selected columns from Table A

If you're lucky enough to need the exact same columnset from each of your 50 tables in DatabaseA then, just change the * in the query above to [ColumnA],[ColumnB],[ColumnC]. If you happen to need a different columnset for each table, well... that may be problematic. You'd need to create a list of TABLE_NAME | ColumnSet and integrate that into your looping... and well.. thats starting to sound like a lot of work! You could probably do the 50 by hand before you could code it. Hope this helps!

Answer 2

This means that the query is predictable as well as the destination table names.

You can make a loop through the tables you need either by putting their names in a table or getting them from INFORMATION_SCHEMA.

Inside this loop container, you can construct the source queries from the INFORMATION_SCHEMA then make the source component in the Data Flow use expression to use your query.