Developing a SQL SSIS Package development standards is important to standardise the development and implementation of SSIS packages , allowing efficient deployment and common terminology for troubleshooting. I’ve included an example list based on experience and good practise.
1. Schedule large ETL on production servers during out of business hrs or less busy periods to minimise contention
2. Before deploying a resource-intensive SSIS packages create a performance impact analysis and confirm the system can absorb the resource requests.
3. Use standard naming conventions. This will make it easier for you and SQL Team to debug or modify your packages.
Rename all default name and description properties using standardised naming conventions.
4. Include annotations in packages to make it easier to understand what is going on during the development period and post go-live
5. Use Sequence Containers to organize package structures into logical work units.
6. Scope variables for the containers for which they are required.
7. If data is already pre-sorted, use IsSorted=TRUE , therefore decreasing unnecessary sorts – releasing resources for other processes.Read about SORT optimisation on SORT IN TEMPDB and Sort Warnings
8. Return only required columns . Avoid selecting an entire table. Use OLE DB Source component or the Lookup Component to select data. Added benefit of efficient buffer usage
9. Use the SQL Server Destination for inserting data, instead of the OLE DB Destination to boost performance. Weigh up against ease of implementation
10. Prioritise usage of custom \tasks rather than script task based on reuse value
11. Performance Test everything before deploying on live. Use DTExec.
12. To conditionally execute a task at runtime use expressions on your precedence constraints. Do not use an expression on the "Disable" property of the task.
13. Connection Managers : Try and use clear and information rich names. Avoid using server
15. Use template packages for logging, event handling and configuration.Standardisation
16. Use a common folder structure,for deployment purposes, rather than to SQL Server .This is to ensure consistency across all environments Use the following structure: