Copy tables from one server to another using SSIS

Question

Post reply

Copy tables from one server to another using SSIS

sonal_anjaria

SSC Eights!

Points: 854
More actions
March 2, 2011 at 7:46 am

#231962

I am working on SSIS and I have a following business requirement.
Using SQL Server 2005
The source tables are all stored in a sybase database
Table A contains names of tables to be copied from Data Reader Source to OLE DB destination.
Read the names of tables to be copied from DB source to OLE DB destination from table A.
Copy table from source to destination ( by iterating through tables)
Repeat Step 4 till all tables are copied from OLE DB source to OLE DB destination.
Thank you Sonal.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

Hi Sonal,

Do you have the tables from source created in the destination database? Or you have to create them first?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 2

You can use a for each loop to loop over the table and store the tablename in a variable.

Then you can construct a dynamic SQL statement in an Execute SQL Task and execute it to fetch the data.

If necessary, add more for each loops to implement some parallellism.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

Koen Verbeeck (3/4/2011)
You can use a for each loop to loop over the table and store the tablename in a variable.
Then you can construct a dynamic SQL statement in an Execute SQL Task and execute it to fetch the data.
If necessary, add more for each loops to implement some parallellism.

Koen,

This will not work with the standard components, if each table layout is different. And I think this is the case.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 4

CozyRoc (3/4/2011)
Koen Verbeeck (3/4/2011)
You can use a for each loop to loop over the table and store the tablename in a variable.
Then you can construct a dynamic SQL statement in an Execute SQL Task and execute it to fetch the data.
If necessary, add more for each loops to implement some parallellism.
Koen,
This will not work with the standard components, if each table layout is different. And I think this is the case.

Therefore the use of dynamic SQL. You construct the whole SELECT and INSERT statement on the fly.

But, to be true, this will only work if the destination and source table have the exact same schema. If they don't, metadata should be collected about the tables.

But it can be done with standard components.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 246987 More actions · Answer 5

CozyRoc (3/4/2011)
Koen Verbeeck (3/4/2011)
You can use a for each loop to loop over the table and store the tablename in a variable.
Then you can construct a dynamic SQL statement in an Execute SQL Task and execute it to fetch the data.
If necessary, add more for each loops to implement some parallellism.
Koen,
This will not work with the standard components, if each table layout is different. And I think this is the case.

If you re-read Koen's response, you will see that he avoids the usual SSIS meta-data issues by suggesting the use of dynamic SQL, which would work, I think. Not elegant, I admit.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246987 More actions · Answer 6

Pah, you beat me to it!

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 7

Phil Parkin (3/4/2011)
Pah, you beat me to it!

Ha-ha. I call the victory of this Friday mine!

Now I can celebrate it all weekend long with the finest of beers!

Ahem... 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

COZYROC One Orange Chip Points: 28499 More actions · Answer 8

Koen Verbeeck (3/4/2011)Therefore the use of dynamic SQL. You construct the whole SELECT and INSERT statement on the fly.
But, to be true, this will only work if the destination and source table have the exact same schema. If they don't, metadata should be collected about the tables.
But it can be done with standard components.

Koen,

I guess you assume the "Linked Server" functionality will work properly? Because the requirement above is for transfer from ADO.NET Sybase database into SQL Server. Yes, in that case it would work properly.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 9

CozyRoc (3/4/2011)
Koen Verbeeck (3/4/2011)Therefore the use of dynamic SQL. You construct the whole SELECT and INSERT statement on the fly.
But, to be true, this will only work if the destination and source table have the exact same schema. If they don't, metadata should be collected about the tables.
But it can be done with standard components.
Koen,
I guess you assume the "Linked Server" functionality will work properly? Because the requirement above is for transfer from ADO.NET Sybase database into SQL Server. Yes, in that case it would work properly.

Hmmmm, it seems you have outwitted me on this late Friday afternoon. 😀

I forgot about the linked server thingy.

Allright, if there is a linked server, it is pretty easy and it can be done by using one OLE DB connection to the destination and with the method I described above (using dynamic sql to create a insert ... select statement or an select into statement if the destination table does not exist. In both cases, openquery should be used).

