Database Management
Great SSIS Package Tuning Tips While Working With Task Factory And Good General Practices For SSIS
The purpose of this how-to article is to cover topics around SSIS where Task Factory can assist in boosting performance and other 3rd party resources on tuning an SSIS package for better performance and good practices in SSIS.
First published date
10/18/2022 7:47 PM
Last published date
5/5/2023 8:06 PM
Overview
SSIS in general is a memory-intensive application. There are several things in SSIS and using Task Factory can optimize the memory.
You can always adjust the DefaultBufferRows in the properties of the data flow. The default value is 10,000 rows that flow through the buffer, and you can gradually increase it as long as your server can handle it, but increase it incrementally for best results.
The TF Upsert Destination is great for boosting performance compared to the native OLE DB Destination. Because the native component inserts each row one at a time and opens and closes a connection as it goes through this process it will take a toll on the performance. The TF Upsert Destination has the option to insert and update using a bulk statement, which boosts the performance.
Overview of Inmon and Kimball Methodologies for Data Warehousing Concepts SSIS Package Design:
Good Security Practices in SSIS:
Additional 3rd party resources below for SSIS Package Tuning:
Enhancing SSIS ETL Tools with SolarWinds Task Factory
You can always adjust the DefaultBufferRows in the properties of the data flow. The default value is 10,000 rows that flow through the buffer, and you can gradually increase it as long as your server can handle it, but increase it incrementally for best results.
The TF Upsert Destination is great for boosting performance compared to the native OLE DB Destination. Because the native component inserts each row one at a time and opens and closes a connection as it goes through this process it will take a toll on the performance. The TF Upsert Destination has the option to insert and update using a bulk statement, which boosts the performance.
Overview of Inmon and Kimball Methodologies for Data Warehousing Concepts SSIS Package Design:
- Use the Inmon methodology to design a normalized data warehouse, and create separate dimensions and fact tables. This helps to ensure data integrity and reduces data redundancy.
- Use the Kimball methodology to design a dimensional data warehouse, and create a star or snowflake schema. This makes it easier to understand and analyze the data and provides faster query response times.
Good Security Practices in SSIS:
- Use package protection levels to secure sensitive information in SSIS packages. You can choose between four different protection levels: Do not save sensitive data, Encrypt sensitive data with a user key, Encrypt sensitive data with a password, and Encrypt all data with a password. Select the appropriate protection level based on the sensitivity of the data.
- Use Windows authentication for connections to data sources, and limit access to the data sources to only those users who need it.
- Store package configuration files securely, and encrypt sensitive data within the configuration files.
Additional 3rd party resources below for SSIS Package Tuning:
Enhancing SSIS ETL Tools with SolarWinds Task Factory
SQL Server Integration Services SSIS Performance Tuning Techniques
Top 10 Methods to Improve ETL Performance Using SSIS
Top 10 SQL Server Integration Services Best Practices
Access Control for Sensitive Data in Packages
Data Warehouse Design - Bill Inmon Vs Ralph Kimball Approach
Product section
Task Factory (TF)
Cause
N/A
Resolution
An SSIS developer needs to use their best judgment and remember that SSIS is a memory-intensive application and all of the resources in the overview are a great guide.