August 28, 2006 at 4:12 am
I have to copy objects from one server to different servers. How can I achieve this through dts. In dts, all i know is to copy to each and every server. Is there any other way?
Thanks
R
August 28, 2006 at 10:02 am
Well, yes, you have to specify which servers you want to copy to. You can put a Copy SQL Server Objects task for each destination server in a single package then execute the package.
Greg
Greg
August 29, 2006 at 8:20 am
I put the destination server/database names in a table; then a stored procedure reads the table in a cursor and for each loop iteration uses the OLE stored procedures sp_OACreate, sp_OAMethod, etc., to call the package, populating package global variables with the Server and DB. I got this example from http://www.sqldts.com/, specifically Darren Green's article now in http://www.databasejournal.com/features/mssql/article.php/1459181.
There will be a need in the package to set up a dynamic properties task to populate the target tasks with server and database variable taken from the global variables populated by the stored proc.
I have attached a code snippet from my proc that may help:
------------------------------------------------------
-- Declare source servers cursor, open and first fetch
------------------------------------------------------
DECLARE SourceServers CURSOR FOR
SELECT ServerName, userID, Password, DBName, OverrideSQL
FROM TransmissionServers
WHERE TransmissionID = @TransmissionID
AND IOFlag = 'I'
OPEN SourceServers
FETCH NEXT FROM SourceServers INTO @SourceServer, @SourceServeruserID, @SourceServerPassword, @SourceDB, @OverrideSQL
-----------------------------------------------------------------------------------
-- Transmission Loop
-----------------------------------------------------------------------------------
WHILE @@FETCH_STATUS = 0
BEGIN
-------------------------------
-- Create/Load a Package Object
-------------------------------
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT
IF @hr <> 0
BEGIN
SELECT @Message = 'Create Package object failed. Problem with extended procedure sp_OACreate. Process terminated.'
Exec @rc = dbo.usp_LogMessage 'Stored Procedure', @Application, @TransmissionID, NULL, @Message
Exec dbo.usp_displayoaerrorinfo @oPKG, @hr
SELECT @abend = 1
BREAK
END
if @PackageServerUserID is null or @PackageServerUserID = ''
SET @Cmd = 'LoadFromSQLServer("' + @PackageServer + '", NULL, NULL, 256, , , , "' + @Package + '")'
else
SET @Cmd = 'LoadFromSQLServer("' + @PackageServer +'", "' + @PackageServerUserID + '", "' + @PackageServerPassword + '", 0, "' + @PackagePassword + '", , , "' + @Package + '")'
EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL
IF @hr <> 0
BEGIN
SELECT @Message = 'LoadFromSQLServer failed for package ' + @Package + '. Return code is ' + rtrim(cast(@hr as varchar(6))) + '. Process terminated. @Cmd=' + @Cmd
Exec @rc = dbo.usp_LogMessage 'Stored Procedure', @Application, @TransmissionID, NULL, @Message
Exec dbo.usp_displayoaerrorinfo @oPKG , @hr
SELECT @abend = 1
BREAK
END
SELECT @Message = 'Package ' + @Package + ' has been created and loaded from ' + @PackageServer
Exec @rc = dbo.usp_LogMessage 'Stored Procedure', @Application, @TransmissionID, NULL, @Message
------------------------------------
-- Populate package global variables
------------------------------------
SELECT @TransmissionResult = 1
EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("TransmissionID").Value', @TransmissionID
EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("Package").Value', @Package
EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("SourceServer").Value', @SourceServer
EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("TransmissionResult").Value', @TransmissionResult
SELECT @Message = 'Package ' + @Package + ' about to be executed for Source Server ' + @SourceServer
Exec @rc = dbo.usp_LogMessage 'Stored Procedure', @Application, @TransmissionID, NULL, @Message
-------------------------------------------------------------------
-- Execute package, retrieve global variable that shows pkg status
-------------------------------------------------------------------
EXEC @PackageRC = sp_OAMethod @oPKG, 'Execute'
EXEC @hr = sp_OAGetProperty @oPKG, 'GlobalVariables("TransmissionResult").Value', @TransmissionResult OUTPUT
---------------------------------------------------------------------------
-- If @TransmissionResult is still 1, there was an error in the package
---------------------------------------------------------------------------
IF @TransmissionResult = 0
BEGIN
SELECT @Message = 'Package ' + @Package + ' executed OK for ' + @SourceServer
Exec @rc = dbo.usp_LogMessage 'Stored Procedure', @Application, @TransmissionID, NULL, @Message
INSERT #SaveServer VALUES(@SourceServer,
NULL,
NULL,
NULL,
NULL,
'OK')
END
ELSE
BEGIN --Log failure, fail the problem transmission and continue
SELECT @Message = '*** ' + @Package + ' failed for ' + @SourceServer + ', RC=' + cast(@PackageRC as varchar(8)) + '. Ignored, process continued. ***'
Exec @rc = dbo.usp_LogMessage 'Stored Procedure', @Application, @TransmissionID, NULL, @Message
INSERT #SaveServer VALUES(@SourceServer,
NULL,
NULL,
NULL,
NULL,
@Message)
END
-------------------
-- Clean up package
-------------------
EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'
IF @hr <> 0
BEGIN
SELECT @Message = 'UnInitialize failed; sp_OAMethod problem.'
Exec @rc = dbo.usp_LogMessage 'Stored Procedure', @Application, @TransmissionID, NULL, @Message
Exec dbo.usp_displayoaerrorinfo @oPKG , @hr
RETURN -9
END
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
SELECT @Message = 'Package destroy failed; sp_OADestroy problem.'
Exec @rc = dbo.usp_LogMessage 'Stored Procedure', @Application, @TransmissionID, NULL, @Message
Exec dbo.usp_displayoaerrorinfo @oPKG , @hr
SELECT @abend = 1
BREAK
END
--------------------------
-- Get Next Source Server
--------------------------
FETCH NEXT FROM SourceServers INTO @SourceServer, @SourceServeruserID, @SourceServerPassword, @SourceDB, @OverrideSQL
END
-----------------------------------------------------------------------------------
-- Finalization
-----------------------------------------------------------------------------------
close SourceServers
deallocate SourceServers
August 31, 2006 at 1:03 am
Thanks Carol, That will save my major work time.
R
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply