SSIS – Choosing the Proper Destination for Performance

Introduction

In this article we are going to make the differences between OLEDB Destination without Fast Load, OLEDB Destination with Fast Load and SQL Server destination.
By all of that we can insert records in our SQL Server Destination Table object. Here we are going to demonstrate the differences between them that we can choose the Proper option where performance is the key Factors.

Hope after reading this article we can choose the best for us and hope it will be enjoyable.

What the Scenario

The scenario is simple one. Just read a Flat file from source and put it in the destination table. Here we more focus on Destination component and try to choose best for our project. Hope you understand the scope of work and scenario.

How we Differentiate it

The data flow tasks

Run with OELEDB Destination WITHOUT Fast Load

Now run it and if we check the Profiler we find that

Data is inserted one by one using sp_Cursor. Look at the parameter named @stdroll and values. If the data is inserted one by one obviously it will takes time when source have huge data.

Run with OLEDB Destination WITH Fast Load

Now run it and if we check the Profiler we find that

As we see that the data is inserted in Bulk, so it improves the performance.

The Fast Load Max Insert Commit Size property of the OLE DB Destination is used to determine how many rows should be committed as a single transaction when using the Fast Load option

Run with SQL Server destination

If we chose the SQL server destination the SQL Server must exists in the same box where the SSIS package exists. The SQL Server destination cannot use the Remote/Network SQL Server.

Now run it and if we check the Profiler we find that

BULK INSERT [dbo].[tbl_sample]

FROM 'Global\DTSQLIMPORT              00000000000020c40000000000325598'

WITH (DATAFILETYPE = 'DTS_Buffers', CODEPAGE = 'RAW', CHECK_CONSTRAINTS, TABLOCK)

Hope you like it.

Posted by: MR. JOYDEEP DAS