Best practices for SSIS
SSIS stands for SQL Server Integration Services. I have included few guidenlines which will help beginners of SSIS. Its always better to follow certain best practices while using SSIS so that performance tuning can be achieved with less efforts.
1] Components usage Use SSIS components only when it is extremely required.
2] Creation of SQL job If you are creating any SQL job then divide tasks in to smaller steps and create multiple steps so that readability will be more and maintenance is also easy.
3] child package call If you are executing child package from master package then parameters which are passed from master need to be configured in the child package.
4] Typecast usage You can change it to source level to avoid unnecessary type casting.
5] Sort operation: Its good practice to do sort operation at database query because it takes much more time if we do sort operation in SSIS.
6] Atomicity of transaction: Its always better to use SSIS where atomicity of transaction is present even though ETL can process large amount of data.
7] Trimming of package names: If your package name is more than 100 characters then it gets truncated when that package is deployed in to SQL server. You may get execution failure error in such scenario so to avoid this, limit the package name to maximum 100 characters.
8] Select query Whenever there is necessary to pass column names from the source to the downstream, avoid select * from table name and write necessary column names instead of *
9] Package protection level : Its always better to set the package protection level to 'DontSaveSensative' so that when packages gets deployed from one system to another system, deployment errors gets resolved.
10] Package name as configuration filter Its good habit to use a particular package name as a configuration filter for all the configuration items that are specific to the package.
11] WITH (NOLOCK) on tables It prevents keeping locks on the tables in SQL queries so that concurrency and performance will be improved.
12] Support of managed code There is no unmanaged code in SSIS. The Script component, the script task and the custom components give users power of managed programming language with less infrastructure code to write.
