If you’ve ever done a migration or consolidation on a SQL Server 2000 server that uses DTS Packages you probably ran into the task of needing to open each DTS Package and change the connections within that package to a new server (this is assuming the environment does NOT make use of config or ini files and dynamically assigning connection details). Depending on the level of utilization of DTS Package this can become a VERY tedious and lengthy task.
Below is a script that will go through each of your DTS packages on a server and “auto-magically” change each SQL Server connection found to a new server name.
DECLARE @object INT
DECLARE @pkgname NVARCHAR(255)
DECLARE @rc INT
DECLARE @Numof INT
DECLARE @i INT
DECLARE @src VARCHAR(255)
DECLARE @desc VARCHAR(255)
DECLARE @property VARCHAR(8000)
DECLARE @Connection_Value VARCHAR(8000)
DECLARE @Server_Value VARCHAR(8000)
DECLARE @Database_Value VARCHAR(8000)
DECLARE @Source_Server_Name VARCHAR(100)
DECLARE @Target_Server_Name VARCHAR(100)
SET @Source_Server_Name = ‘SOURCESERVER’
SET @Target_Server_Name = ‘TARGETSERVER’
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE name LIKE ‘#dts_package____%’)
DROP TABLE #dts_packages
CREATE TABLE #dts_packages
(name VARCHAR(1000),
id UNIQUEIDENTIFIER,
versionid UNIQUEIDENTIFIER,
description VARCHAR(1000) NULL,
createdate DATETIME,
owner VARCHAR(100),
size INT,
packagedata IMAGE NULL,
isowner VARCHAR(100),
packagetype INT
)
INSERT INTO #dts_packages EXEC msdb.dbo.sp_enum_dtspackages
WHILE (SELECT COUNT(*) FROM #dts_packages) > 0
BEGIN
SELECT TOP 1 @pkgname=name FROM #dts_packages ORDER BY name
DELETE FROM #dts_packages WHERE name = @pkgname
–Creating object
EXEC @rc = sp_OACreate ‘DTS.Package’, @object OUTPUT
EXEC @rc = sp_OAMethod @object, ‘LoadFromSQLServer’,
– SQL Server Authentication
– NULL,@Source_Server_Name,’login’,'password’,’0′,”,”,”,@pkgname
– Windows Authentication
NULL,@Source_Server_Name,”,”,’256′,”,”,”,@pkgname
EXEC @rc = sp_OAGetProperty @object, ‘Connections.Count’, @Numof OUT
SET @i = 0
WHILE @i < @Numof
BEGIN
SET @i = @i + 1
SET @property = ‘Connections(‘ + RTRIM(CAST(@i AS CHAR)) + ‘).Name’
EXEC @rc = sp_OAGetProperty @object, @property, @Connection_Value OUT
SET @property = ‘Connections(‘ + RTRIM(CAST(@i AS CHAR)) + ‘).DataSource’
EXEC @rc = sp_OAGetProperty @object, @property, @Server_Value OUT
SET @property = ‘Connections(‘ + RTRIM(CAST(@i AS CHAR)) + ‘).Catalog’
EXEC @rc = sp_OAGetProperty @object, @property, @Database_Value OUT
IF CHARINDEX(@Source_Server_Name,@Server_Value) > 0
BEGINPRINT
‘DTS Package: ’ + @pkgname
PRINT ‘Connection Name: ’ + @Connection_Value
PRINT ‘Server Name: ’ + @Server_Value
PRINT ‘Database Name: ’ + @Database_Value
PRINT ”
SET @property = ‘Connections(‘ + RTRIM(CAST(@i AS CHAR)) + ‘).DataSource’
SET @Server_Value = REPLACE(@Server_Value,@Source_Server_Name,@Target_Server_Name)
EXEC @rc = sp_OASetProperty @object, @property, @Server_Value
ENDEXEC
@rc = sp_OAMethod @object, ‘SaveToSQLServer’,
NULL,@Source_Server_Name, ”,”,’256′
END
END
END
END
BE AWARE ‘Ole Automation Procedures’ needs to be enabled for this script to work.
This script only looks at “Microsoft OLE DB Provider for SQL Server” connections but can be tweaked to look at other DTS Package items that would require the same type of adjustments. Things like “Execute Package task”, “Copy SQL Server Objects Task”, etc. but for the sake of keeping it simple we’re only going to look at “Microsoft OLE DB Provider for SQL Server”
By default the script will use Windows Authentication but if you require SQL Server Authentication then modify the section below in the script above:
-- SQL Server Authentication
-- NULL,@Source_Server_Name,'login','password','0','','','',@pkgname
-- Windows Authentication
NULL,@Source_Server_Name,'','','256','','','',@pkgname
