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?
Thanks.
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.