If there is no linked server and there isn't an option to create one, and the requirement of looping over a table with the tablenames still stands (so you just can't simply use the import/export wizard), then I would create a script task and do it in .NET:

1. Create two OLE DB connections (as stated by the OP), one for the source, one for the destination.

2. Attach a DataReader to the source connection.

3. Attach a SQLBulkCopy to the destination connection.

4. Attach the DataReader to the SQLBulkCopy and transfer the rows.

Note: again, if the schema's aren't exactly the same, metadata should be collect so that the SQLBulkCopy mappings can be constructed.

I've done this before, so if code examples are needed, drop a message.

Note #2: there are probably multiple solutions, I've listed only the ones I'm familiar with.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

COZYROC One Orange Chip Points: 28499 More actions · Answer 10

Koen,

The programming solution will work. However it is too demanding for many users. Another solution would be the CozyRoc commercial solution Data Flow Task Plus. It can retrieve the source and destination component's metadata at runtime and it doesn't require programming.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 11

CozyRoc (3/4/2011)
Koen,
The programming solution will work.

I know 😉 :hehe:

CozyRoc (3/4/2011)
However it is too demanding for many users.

True. I've spent a good week trying to figure it out myself.

CozyRoc (3/4/2011)
Another solution would be the CozyRoc commercial solution Data Flow Task Plus. It can retrieve the source and destination component's metadata at runtime and it doesn't require programming.

Ah, I was wondering when the sales pitch would show up 😀

I'm sure it will work like a charm. However, if 3rd party tools aren't allowed in production - which is the case in some companies - we're back at the start.

Aside from that, at CozyRoc, is it possible to buy one component only, or is it more "all or nothing"?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

COZYROC One Orange Chip Points: 28499 More actions · Answer 12

CozyRoc (3/4/2011)
Another solution would be the CozyRoc commercial solution Data Flow Task Plus. It can retrieve the source and destination component's metadata at runtime and it doesn't require programming.
Ah, I was wondering when the sales pitch would show up 😀
I'm sure it will work like a charm. However, if 3rd party tools aren't allowed in production - which is the case in some companies - we're back at the start.
Aside from that, at CozyRoc, is it possible to buy one component only, or is it more "all or nothing"?

I know you knew it was coming 😉 The solution works really nice. The argument about not using 3rd party tools "falls flat on its face" when you find that many of these same companies are willing to execute external applications from their SSIS packages, to get the job done. But this is not considered "3rd party tools" ...

The CozyRoc licensing is "all or nothing". The "individual component system" doesn't make business sense.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 13

CozyRoc (3/4/2011)
The solution works really nice. The argument about not using 3rd party tools "falls flat on its face" when you find that many of these same companies are willing to execute external applications from their SSIS packages, to get the job done. But this is not considered "3rd party tools" ...

It is not me you have to convince. I suggested someone else in this forum to install BIDS Helper and even that was not allowed, despite that it is an open source tool.

I think some companies have the following reasoning:

"We pay you to do it. Stop making us pay for other tools to do your job."

CozyRoc (3/4/2011)
The CozyRoc licensing is "all or nothing". The "individual component system" doesn't make business sense.

Ah ok. There isn't any chance for a trial version or something like that?

I'm a consultant myself, so I can't buy the licensing, but I can advise clients to do so if that is the best solution for a given problem.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

COZYROC One Orange Chip Points: 28499 More actions · Answer 14

"We pay you to do it. Stop making us pay for other tools to do your job."

Weird logic. But you can easily explain it will cost N times more to build the same and they could decide if it is worth pursuing it.

CozyRoc (3/4/2011)
The CozyRoc licensing is "all or nothing". The "individual component system" doesn't make business sense.
Ah ok. There isn't any chance for a trial version or something like that?
I'm a consultant myself, so I can't buy the licensing, but I can advise clients to do so if that is the best solution for a given problem.

The licensing is very liberal. When you test and develop from Visual Studio (BIDS), no license key is required. You can have as many developers as you want, without paying a dime. A license key is required when you want to execute your packages under SQL Job Agent. You can get 30 days trial key, when you want to test under SQL Job Agent.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/