Solomon Rutzky (1/22/2015)
Kurt W. Zimmerman (1/21/2015)
Solomon, just wondering how much difference in performance is your DB_BulkCopy compared to creating an SSIS EXPORT/IMPORT package?
Kurt, I have no idea what the difference performance-wise is. I do know that most of the actual work performed to do the data movement is handled by the SqlBulkCopy functionality that exists in the .NET Framework, and that might be the same code that SSIS uses. If so, then it should be about the same. Obviously a stored procedure is not going to be multi-threaded like SSIS, but as I said before, the data transfer is direct from SQL Server to destination without going through SSIS and/or another server that might be hosting SSIS.
I also know that I can write a simple cursor to loop through the tables in a database and call DB_BulkCopy for each one (or even a subset or whatever) in the time it would take me to open BIDS (Business Intelligence Development Studio), create a new SSIS package, and place the first task in it ;-).
I like the concept of SSIS and think it is really powerful, but if I can avoid using SSIS then I avoid it.
Thanks Solomon.... The neat thing about creating the SSIS package is you don't need to go into BIDS to do it, just but just use the IMPORT/EXPORT wizard. However I did find that this has limitations and may not always work.
I agree with you simply spinning though a cursor is easy to write. What I've done was develop a control table that contains the name of the table and the status. This way I can run multiple copies of the same script without table contention.
I did this with the syncing process I wrote and had as many as 8-10 processes running at the same time. I had to watch the disk IOs, memory usage & NIC utilization because it was easy to swamp those resources and bring everything to a halt.
I attempted to use your DB_BulkCopy last evening with no success. Maybe if I can drop you a PM with the errors I was receiving to sort this out.
Kurt W. Zimmerman
New York, NY