SSIS Best Practices |

T-SQL Tuesday #80 – SSIS Projects, Packages, and Deployments

Recently I was talking to someone who is just getting started with SSIS and wanted to know about the package deployment model. I hadn’t thought about a package deployment model in a while. TL;DR Do not use package deployment for SSIS. Why? Because SSIS code deployed in a project model is easier to maintain and migrate. Maintaining a large number of packages can be painful, something I know too well.

Making Maintenance Difficult One Package at a Time

Prior to SQL Server 2012, there was no project deployment. SSIS code was all deployed as packages. These packages could be stored within MSDB or they could be stored and run from the file system. In disorganized places like the one where I worked, they were deployed in both. Assuming nothing much changed since I left, they have all versions of SQL Server which were released prior to the day the new IT Director started in 2012. There was DTS on SQL Server 97, 2000 and SSIS on 2005, 2008 and 2008 R2. No reason to upgrade anything which still worked was their motto. When space was a problem, one could always go build another server. I think the LAN administrator was happiest when he was able to justify building a new server as he could spend hours shopping for parts on the internet and building the latest server.

I was given the task of supporting all of the SSIS code, which of course broke periodically. There were 300 packages on the myriad of different servers all named package1 which were deployed every way possible. As a bonus one could not trust the open source software control package to have the latest code, unless it was one I worked on previously. The hunt for where the code and the config file used to drive it, and getting access to where it was stored, was just part of the maintenance process. If one package called another package, then both packages needed to be found and reviewed. Each of those packages would have different ways of connecting to the same database too. My favorite was when one package called another package and they each used different IDs to access the exact same database. Ah the joys of troubleshooting SSIS Packages. It is was on the top five list of the reason I was very happy when I quit that job.

SSIS Projects

If you create write SSIS code and use a project deployment model, you can create one data connection for all the packages which need one. The code is deployed to one place, the Integration Services Catalog. All the related code is deployed to one folder. If you need to change a connection which all the packages use, you can do it in one place. You want to pass some parameters for all of the packages to use? No problem. SSIS project deployment offers some great advantages. I cannot think of any reasons to use a package deployment for SSIS 2012 and beyond. If you are writing SSIS code it is how you want to deploy packages.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Best Practices for the care and use of the SSISDB

Deploying SSIS packages since the release of SQL Server 2012 and beyond, has moved to the SSISDB database. If you are using SQL Server 2012 and beyond and are not using the SSISDB for deployment, my sincere condolences as maintaining and deploying packages any other way is a maintenance headache which thankfully has been resolved. As the SSISDB has not been used for very long, the appropriate care and feeding of this database is not well known. SSISDB is part of Integration Services Catalogs. It is not possible to create an SSISDB without first creating an SSISDB catalog, which does not happen when SQL Server is installed. Follow Microsoft’s instructions for creating a SSISDB Catalog, which creates the SSIS catalog and the SSISDB database. You cannot rename the database, as SSISDB is the name of the database that Integration Services uses internally.

Backup the SSISDB

A client asked me recently why he should back up the SSISDB database. While you can recreate everything inside of the SSISDB, it will take time and you will have to remember exactly how all of your variables were set. Restoring the backup decreases this issue and having a backup allows a server to be redeployed quickly. When you do back up the database, make sure that you remember to backup the database certificate, which is created when the SSISDB is created as well, as you will need this to do a restore. By default. the recovery model of the SSISDB is set to Full. If the packages in SSISDB are changing minute by minute, full would make sense, but given that an SSISDB contains packages which are run on a scheduled basis, most likely the changes made are infrequent. Change the recovery model to simple.

Managing SSISDB Growth Over Time

SSISDB contains all of the data used for the reports created when SSIS packages are run. Right click on the SSISDB icon underneath the Integration Services Catalog and take a look at the settings. The default settings are listed here, and to decrease the size of the SSISB over time, you may which to change them. The Retention Period is set to 365 days. Many environments don’t look at reports greater than 90 days, as information prior to that timeframe isn’t very meaningful. If that is the case, change the retention period to the number of days someone is actually going to look at the report, which will decrease the amount of data stored in the database. To get rid of the logs, the setting Clean Logs Periodically needs to be set to True, so don’t change it.

The Server-wide Default Logging Level is by default set to Basic. The information provided at this level is generally what is needed to troubleshoot any issues. Don’t set the logging to None just to save space. If the SSIS code ever crashes, the person doing this may be cursed. If you have simple jobs though, you may find the logging level of Performance may be adequate. Check out Microsoft’s documentation on Logging to better understand the differences between levels.

Lastly if using version control in another application, such as TFS, there may not be a need to set the Maximum Number of Versions per Product to 10. Generally speaking most people don’t look past the last 3 versions. The number should reflect what is practically, which is nearly always less than 10.