Moving DTS Packages from one server to another
-
Hi
We are currently exproting DTS packages from one server to another. The packages themselves have been exported and now we need to change the connection details inside each of the packages. Unfortuantely when they were set up they were not set up to look at configuration files.
Both servers are running SQL Server 2000. The old serve is SP3 the new is SP4.
We have basically taken some code form the Web, that iterates trhough the packages, examines the connections for a matching string, updates the connetion to the new one, and then SHOULD write the package back.
Thats where we have the problem. We have tried writing back to both SQL server and SaveToStorageFile and we are always getting the following error.
0x8007010B Microsoft Data Transformation Services (DTS) Package The directory name is invalid.
The lines we beleive is causing the problem are
-- Save DTS package to Server2
EXEC @rc = sp_OAMethod @object, 'SaveToSQLServer', NULL, 'SQL101\BO', '','','256','','','',''
OR
Set @FileLocation = 'E:\BOWorkingFolder\' + @pkgname + 'pkg'
EXEC @rc = sp_OAMethod @object, 'SaveToStorageFile',
NULL, @FileLocation
We certainly have no expertise in this area and have tried to find resolution on the web, but no joy, so any help greatfully received.
Thanks
-
You saved the DTS packages in different server, but if the server and the database was set up internally in each DTS package, then you just have to go to each DTS package to change the server name. May be it is good time to change it to look at the configuration file.
It does not matter you change the file location, it had nothing to do with the server inside the DTS Packages.
-
Thanks
I have taken the liberty of posting all the code that is executing. Our understanding is that we should be able to change the connection details ( referencing different server/database ) as neccesary. In fact the actaul database is the same name, just a different server. The debug suggests that everything is ok. The only problem is that we can not save the package back to the server.
Thanks very much
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE dbo.mjw_convert AS
---------------------
-- Begin Section A --
---------------------
PRINT 'BEGIN SECTION A'
set nocount on
--Declaring variable
Declare @object int
Declare @pkgname nvarchar(255)
Declare @rc int
Declare @src varchar(255)
Declare @desc varchar(255)
Declare @Numof int
Declare @NumofItems int
Declare @i int
Declare @j-2 int
Declare @property varchar(8000)
Declare @property_value varchar(8000)
Declare @property_name varchar(8000)
Declare @FileLocation varchar(8000)
-- Get list of Packages from KB article 241249
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),
createdate datetime,
owner varchar(100),
size int,
packagedata image,
isowner varchar(100),
packagetype int
)
insert into #dts_packages exec msdb..sp_enum_dtspackages
PRINT 'END SECTION A'
-------------------
-- End Section A --
-------------------
PRINT 'BEGIN SECTION B'
---------------------
-- Begin Section B --
---------------------
while (select count(*) from #dts_packages where name = '1 Test') > 0
begin
select top 1 @pkgname=name from #dts_packages order by name
delete from #dts_packages where name = @pkgname
Print 'Starting the migration of package ' + rtrim(@pkgname)
--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,'SQL101\BO','sa','dummySApassword','0','','','',@pkgname
-- Windows Authentication
-- NULL,'SQL101\BO','','','256','','','',@pkgname
IF @rc <> 0 goto PrintError
print 'Package loaded successfully'
PRINT 'END SECTION B'
-------------------
-- End Section B --
-------------------
PRINT 'BEGIN SECTION C'
---------------------
-- Begin Section C --
---------------------
-- Get Number of Connections
EXEC @rc = sp_OAGetProperty @object, 'Connections.Count', @Numof OUT
IF @rc <> 0 goto PrintError
PRINT 'Number of connections: ' + CAST(@Numof AS VARCHAR(255))
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, @property_value OUT
IF @rc <> 0 goto PrintError
-- Change SERVER1 to SERVER2 in Connection Name
PRINT 'Change Connections(' + rtrim(cast(@i as char)) + ').Name'
PRINT 'Name Property Value: ' + @property_value
if charindex('(local)',@property_value) > 0
begin
Print 'Change Connection.Name for ' + @property
set @property_value = replace(@property_value,'(local)','SQL101\BO')
EXEC @rc = sp_OASetProperty @object, @property, @property_value
IF @rc <> 0 goto PrintError
Print 'Changed Value To : ' + @property_value
end
if charindex('E:\AndyMWBTest',@property_value) > 0
begin
Print 'Change Connection.Name for ' + @property
set @property_value = replace(@property_value,'E:\AndyMWBTest','E:\BOWorkingFolder')
EXEC @rc = sp_OASetProperty @object, @property, @property_value
IF @rc <> 0 goto PrintError
Print 'Changed Value To : ' + @property_value
end
-- Get DataSource of Connections
PRINT 'Get DataSource of Connections'
set @property = 'Connections(' + rtrim(cast(@i as char)) +
').DataSource'
EXEC @rc = sp_OAGetProperty @object, @property, @property_value OUT
IF @rc <> 0 goto PrintError
-- Change SERVER1 to SERVER2 in Connection Value
PRINT 'Datasource Property Value: ' + @property_value
if charindex('(local)',@property_value) > 0
begin
Print 'Changed Connection.DataSource for ' + @property
set @property_value = replace(@property_value,'(local)','SQL101\BO')
EXEC @rc = sp_OASetProperty @object, @property, @property_value
IF @rc <> 0 goto PrintError
Print 'Changed Value To : ' + @property_value
end
if charindex('E:\AndyMWBTest',@property_value) > 0
begin
Print 'Changed Connection.DataSource for ' + @property
set @property_value = replace(@property_value,'E:\AndyMWBTest','E:\BOWorkingFolder')
EXEC @rc = sp_OASetProperty @object, @property, @property_value
IF @rc <> 0 goto PrintError
Print 'Changed Value To : ' + @property_value
end
end
PRINT 'END SECTION C'
-------------------
-- End Section C --
-------------------
PRINT 'BEGIN SECTION D'
---------------------
-- Begin Section D --
---------------------
-- Get Number of Task
EXEC @rc = sp_OAGetProperty @object, 'Tasks.Count', @Numof OUT
IF @rc <> 0 goto PrintError
set @i = 0
PRINT 'Tasks.Count: ' + rtrim(cast(@Numof as char))
-- Process Through each Task
While @i < @Numof
begin
set @i = @i + 1
-- Get Number of Properties
set @property = 'Tasks(' + rtrim(cast(@i as char)) +
').Properties.Count'
EXEC @rc = sp_OAGetProperty @object, @property, @NumofItems OUT
IF @rc <> 0 goto PrintError
PRINT 'TaskItem.Count: ' + rtrim(cast(@NumofItems as char))
-- Process through all properties
set @j-2 = 0
while @j-2 < @NumofItems
begin
-- Get Name of Property
set @property = 'Tasks(' + rtrim(cast(@i as char)) +
').Properties(' +
rtrim(cast(@j as char)) + ').Name'
EXEC @rc = sp_OAGetProperty @object, @property, @Property_name OUT
IF @rc <> 0 goto PrintError
-- PRINT 'Task Property Name: ' + @property_name
-- Get Value of Property
set @property = 'Tasks(' + rtrim(cast(@i as char)) +
').Properties(' +
rtrim(cast(@j as char)) + ').Value'
EXEC @rc = sp_OAGetProperty @object, @property, @Property_value OUT
IF @rc <> 0 goto PrintError
Print 'Tasks(' + rtrim(cast(@i as char)) +
').Properties(' +
rtrim(cast(@j as char)) + ').Value :' + @property_value
-- Change SERVER1 to SERVER2 in Value of Each Property
if charindex('(local)',@property_value) > 0
begin
Print 'Changed Task.Properties for ' + replace(@property,'Value',@property_name)
set @property_value = replace(@property_value,'(local)','SQL101\BO')
EXEC @rc = sp_OASetProperty @object, @property, @property_value
IF @rc <> 0 goto PrintError
Print 'Changed Value To : ' + @property_value
end
if charindex('E:\AndyMWBTest',@property_value) > 0
begin
Print 'Changed Task.Properties for ' + replace(@property,'Value',@property_name)
set @property_value = replace(@property_value,'E:\AndyMWBTest','E:\BOWorkingFolder')
EXEC @rc = sp_OASetProperty @object, @property, @property_value
IF @rc <> 0 goto PrintError
Print 'Changed Value To : ' + @property_value
end
end
end
PRINT 'END SECTION D'
-------------------
-- End Section D --
-------------------
PRINT 'BEGIN SECTION E'
---------------------
-- Begin Section E --
---------------------
-- Get Number of Steps
EXEC @rc = sp_OAGetProperty @object, 'Steps.Count', @Numof OUT
IF @rc <> 0 goto PrintError
PRINT 'Steps.Count: ' + rtrim(cast(@Numof as char))
set @i = 0
-- Process Through each Step
While @i < @Numof
begin
set @i = @i + 1
-- Get Number of Properties
set @property = 'Steps(' + rtrim(cast(@i as char)) +
').Properties.Count'
EXEC @rc = sp_OAGetProperty @object, @property, @NumofItems OUT
IF @rc <> 0 goto PrintError
-- PRINT 'StepItem.Count: ' + rtrim(cast(@NumofItems as char))
-- Process through all properties
set @j-2 = 0
while @j-2 < @NumofItems
begin
-- Get Value of Each Property
set @property = 'Steps(' + rtrim(cast(@i as char)) +
').Properties(' +
rtrim(cast(@j as char)) + ').Value'
EXEC @rc = sp_OAGetProperty @object, @property, @Property_value OUT
IF @rc <> 0 goto PrintError
PRINT 'Steps(' + rtrim(cast(@i as char)) +
').Properties(' +
rtrim(cast(@j as char)) + ').Value :' + @property_value
-- Change SQLPROD1 to DOHDBOLYPR01 in Value of Each Property
if charindex('(local)',@property_value) > 0
begin
-- Print 'Changed Steps.Properties for ' + replace(@property,'Value',@property_name)
set @property_value = replace(@property_value,'(local)','SQL101\BO')
EXEC @rc = sp_OASetProperty @object, @property, @property_value
IF @rc <> 0 goto PrintError
Print 'Changed Value To : ' + @property_value
end
if charindex('E:\AndyMWBTest',@property_value) > 0
begin
-- Print 'Changed Steps.Properties for ' + replace(@property,'Value',@property_name)
set @property_value = replace(@property_value,'E:\AndyMWBTest','E:\BOWorkingFolder')
EXEC @rc = sp_OASetProperty @object, @property, @property_value
IF @rc <> 0 goto PrintError
Print 'Changed Value To : ' + @property_value
end
end
end
PRINT 'END SECTION E'
-------------------
-- End Section E --
-------------------
PRINT 'BEGIN SECTION F'
---------------------
-- Begin Section F --
---------------------
-- Remove package from Server2
-- print 'Removing packing from Server mikey'
-- EXEC @rc = sp_OAMethod @object, 'RemoveFromSQLServer',
-- NULL,'SQL101\BO','','','256','','',@pkgname
-- print 'RTCD: ' + rtrim(cast(@rc as char))
-- IF @rc <> 0 and @rc <> -2147217900 -- the -2147217900 is the return code if package does not exist
-- goto PrintError
-- print 'Package Removed ....... '
-- Save DTS package to Server2
-- EXEC @rc = sp_OAMethod @object, 'SaveToSQLServer',
-- NULL, 'SQL101\BO', '','','256','','','',''
Set @FileLocation = 'E:\BOWorkingFolder\' + @pkgname + 'pkg'
EXEC @rc = sp_OAMethod @object, 'SaveToStorageFile',
NULL, @FileLocation
IF @rc <> 0 goto PrintError
Print 'Package Saved Successfully'
PRINT 'END SECTION F'
-------------------
-- End Section F --
-------------------
end
return
-- Process Errors
PrintError:
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT rc=convert(varbinary(4),@rc), Source = @src, Description = @desc
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply