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

Filed under: DTS, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, DTS, language sql, microsoft sql server, sql statements, T-SQL