SQL 2014 - SSIS or stored procedures to copy data from SQL Server to SQL Server with the sa...

We have an SSIS project to load data from CSV to a staging area on SQL Server (DB_Stage).

The main purpose of Staging is to prepare the data ready to move to the Production database(DB_Prod) and in the process flag any errors with data or files.

DB_Stage is created by taking create table scripts from DB_Prod, so the table structure in both databases is same. Once the load to DB_Stage is successful then data need to be moved to DB_Prod.

I'm thinking to create a stored procedure for each table in DB_Stage to push data to DB_Prod as there is no transformation required and also thinking SQL to SQL is faster. However I read some articles which say SSIS has the capacity to parallel processing and load will be much faster. But I didn't understand it completely.

I can create another set of SSIS packages to move data from Stage to Prod using Biml with in no time. But I need some advice on which is the best approach. Stored procedures or SSIS packages in my scenario.

One advantage if I use SSIS package is I can configure the destination database, so Stage data can be loaded to any server/database (this is a requirement for us).

If I use stored procedures I don't find a way to parametarize the target database. It seems I must hard code this way...

insert into Prod_DB.dbo.Table1(col list) select (col list) from DB_Stage.dbo.table1.

Any help would be greatly appreciated.

As you have mentioned that you don't have any transformation to use in between dev and prod environment. I would recommend you to use SSIS instead of stored procedure. SSIS will consider this as a synchronous task and will start transferring record as soon as it can. SSIS can take advantage of buffer pipe line which you can control and achieve parallelism.

I would recommend few setting to be done when using SSIS in this case:

  • Avoid table locks on destination
  • Adjust max. number of rows & max. row commit size
  • If you are planning to transfer data for multiple table at once then set max. thread and buffer accordingly.

I am pretty sure you will see performance gain by using SSIS instead of TSQL here.