How to know all the packages that are on a particular SQL server instance.

Problem: How to know all the packages that are on a particular SQL server instance.

Solution: sysssispackages

In SSIS all the packages that are created on the particular instance are stored inside of msdb database.

It helps to know when the package is created and what type of designer was used to create the package and version of the sql used as well.

Example:

In this article, I am going to show we can use packages table to see what version and type of package it was.

SELECT NAME,

                createdate,

                CASE

                                WHEN PackageType = 0

                                                THEN '(default value)'

                                WHEN PackageType = 1

                                                THEN '( SQL Server Import and Export Wizard)'

                                WHEN PackageType = 3

                                                THEN '( SQL Server Replication)'

                                WHEN PackageType = 5

                                                THEN '( SSIS Designer)'

                                WHEN PackageType = 6

                                                THEN '(Maintenance Plan Designer or Wizard).'

                                END AS PackageType,

                packageformat

FROM dbo.sysssispackages

ORDER BY createdate DESC

Results:

	name	createdate	PackageType	packageformat
1	QueryActivityUpload	2017-03-10 09:21:34.450	( SSIS Designer)	1
2	QueryActivityCollect	2017-03-10 09:21:34.377	( SSIS Designer)	1
3	PerfCountersUpload	2017-03-10 09:21:34.353	( SSIS Designer)	1
4	PerfCountersCollect	2017-03-10 09:21:34.343	( SSIS Designer)	1
5	TSQLQueryUpload	2017-03-10 09:21:34.337	( SSIS Designer)	1
6	TSQLQueryCollect	2017-03-10 09:21:34.320	( SSIS Designer)	1
7	SqlTraceUpload	2017-03-10 09:21:34.297	( SSIS Designer)	1