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.
Filed under: DTS, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, DTS, language sql, microsoft sql server, sql statements, T-SQL