Package Store

Overview

This is not the SSISDB catalog.

Packages can live in three places: the Integration Services catalog, the file system, and this table, which is the oldest of the three and the one nobody remembers. The SSIS pages elsewhere in this product read the catalog; nothing reads this, and on instances that have been upgraded since 2008 it is usually where the packages actually are.

The Package Store page: the packages stored in msdb itself
The whole page. Eight packages in four folders, and the verdict on every row saying that no job step on this instance names any of them.

Where to find it

Expand a server in the tree, expand the msdb database, then MSDB → Package Store.

What it can say

Every package, with its real folder path

The table stores a parent id and nothing else, so a package four folders deep looks like a package at the root until the tree is walked. This page walks it.

Which packages an Agent job actually runs

Matched by comparing the SSIS step commands against the package names. A package nobody runs is either a leftover or somebody’s deployment that was never wired up, and both are worth a line.

The maintenance plans hiding among them

A maintenance plan is stored here as a package with type 6. A page that counted rows would report an instance with four maintenance plans as running four SSIS packages.

Packages protected with a password

Those cannot be opened or run anywhere else without the password, which is a migration problem that only appears on the day of the migration.

Reading the chart

The bars: package size in msdb, largest first
Eight data collector packages, largest first. Every one of them sits in the same folder path, which was rebuilt by walking the parent ids.

One bar per package, by the size it takes in msdb, with the folder and the kind under the name.

Size rather than count, because the package XML is what holds the space here. The folder is part of the label because the store keeps a parent id and nothing else until that tree is walked.

Reading the grid

The grid: folder, kind, owner, size and what runs it
Run by names the Agent job step that runs each package. Blank there, as on every row here, is either a leftover or a deployment nobody wired up.
Column What it is
Package The package name.
Folder The full path, rebuilt by walking the folder tree.
Kind SSIS package, legacy DTS package, or maintenance plan.
Owner The principal that owns it.
Size The length of the package XML, read as a length rather than as content.
Created When it was added to the store.
Version The package version, and its major and build numbers.
Run by The Agent job and step that runs it, or blank.
Verdict Whether this package is run by anything, whether it is a plan, and whether it is password protected.

The package bytes are read as a length, never as content. packagedata is an image column holding the whole XML of the package, and selecting it would pull megabytes across the wire to show a name and a date.

The toolbar

Control What it does
Maintenance plans Opens Maintenance Plans, for the type 6 rows.
What the jobs run Opens Job Commands.
Space and retention Opens msdb Space and Retention, because package XML is real weight.

Where the data comes from

Source What it gives
dbo.sysssispackages The packages: name, id, description, packagetype, packageformat, owner SID, createdate, vermajor, verbuild, packagedata length, and encryptionlevel.
dbo.sysssispackagefolders The folder tree, walked to rebuild the path.
dbo.sysjobsteps The SSIS step commands, matched against the package names.
sys.server_principals The owner name behind the SID.

On SQL Server 2005 these are sysdtspackages90 and sysdtspackagefolders90, and the page reads whichever exists.

Messages you may see

Nothing is stored in the msdb package store. Packages on this instance are either in the Integration Services catalog, on the file system, or nowhere. The SSIS pages under a catalog database cover the first.

Report Why you would go there
Maintenance Plans The type 6 packages, as plans.
Job Commands The steps that run these packages.
SSIS Catalog Inventory The other package store, under an Integration Services catalog database.
msdb Space and Retention What the package XML weighs.

Frequently asked questions

Is this the same as the SSIS pages? No. The SSIS group appears under a database holding an Integration Services catalog and reads SSISDB. This page reads msdb.dbo.sysssispackages, which is the older store and is on every instance.

A package is here and no job runs it. Either it is run from outside SQL Server (a scheduler, a person, another application), or it is a leftover. The page cannot tell those apart and says so rather than guessing.

We are migrating. What do I need to know? The password protected rows. Everything else can be exported and redeployed; a package with an encryption level that needs a password cannot be opened without it.

Why does my instance show four packages when I have no SSIS? Because maintenance plans are stored as packages. The Kind column separates them.