Executing DTS Via Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Executing DTS Via Stored Procedure

Author

Topic

Dim it ri
Starting Member

1 Post

Posted - 2005-06-21 : 06:48:41

Hi all,

I have some trouble with executing a dts package via a stored Procedure.

It works fine when i execute it via a stored procedure on server A updating server A.
It workes fine on serve A when i execute it manualy on server A.
It workes Fine when I execute it via a stored procedure on server A updating server B.
It workes fine When i manualy execute it on der ver B.

however it doen not work when i execute it via a stored procedure on serve B updating server B

Description of the problem:
The package starts executing, the global variables are filled in,
A table is created with info from a global variable, a connection is set to an access db with info from a global variable.
When the datapump starts it al goes wrong. yet all connections are set correct, all datatransformations are correct... enless i missed something

Please could someone help me? I have been stuck on this for days.

I don't believe the problem is in the stored procedure, but here it is anyway:

CREATE PROCEDURE SynchronizeDistritax AS

DECLARE @proid INT, @proDB NVARCHAR(255)
DECLARE @rc INT, @pkg INT

EXEC @rc = sp_OACreate 'DTS.Package', @pkg OUTPUT
IF @rc <> 0
BEGIN
PRINT '*** instance failed'
END

EXEC @rc = sp_OAMethod @pkg, 'LoadFromSQLServer', NULL, @ServerName = @@SERVERNAME,
@Flags = 256,
@PackageName = "DS_UpdateDistriTax"

DECLARE C1 CURSOR FOR SELECT ProDB + '\distritax.mdb' AS proDB, ProID FROM DistriService2004.dbo.tblProducts WHERE (ProDB <> N'')
OPEN C1

FETCH NEXT FROM C1
INTO @proDB, @proid
WHILE @@FETCH_STATUS = 0
BEGIN

EXEC @rc = sp_OASetProperty @pkg, 'GlobalVariables ("DistriTaxDB").Value', @proDB
IF @rc <> 0
BEGIN
PRINT '*** set DistriTaxDB failed'
END

EXEC @rc = sp_OASetProperty @pkg, 'GlobalVariables ("ProdID").Value', @proid
IF @rc <> 0
BEGIN
PRINT '*** set ProdID failed'
END

EXEC @rc = sp_OAMethod @PKG, 'Execute'
IF @rc <> 0
BEGIN
PRINT '*** Execute failed'
END

FETCH NEXT FROM C1
INTO @proDB, @proid
END

CLOSE C1
DEALLOCATE C1

EXEC @rc = sp_OAMethod @PKG, 'UnInitialize'
IF @rc <> 0
BEGIN
PRINT '*** UnInitialize failed'
END

EXEC @rc = sp_OADestroy @PKG
if @rc <> 0
BEGIN
PRINT '*** Destruction failed'
END
GO

thx
Dimitri

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources