Copy tables from one server to another using SSIS

• sonal_anjaria

  SSC Eights!

  Points: 854

  More actions

  March 5, 2011 at 6:56 am

  #1294984

  Best case scenario would be to dynamically create the tables from Sybase to SQL Server and populate them.

  Would love to see some code examples.

  Thank you sonal.

  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.[/quote]

• Koen Verbeeck

  SSC Guru

  Points: 259115

  More actions

  March 8, 2011 at 5:33 am

  #1295790

  For the moment, Sonal, forget about datareaders and sqlbulkcopy. Those are .NET classes you can use in a script task.

  We'll go for the linked server strategy, as it is the easiest one.

  First of all, you can check if a linked server exists in SSMS by going to Server Objects --> Linked Servers. If there isn't a linked server to your source server (Sybase), create one. (example: http://support.microsoft.com/kb/280102)

  Why use a linked server? So you can use the OPENQUERY syntax to query your source server. http://msdn.microsoft.com/en-us/library/ms188427.aspx

  You'll need the following:

  a metadata table that contains the database, schema and table name for every table that you want to import. If needed, also for the destination.

  Create a package that gets the data from this metadata table. Loop over this table with a for each loop (using ADO.NET). This means that you'll serially import the tables.

  For every table, get the metadata from the source table. We'll use this to dynamically construct the SELECT statement. In every RMDB there are tables that contain metadata about the tables, such as INFORMATION_SCHEMA.Columns or SYSIBM.COLUMNS. Get the columnnames and the datatypes for the current table and store it in a resultset (object variable in SSIS). Create a script task that reads this resultset and creates the appropriate SELECT statement. It is possible that you'll have to convert some datatypes if they are not compatible between SQL Server and SYBASE. (So yes, get your hands dirty in .NET :-))

  Place the constructed SELECT statement into a OPENQUERY like this:

  SELECT * INTO myDestinationDB.myDestinationSchema.myDestinationTable FROM OPENQUERY('myLinkedServer', myConstructedSelectStatement)

  Store this statement into an SSIS string variable and execute it through an Execute SQL Task.

  The SELECT INTO syntax will create your destination table, so you'll need to drop them before the import if they already exists.

  You can expand this solution easily to incorporate WHERE clauses into the SELECT statement or to implement restartability.

  If you have any questions about a substep of this solution, feel free to ask.

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