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.
Get 100% Hike!
Master Most in Demand Skills Now!
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.
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.
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.
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).