SSIS Best practices

SSIS Best practices

1. Readme.doc – explains the thorough purpose and functionality of the package.
2. Names for all tasks, objects, and connections should be self descriptive about the purpose. Never hard code specific database or server in names since it might change.
3. Modular structure of packages i.e. multiple number of packages is preferred over one big package to cover a big complex functionality.
4. SSIS has a significant amount of built-in functionality; use that instead of custom scripting.
5. Use configuration files to take the connection string or other properties which might change and export only configurable values.
6. Use SSIS built-in logging capabilities to log serious package error conditions and any additional information that may be useful for troubleshooting the package.
7. Use checkpoints in your packages i.e. the tasks need to be organized in a logical manner and if it makes sense, group them together via the Sequence Container to allow for the logical restart of the group if one of the tasks inside the container failed.
8. Store SSIS Configuration files in the designated folder given by DBA and it should be accessible from all the environments.
9. We should manage the configuration files to allow the same SSIS package to run in dev, test and prod environments.
10. Learn how to create a SSIS configuration file.
11. We should structure SSIS packages and configuration files with the goal of minimizing the number of different SSIS packages deployed, especially in cases where SSIS packages are very similar to one another except for some key properties, such as server and database names

12. Developers that need to connect to SSIS on a server will need to be granted membership to the “Distributed COM Users” group on that machine.

13. Learn how to deploy a package.

14. We can create SQL serve Jobs in SQL Agent to execute SSIS package.

15. Security

Protection Mechanism Details
Set protection level of packages Protection Level: ServerStorage

The protection afforded is based on SQL Server database roles. The protection level will rely on server storage and roles for access control. All packages saved in MSDB will use this protection level.

Save packages to MSDB Package Storage Location: MSDB

Saving packages to the msdb database will help provide security and the server, database, and table levels. Packages saved in MSDB will benefit from the integration service database level roles (db_ssisadmin, db_ssisltduser, db_ssisoperator). Any other options aside from saving to MSDB will be evaluated in a case by case basis and discussed with the database administrators and the information security office.

Save logs to MSDB Log Location: MSDB

Logs to MSDB. This enhances the availability of log data to tech supports outside of SQL Server. Other advanced logging options outside of SQL Server will be evaluated in a case by case basis and discussed with the database administrators and the information security office.

Save configuration in XML format Configuration Location: File system (XML format)

Ensure that configuration file has no sensitive data (e.g. connection strings) and the file and folder it is located is properly secured. Contact the appropriate database administrator and the Information Security Office for assistance for securing the folder.

Use of SQL Jobs in Package Execution Package Execution: through SQL Jobs

All packages should be executed from a SQL job. Packages executed other ways (e.g. Windows Scheduler) need to have a notification process established and should be an exception of the rule.


16. Monitoring folders for dropped files.
17. moving files from one file to another.
18. Copying data from an excel spread sheet into SQL Server.
19. Logging.
20. SSIS Naming conventions
companyname_application_taskDescription

Verify that the name and description of the task of component sufficiently describes it. Often times, the default name and description assigned to a task or component may not clearly or accurately indicate its purpose.

Prefix each task or component’s name with an identifier to describe the type of task or component it is.

[SSIS Package Name]_[Environment Name].dtsConfig