The SSIS catalog comes packaged with a rich set of built-in reports that give those monitoring Integration Services a window into what has been happening inside the catalog. However, if you work with SSIS long enough, you’ll eventually need to write your own queries against the underlying tables. Of the many things the built-in catalog tables and views do well, one of its minor shortcomings is that it doesn’t identify SSIS execution status by name anywhere in the database. Although it’s easy to remember the common ones (7 for succeeded, 4 for failed, etc.), there ought to be an easy way to get at those status values.
SSIS Execution Status
Several years ago, fellow MVP Jamie Thompson recommended posting a sticky note on your monitor to remind you of what each of these status IDs represent. I like that suggestion – in fact, I did this very thing for quite a while. The more work I did against the SSIS catalog, though, the more I wanted to have that information easily accessible in the catalog. To solve that problem, I created a separate table to store these status ID along with their names.
A simple version of the script I used to create this table is below.
The above table can be joined to existing tables or views to report the name of the status. The status values are officially listed on Microsoft’s MSDN website here.
For the SSIS execution status script I used above, there are a couple of design notes below to keep in mind.
First of all, the script creates a separate schema named toolbelt in which the new table is created. I did this to avoid potential issues in case a future update to the SSIS catalog were to drop and recreate the catalog schema. In fact, in the production version of my SSIS toolbelt that I use, I create an entirely separate database in which I place my custom SSIS objects, out of an abundance of caution to prevent name collisions or potential upgrade issues.
Next, I included in this custom table a status group for each status to group together currently active, failed, and successful executions for easy reporting. This is not essential, but useful to group together statuses for reporting purposes. You might choose to group these statuses differently than I did.
Finally, I opted to include indexes on this table for performance reasons. It probably won’t have a significant impact since there are only nine rows in the list of statuses, but the indexes are in place should they ever be needed.
Though not a game-changer, this script to create an SSIS execution status reference table has helped me quickly reconcile status IDs to their label equivalents, and continues to be useful when building custom reports against the SSIS catalog. I hope you find this snippet as useful as I have.
Nice article Tim. Thank you. Wondering why did you put both 6 and 9 as ‘Failed’. What will be the difference? Apparently to me 7 and 9 appear similar.
Hi Taiob, the “Failed” identifier you mention is the group, not the specific identifier for that status. Note that each status has its own label, and is grouped in to a status collection of Failed, Succeeded, or Active (running). This is to make the reporting easier, so you don’t have to use a lengthy IN() statement to check for several different statuses.
Do you know the order of the status from when an SSIS is first executed to successful completion. My initial thought is status 2 then 7 however that may not be the case.
Nancy, the normal progress for a successful execution would be to start with a status of 1 (Created), followed by 2 (Running), and then 7 (Succeeded). I suspect that there might be a possibility to see a status of 5 (Pending) if there is any queueing in the SSIS catalog, but I haven’t seen that behavior in my testing.