SQL SSIS Package development standards

22 February,2013 by Tom Collins

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.

 Example Details

 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:







Read More

 SSIS version in SQL Server

SQL SSIS and service pack upgrade

Author: Tom Collins (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL SSIS Package development standards

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer