CODEWITHSUNDEEP
sql-server-2005dtsssis
Sheehan Alam
Sheehan Alam

Reputation: 60919

Where does SQL Server store the DTS packages?

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

Upvotes: 3

Views: 27132

Answers (4)

Tony Trus
Tony Trus

Reputation: 1

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.

Upvotes: 0

Max Favilli
Max Favilli

Reputation: 6449

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).

Upvotes: 1

Andy Jones
Andy Jones

Reputation: 1482

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.

Upvotes: 1

Ironicnet
Ironicnet

Reputation: 567

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

Upvotes: 2

Related Questions