SQL Server Integration Services (SSIS) Standards and Best Practices

SQL Server Integration Services Standards *The ideas provided in this document are not … several mechanisms to store log records generated from SSIS including Text Files, SQL Profiler … SQL Server Integration Services Standards*The ideas provided in this document are not “set in stone” and should be adapted to fit any specific business and technology needs. SQL Server Integration Services (SSIS) Standards and Best Practices Integration Services is an ETL (Extraction, Transformation, and Loading) tool designed to aid developers

o Generally, Dataflow source and destination item names should reflect the source or destination to which they are specifically connected. This could be a table or stored procedure within a database, or a flat file, etc ? Database Table Ex. dimEmployee ? Stored Procedure Ex. uspGetAllEmploye es • Connection Manager Names o Similar to Dataflow Sources and Destinations, the name of a Connection Manager should reflect the actual connection that they are making. ? Database Ex. ABCDataWarehouse ? Flat File Ex. NewOrders.csv o It is important to note that, by default, SQL Server will append the Host/Instance name to the beginning of the Connection Manager’s name. Generally, it is better to remove this as this may not accurately reflect the true name of the Host/Instance once a package is migrated from one environment to another. • Package Variable Names o Package variable names should describe their contents and use • Running Packages As a SQL Agent Job o When setting up a package to run on a schedule via a SQL Server Agent job, it is important to name it in such a way that a system administrator can easily identify it and, to some degree, be able to tell its overall function and/or to which project, program, or process it belongs. Package Documentation Thorough in-package documentation greatly aids in allowing more than one developer to understand the purpose of a given SSIS package and how it functions. It conveys meanings and ideas that simply interpreting the structure of a package or following along with the code cannot. • In Task Descriptions o Each Control Flow Task and Data Flow Item allows a user defined description to be written as part of the Task or Item properties. These Description properties should be utilized to further describe the functionality taking place within the task or item, any special circumstances, or really any other bit of information the developer feels would facilitate another developer interpreting and taking over development of a given SSIS package. • Variable Descriptions o As part of the properties associated with a package variable, a description field is made available. A developer should use this field to identify what the role of the variable will be and what data should be contained within it……