April 24, 2003 at 11:17 am
I have a DTS package which runs great from the enterprise manager, but I need the DTS package to run in a loop, continuously.
I've tried to simply create an "on completion" workflow to connect the script back to the beginning, but when I do that, the package starts in the wrong place.
So I decided to try creating a simple T-SQL script to run the package in a loop. Here is the script:
CREATE PROCEDURE dbo.spAutoMatchLoop AS
Declare @hr int, @oPKG int, @GVOutput int
Print 'beginning'
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
Print 'Successfully created package'
EXEC @hr = sp_OAMethod @oPKG,
'LoadFromSQLServer("P2PS04", "", "", 256, , , , "Automatch")',
NULL
IF @hr <> 0
BEGIN
PRINT '*** Load Package failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
print 'successfully loaded package'
while 1=1
begin
EXEC @hr = sp_OAGetProperty @oPKG, 'GlobalVariables("Tranch").Value', @GVOutput OUT
IF @hr <> 0
BEGIN
PRINT '*** GlobalVariable Read Failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
PRINT 'Starting tranch ' + rtrim(ltrim(cast(@GVOutput as varchar))) + ' at ' + rtrim(ltrim(cast (getdate() as varchar)))
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN
END
print 'successfully executed tracnh ' + rtrim(ltrim(cast(@GVOutput as varchar))) + ' at ' + rtrim(ltrim(cast (getdate() as varchar)))
EXEC @hr = sp_OAMethod @oPKG, 'SaveToSQLServer("P2PS04",,,256,,,,)'
IF @hr <> 0
BEGIN
PRINT '*** Save Package failed'
Print 'hr = ' + cast (@hr as varchar)
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN
END
print 'sucessfully saved package'
end
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
GO
When I run this loop, it generates the following error on each iteration:
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Error Code: 0
Error Source= Microsoft OLE DB Provider for SQL Server
Error Description: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Error on Line 8
(Microsoft OLE DB Provider for SQL Server (80040e4d): Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.)
Step Error code: 800403FE
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:4500
I run it from an account with all the necessary permissions, but I get the login error. Is there any way to force SQL Server to recognize the login that I am using? If not, is there another way to execute a DTS package in a continuous loop?
Thanks very much.
-Richard
April 24, 2003 at 11:48 am
Is there a reason you can't schedule it to run every minute?
You could do an xp_cmdsheel and "dtsrun" it, but I wouldn't recommend it. If you schedule it, then you get the advantage of being able to easily pause it.
Steve Jones
April 24, 2003 at 12:15 pm
The problem with scheduling it is that it has a very wide range of run times - anything for 2 to 5 days - and if it is running in two different threads, then each process will step on the toes of the other so to speak.
April 25, 2003 at 4:07 am
You might try schduling as a Job that runs continuously.
Create basic Job by scheduling the package in EM. Open Agent and go into the Job. In the jobs Steps open the one for the package. On the Advanced tab set the job on completion and if you want on fail to go back to Step 1. Thsi will cause it to run continuosly and is how some replication jobs are setup.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply