For those of you who have moved completely to SQL 2012 & 2014, Lucky you and don’t judge! For the rest of us who are still dealing with DTS packages I’m going to do a couple of posts on the subject. To start with here’s a script for collecting all of the connection information on all of the DTS packages on an instance. This is particularly useful if you are planning on moving an instance to another location and need a list of packages that you will need to modify.

A couple of notes on the script

• This particular script will only work on DTS packages that are stored in MSDB. If you have them stored on the file share at the very least you will have to modify how you pull the list of packages. Beyond that I’m honestly not sure if the SMO will work. If someone wants to work it out please let me know how it goes.
• It also will not read encrypted packages. An entry is added to the #DTS_List table showing that the package is encrypted.
• If there are embedded DTS packages they aren’t included either.
• The script uses the OLE Automation functions which require sysadmin access. They also require that “Ole Automation Procedures” be turned on on the instance.

  sp_configure 'show advanced options', 1;
  GO
  RECONFIGURE;
  GO
  sp_configure 'Ole Automation Procedures', 1;
  GO
  RECONFIGURE;
  GO

• There is a section with the comment “Load Package from Source Server”. Under this section there are two connection options to load the DTS package. Using a SQL Server login or trusted connection (using the current Windows login). You can un-comment which ever method you would rather use. If you use the SQL Server login version then you will need to fill in the correct username/password of course.
• You can easily restrict the script to a single DTS package or group of them by adjusting the query that loads #tmp_DTS_Packages.
• The data is stored in the temp table #DTS_List. I leave that table in place at the end in case you need to run queries against it. It can of course also be dumped into a more permanent table.

--Declaring variables
DECLARE @object int
DECLARE @rc  int
DECLARE @src varchar(255)
DECLARE @desc varchar(255)
DECLARE @Numof int
DECLARE @NumofItems int
DECLARE @i int
DECLARE @j int
DECLARE @property varchar(8000)
DECLARE @DTSName varchar(255)
DECLARE @ConnectionName varchar(255)
DECLARE @DataSource varchar(255)
DECLARE @IntegratedSecurity varchar(255)
DECLARE @Catalog varchar(255)
DECLARE @ApplicationName varchar(255)
DECLARE @ProviderId varchar(255)
DECLARE @UserId varchar(255)
DECLARE @ServerName varchar(255)
SET @ServerName = @@ServerName

IF OBJECT_ID('tempdb..#DTS_List') IS NOT NULL
	DROP TABLE #DTS_List

CREATE TABLE #DTS_List (
	[ServerName] varchar(255),
	[DTSName] varchar(255),
	[ConnectionName] varchar(255),
	[DataSource] varchar(255),
	[IntegratedSecurity] varchar(255),
	[Catalog] varchar(255),
	[ApplicationName] varchar(255),
	[ProviderId] varchar(255),
	[UserId] varchar(255)
	)

IF OBJECT_ID('tempdb..#tmp_DTS_Packages') IS NOT NULL
	DROP TABLE #tmp_DTS_Packages 

CREATE TABLE #tmp_DTS_Packages 
	(name varchar(1000),
	id uniqueidentifier,
	versionid uniqueidentifier,
	description varchar(1000),
	createdate datetime,
	owner varchar(100),
	size int,
	packagedata image, 
	isowner varchar(100),
	packagetype int
	)

INSERT INTO #tmp_DTS_Packages (Name) 
	SELECT DISTINCT Name FROM msdb.dbo.sysdtspackages

