Should I use SSIS or stored procs to copy data from a number of database tables on one server to database tables on a different server?

Each table's structure is identical to the destination server except for an identifier added to the destination table. There is no real data transformation. The process would get called from a user application.

My thoughts on using SSIS is that each copy process can run simultaneously. Can I do this with stored procs?

Any suggestions?


  • 2
    Is this going to be a 1 time copy or something you'll do on a routine basis? – Thomas Stringer Jan 23 '14 at 17:11
  • IF the tables have FK's then it would be tricky using SSIS. If it is a one time transfer, you can even BCP-OUT/IN the data. – Kin Shah Jan 23 '14 at 17:31
  • 2
    What does "each copy process can run simultaneously" mean? Why does SSIS provide more benefit there than stored procedures? Two different users can call an SSIS package or a stored procedure at the same time. Your challenge in both cases is that if they both ask to move similar (but not identical) data from/to the same tables, who wins? – Aaron Bertrand Jan 23 '14 at 17:37
  • 1
    Agreed with @AaronBertrand. The tool isn't what determines whether or not data transfer happens in parallel, it's the implementation specifics that'll demand that. – Thomas Stringer Jan 23 '14 at 17:49
  • How would I asynchronously call 2 stored procs? – billinkc Jan 23 '14 at 19:46

Seeing some comments saying not to use SSIS for this, but my vote would be for using SSIS based on the fact that you said that the data would be transferred between servers. If you used a stored proc, you would have to have a linked server setup.

And yes, using SSIS would allow you to run your extract and load on your tables in parallel. It would also give your more flexibility to do things like dump your extract to raw files to get that done quickly without putting as much pressure on your source system and then import the raw files on your destination server where it might take longer.

I also guess you could use replication, but if you don't have replication already installed and configured, that would be a bigger lift to get up and running then writing a simple SSIS package that could do all this for you, in my opinion.

  • Well my recent experience with SSDT and linked servers would second the SSIS approach. Linked servers also have some limitations that can be a pain. – nojetlag Feb 6 '14 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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