Wednesday, December 19, 2012

SSIS 2012 Favorites: The SSIS Catalog

SQL Server 2012 has some great enhancements to Integration Services. This is a series of posts on the new features in SSIS 2012 which are personal favorites and have been valuable this year. The intended audience is for people evaluating the software, thinking about upgrading, or wanting to learn more about SSIS 2012.


The Challenges of Yesteryear



In SQL Server 2005 and 2008, there were multiple methods of deploying and monitoring SSIS packages. Do a search online and you'll get many opinions on how to store packages on a server and monitor them either by applying one of various ETL frameworks (free or paid) or logging events by checking them off one by one in the package design. Its confusing when you have so many options that you can easily spend hours researching, testing, weighing options and evaluating the right solution for your SSIS implementation. Well thanks to SSIS 2012, deployment and monitoring is made much simpler using the new SSIS Catalog.
The Integration Services Catalog is the storage place and monitoring hub of your ETL packages in SSIS 2012. It does this with a new user database called SSISDB and a set of new configuration and report options available. Project and package parameters can also be configured in the catalog. Here's a glimpse of what the Catalog looks like in Management Studio:
The SSIS Catalog and user database that supports it.

Example: Deploying to the SSIS Catalog

SQL Server 2012 streamlines deployment to the SSIS Catalog; let's see how this is done.. In SSDT (formerly BIDS), there is a single Deploy option when you right-click on an SSIS project. Those of you that have been screaming for this functionality since SQL Server 2005 will have an 'aha' moment I'm sure. :)

This functionality is available so long as you operate in the new project-deployment model. I'll go into that in my next SSIS 2012 favorite post. For now, let's continue to focus on the Catalog.

After the new Deployment Wizard comes up, you are prompted for a Catalog folder for deployment to the server. You are able to put SSIS packages within any folder of \\SSISDB. If you'd like to create a new folder in the catalog, click on the New Folder button and select the location. Once asked to confirm all selections in the wizard, click Finish and you should see 4 green checkboxes indicating your packages, parameters, and project connections are now deployed to SSISDB as shown below.

Now onto the server. In my last SSIS 2012 Favorite post on Parameters, we configured packages in the Catalog and the SQL Server Agent, but never talked about how to setup logging which is a huge time-saver in terms of monitoring package performance and troubleshooting packages when they fail. Changing the logging options is a *super* complicated task when it comes to setting logging levels in the SSIS Catalog. Ready for it?

In the SQL Job Step under Advanced Configuration, you can pick how much logging takes place with a single drop down. Just kidding about being complicated! Its actually brain dead simple. The logging level options available are Basic, Performance, and Verbose which are explained in more detail by Matt Masson here. In general, the Basic option is suffice for troubleshooting errors and/or performance. Just in case you need to choose Verbose, make sure to either A) change it back to Basic or None when you're done or B) size SSISDB accordingly or you'll get errors that you've either run out of disk space or SSISDB cannot grow anymore depending on how you maintain db size.

Grand Finale: Monitor SSIS

Ok, so you deployed your package and set it up in the job. Now what? Well after your SQL Job has been running for a bit, you can start monitoring it without having to write a single report yourself or buying any 3rd party tools. Right click SSISDB in the Integration Services Catalog and go to Reports -> Standard Reports, and you will find many reports available to monitor and troubleshot packages running on the server.
Right-click menu for SSISDB reports
The standard report I use the most once I've got my ETL dialed-in is All Executions. I can drill-down from the Overview of an SSIS Package (usually a Parent package) all the way down to the Information events in a data flow pipeline of a child package. All of this you get without having to setup anything in the package itself. This will save you a ton of time in creating sprocs, event  handlers, precedence expressions, etc. This leaves us with time to focus on the core ETL or DB Maintenance task you're trying to accomplish. You can dig into the reports for SSIS 2012 on MSDN. Here are a few screen captures of the All Executions report, an Overview drill-through report from selecting a hyperlink of one of the execution runs, and then digging even deeper into the task-level messages:
Standard Report - All Executions

Standard Report - Overview of a package

Standard Report - Messages of a DFT Task
So there you have it: The SSIS Catalog, where you can deploy with a single-click and monitor with some simple reports. Simpler is always better, and it just doesn't get anymore simpler than this. Thanks Integration Services team!