Where does SQL Server store the DTS packages?

Question

I have created a few DTS packages and saved them on the server. where I can edit these DTS packages on the server?

score 2 · Accepted Answer · answered Jan 14 '09 at 16:01

2

you should connect to integration services... and then in the MSDB folder or in the FileSystem folder, it depends how did you save them

answered Jan 14 '09 at 16:01

Ironicnet

519
3
14

i used the import/export wizard and saved them on the server in the last step. i've tried saving them to the filesystem, but was not able to edit them that way either. – Sheehan Alam Jan 14 '09 at 16:06
using the Business Intelligence Studio I was able to save them in the MSDB folder. I don't think there is a way to save them using the Import/Export Wizard in SQL Management Studio. – Sheehan Alam Jan 17 '09 at 20:24
From the SQL Management Studio, you can connect to a SSIS server. When connected to a Integration Services server, you can run, import or delete packages. If in the import/export you save the package to the MSDB, you will see it. I think that the FileSystem option is bugged or not working maybe. – Ironicnet Jan 20 '09 at 12:15

score 1 · Answer 2 · edited Aug 06 '11 at 02:15

As you probably know, DTS was deprecated and was replaced by SSIS in SQL Server 2005. It IS possible to edit and run DTS packages in SQL Server 2005 however, by installing the “Feature Pack for Microsoft SQL Server 2005”. Particular components that you should look at are “Microsoft SQL Server 2000 DTS Designer Components” for editing and “Microsoft SQL Server 2005 Backward Compatibility Components” for the runtime.

This allows you to upgrade from SQL Server 2000 to SQL Server 2005 and your old DTS packages will still run. This leaves you time to upgrade them to SSIS.

score 0 · Answer 3 · answered May 03 '18 at 00:04

When you use the Import/Export wizard you are given an option, at the end of the wizard, to save the SSIS package into the database.

The wizard saves the package in the msdb database in the sysssispackages table. This option does not save the package to the SSIS Catalog database (SSISDB).

score 0 · Answer 4 · edited May 31 '18 at 17:29

The raw payload of a DTS package should give results from this:

SELECT * FROM msdb.dbo.sysdtspackages WHERE name = 'MyPackageName'

Or this:

exec msdb..sp_get_dtspackage @name = N'PackageName', @id = 'PACKAGE_GUID', @versionid = 'VERSION_GUID'
--you can get PACKAGE_GUID from going to Package/Properties from DTS Designer
--you can get VERSION_GUID from Package/Properties from DTS Designer

The payload is in the packagedata field. However it is in binary format so there is not much manipulation that can be done with the field.

This technically answers the question from the perspective of the physical location.

Where does SQL Server store the DTS packages?

4 Answers4