Thursday, 14 May 2015

Load data to OLEDB Destination in SSIS by using Fast load

What is fast load property in OLEDB Destination? By using this option is Increase Performance tuning of package?

       OLE DB Destination is available in the Dataflow task in SSIS, which provides a couple of options to push data into the destination table.
  • First one is “Table or view” option, which inserts one row at a time.
  • Second is “Table or view fast load” option, which internally uses the bulk insert statement to send data into destination table. It’s always provides better performance compared to other options. Once you choose “fast load” option it gives you more control to manage the destination table behavior during various options like Keep identity, Keep nulls, Table lock and Check Constraints.

Set fast load property in OLE DB Destination
1. Double-click the OLE DB Destination and select “Table or view - fast load” in the Data access mode menu.

2. In Fast load option, we can specify the sort order in the Editor. To access this editor, right click the component and click Show Advanced Editor, and then select the Component Properties tab as shown below.

3. Click OK.

Example: To transfer 2 lakh’s records in a flat file to database by using fastload option.
• By using Table or view

• After that we use “Table or view – fast load” and Sort order by ID.

So speed gain is quite large when using Fastload.
I was also surprised that SSIS –With Fastload method was faster by 80% than with out using Fastload in OLEDB Destination.

Let me know what you think about this article.

No comments:

Post a Comment