Process Advisors

*Subject to Terms and Condition
SSIS Optimization
Updated on 07th Apr, 23 3684 Views

What is SSIS? SSIS is known as one of the most reliable and widely-used data warehousing tool for the process of Extraction, SSIS Transformation and Load (ETL). Its noteworthy features are:

  • It is able to retrieve data from numerous sources
  • Performs ETL operations using ETL
  • Defines a process workflow

However optimizing SQL Server Integration Service is imperative to its better performance. Following tips will be useful for serving this purpose.

Enlarge the packet size

In the SSIS, the data transfer takes place by a packet size of 4KB. In the past times the data size was smaller and so this was proper to be used, but today the large packet size is required for transferring a large size of data.  So the data packet should be increased from 4 KB to at least 32 KB for handling larger files of data which will improve the throughput of the system. Configuring of the packet size is a must to make sure that no data loss has occurred during the time of a transaction.

Want to know what is SSIS? Read this extensive SSIS Tutorial!

Reduce the movement of the SSIS files

When there are a large number of files, the SSIS has to share the two resources that are the network and the input and output recourses as a result of which the process becomes slow. If the input-output is blocked then obviously the file movements need to be minimized. So for it, the storage should be accessed faster. The disks are local to each virtual machine but in reality they are actually being shared by all the virtual machines. The files are copied to each virtual machine which makes the system slow. It is found that SSIS package is best on working for larger files instead of working for a large number of smaller files. And since the files are transferred only once so it is best to combine all the small number of files into a smaller number of files to get a large file.

Allow bulk operations on the login

We have to allow SSIS to load data in bulk sizes and it can be done by allowing the login with the Bulk-Admin server role to load the data from the SSIS to the databases.  And along with bulk loading the loading should be in faster speed also.

Learn these Top MSBI Interview Questions to grab high-paying SSIS jobs!

Delete and then again create the indexes

Pushing data into an indexed table will take much more time than pushing the data into an empty table and then rebuilding indexes afterward. This is because while pushing data into an indexed table, the database engine has to make indexes together with the updates to the table, which causes a considerable delay.

When data is entered into the indexed table, the engine needs to develop the index and also update the table along with it, also, while entering the data index may break and this results in slowing down of the process. So the best way is to remove all the indexes on the final table then enter the data and then again create the indexes for the same table.

Learn new Technologies

Tweaking of SSIS is important

The maximum insert commit size tells about the largest size of data that can be entered before the transaction is allowed by SSIS. Its actual size is about 2147483647. For better performance this size needs to be tweaked.  It effects more if a large number of the SSIS packages are trying to enter data into the same table. This commit size also matters a lot in case data needs to be kept as back up because the SSIS may fail before the transfer of the data.

Allow Fast Parse on the SSIS file

While supporting data formats like date and time the file data source will give an output that will parse faster. This is possible only because of the Fast Parse in the flat file. If you can change the input file’s format then obviously fast parsing will help a lot.  So make sure when you build an output file you set this fast parse option on it. It can be done by following the command as below:

Flat File Source -> Show Advance Editor -> Input and Output Properties (tab) -> Flat File Source Output (node) -> Output Columns (node).

Get Mincrosoft SQL Server certified by enrolling in SSIS Online Tranining Course!

Course Schedule

Name Date Details
Business Intelligence Course 03 Jun 2023(Sat-Sun) Weekend Batch
View Details
Business Intelligence Course 10 Jun 2023(Sat-Sun) Weekend Batch
View Details
Business Intelligence Course 17 Jun 2023(Sat-Sun) Weekend Batch
View Details

Leave a Reply

Your email address will not be published. Required fields are marked *

Speak to our course Advisor Now !

Subscribe to our newsletter

Signup for our weekly newsletter to get the latest news, updates and amazing offers delivered directly in your inbox.