I have two almost identical (structurally) databases:

  1. Documents
  2. DocumentsOld

The original database existed before table partitioning was available, so the solution they found was to create the second database and migrate all the old data to it after each year. Now they want to merge both databases again.

I've done some research and I found that both database have just minor structure differences between tables and no IDENTITY columns.

So, aside from the minor structural differences, I'd say it'd be mostly possible to just INSERT values from the old database back to the first one.

The database is actually somewhat big and has more than 100 tables.

What I want to know is, would it make sense to use SSIS to do this, or is SSIS suited to this kind of job? Or should I just write T-SQL code to do the job?

As an additional question, are there any tools more suited than the first two options above to this kind of job?

  • 1
    Are you going to have to do it again next year, or 1 time thing? Mar 22, 2012 at 19:52
  • Nowadays, this task is executed every year. It's highly possible that this will never be needed again, though, so my primary concern is the cleanest and quickest way of doing this.
    – ivanmp
    Mar 22, 2012 at 19:58
  • I should add that I don't find T-SQL to be the cleanest and easiest way, though, so I'm actually looking for alternatives. There are too many tables, and writing T-SQL for importing each one of them is very error-prone and tedious.
    – ivanmp
    Mar 22, 2012 at 19:59
  • If you're going to reuse it I'd say go the SSIS /T-SQL route. If not I'm a big fan of Redgate compare tools. Super easy. Mar 22, 2012 at 20:36
  • my vote for SSIS.
    – Abdul Ahad
    Mar 23, 2012 at 5:18

1 Answer 1


I'd go straight SSIS myself. Besides having configuration, logging and error handling out of the box, you can get some really nice performance out of it.

You could probably save quite a bit of time by using something like EzAPI to script out the majority of the packages and then tweak the remaining 10%.

I recently used that approach to script out replicating ~150 tables from our Informix instance to SQL Server. Saved me a ton of time by getting the basics set up, all I had to do was fix some data type incompatibility between the systems. I'm behind on blogging about EzAPI but I could put together a sample if you're interested in the approach.


I put together an demo of replicating tables with SSIS EzAPI. You'd need to modify the values in ReplicateTables and then modify the query in GenerateTableList to ensure it's identifying all the applicable tables.

  • Yup! That's something along the lines of what I'm interested in! And thank you for your answer!
    – ivanmp
    Mar 23, 2012 at 13:08
  • I updated my answer to link to an article on how exactly the code could look to generate your packages. Let me know you need further help.
    – billinkc
    Mar 23, 2012 at 19:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.