-- Begin scan through packages
WHILE (SELECT COUNT(*) FROM #tmp_DTS_Packages) > 0
BEGIN
	SELECT TOP 1 @DTSName=name FROM #tmp_DTS_Packages ORDER BY name
	DELETE FROM #tmp_DTS_Packages WHERE name = @DTSName

	PRINT 'Starting the data collection on package ' + RTRIM(@DTSName)
	--Creating object
	EXEC @rc = sp_OACreate 'DTS.Package', @object OUTPUT
	IF @rc <> 0 GOTO PrintError

	-- Load Package from Source Server
	EXEC @rc = sp_OAMethod @object, 'LoadFromSQLServer',
	-- SQL Server Authentication
				NULL,@ServerName,'sysadminid','<strongpassword>','0','','','',@DTSName
	-- Windows Authentication
	--           NULL,@ServerName,'','','256','','','',@DTSName 
	IF @rc <> 0 
	BEGIN
		EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
		IF @desc LIKE '%encrypted%'
			BEGIN
				PRINT 'Package ' + @DTSName + ' is encrypted'
				INSERT INTO #DTS_List (ServerName, DTSName, ConnectionName)
					VALUES (@ServerName, @DTSName, 'Encrypted')
			END
		ELSE
			IF @desc LIKE '%Cannot find specified package in the storage location specified.%'
			BEGIN
				PRINT 'Package ' + @DTSName + ' can not be found.'
				INSERT INTO #DTS_List (ServerName, DTSName, ConnectionName)
					VALUES (@ServerName, @DTSName, 'Can not be found.')

--				RETURN
			END
			ELSE
				IF @desc LIKE '%Invalid class string%'
				BEGIN
					PRINT 'Package ' + @DTSName + ' Invalid class string.'
					INSERT INTO #DTS_List (ServerName, DTSName, ConnectionName)
						VALUES (@ServerName, @DTSName, 'Invalid class string.')
				END
				ELSE
					GOTO PrintError
	END

	IF @rc=0
	BEGIN
		print 'Package loaded successfully'

	 -- Get Number of Connections
	  EXEC @rc = sp_OAGetProperty @object, 'Connections.Count', @Numof OUT
	  IF @rc <> 0 GOTO PrintError

	  SET @i = 0
		-- Process Through each Connection
	  WHILE @i < @Numof
	   BEGIN
		 SET @i = @i + 1

		-- Get Name of Connections
		SET @property = 'Connections(' + rtrim(cast(@i AS char)) + ').Name'
		EXEC @rc = sp_OAGetProperty @object, @property, @ConnectionName OUT
		IF @rc <> 0 GOTO PrintError
		PRINT '  Got Name'

		-- Get DataSource of Connections
		SET @property = 'Connections(' + rtrim(cast(@i AS char)) +
					   ').DataSource'
		EXEC @rc = sp_OAGetProperty @object, @property, @DataSource OUT
		IF @rc <> 0 goto PrintError
		PRINT '  Got DataSource'

		-- Get IntegratedSecurity of Connections
		SET @property = 'Connections(' + rtrim(cast(@i AS char)) +
					   ').UseTrustedConnection'
		EXEC @rc = sp_OAGetProperty @object, @property, @IntegratedSecurity OUT
		IF @rc <> 0 goto PrintError
		PRINT '  Got IntegratedSecurity'

		-- Get @Catalog of Connections
		SET @property = 'Connections(' + rtrim(cast(@i AS char)) +
					   ').Catalog'
		EXEC @rc = sp_OAGetProperty @object, @property, @Catalog OUT
		IF @rc <> 0 GOTO PrintError
		PRINT '  Got Catalog'

		-- Get @ProviderId of Connections
		SET @property = 'Connections(' + rtrim(cast(@i AS char)) +
					   ').ProviderId'
		EXEC @rc = sp_OAGetProperty @object, @property, @ProviderId OUT
		IF @rc <> 0 GOTO PrintError
		PRINT '  Got ProviderId'

		-- Get @UserId of Connections
		SET @property = 'Connections(' + rtrim(cast(@i AS char)) +
					   ').UserId'
		EXEC @rc = sp_OAGetProperty @object, @property, @UserId OUT
		IF @rc <> 0 GOTO PrintError
		PRINT '  Got UserId'

		INSERT INTO #DTS_List 
		(
			ServerName,
			DTSName,
			ConnectionName,
			DataSource,
			IntegratedSecurity,
			Catalog,
			ApplicationName,
			ProviderId,
			UserId
		) VALUES
		(
			@ServerName,
			@DTSName,
			@ConnectionName,
			@DataSource,
			@IntegratedSecurity,
			@Catalog,
			@ApplicationName,
			@ProviderId,
			@UserId
		)		
		
	  END
	END
  EXEC sp_OADestroy @object
END

SELECT * FROM #DTS_List

RETURN

-- Process Errors
PrintError:
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT rc=convert(varbinary(4),@rc), Source = @src, Description = @desc

And if you are dealing with DTS packages then you might find this useful also.

Finding active DTS packages