Getting Error when executing DTS from Stored Procedure

Question

Post reply

Getting Error when executing DTS from Stored Procedure

Hemant Kumar

Ten Centuries

Points: 1063
More actions
December 9, 2005 at 9:00 am

#67937

Hai .....,
          I have created a DTS package in the Server machine (Server=machine7 and my machine is machine3). When i execute the DTS directly by selecting it, it completes successfully. But when I try to execute the same DTS thru my stored procedure, I 'm getting the following error messageI Executed the procedure thru Query Analyzer window and got the following message)
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE)
   Error string: Error Code: 0
Error Source= msxml3.dll
Error Description: A connection with the server could not be established
Error on Line 10
   Error source: Microsoft Data Transformation Services (DTS) Package
   Help file: sqldts80.hlp
   Help context: 4500
Error Detail Records:
Error: -2147220482 (800403FE); Provider Error: 0 (0)
Error string: Error Code: 0
Error Source= msxml3.dll
Error Description: A connection with the server could not be established

Error on Line 10

   Error source: Microsoft Data Transformation Services (DTS) Package
   Help file: sqldts80.hlp
   Help context: 4500

Error: -2147467259 (80004005); Provider Error: 0 (0)
   Error string: A connection with the server could not be established

   Error source: msxml3.dll
   Help file:
   Help context: 0

DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun: Package execution complete.
NULL

Can anyone help me solve this problem please

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

What code are you using in the stored procedure?

------------------------------
The Users are always right - when I'm not wrong!

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 2

When you say you are selecting it, is it on your machine that you select it?

Keep in mind that if you run Enterprise Manager and execute a DTS package, it is executing FROM YOUR MACHINE, not the server. If you schedule it or use some stored procedure to start a job or shell out for DTSRUN, then it is running from that machine, in that security context.

Hemant Kumar Ten Centuries Points: 1063 More actions · Answer 3

Hi Steve,

No, the DTS is on the server machine (i.e., machine7). I have registered the server in my client (by right clicking SQL Server Group and doing New Sql Server Registration... from the context menu) and I 'm succeeding when executing it by selecting the package and then from the context menu, doing Execute Package

Hemant Kumar Ten Centuries Points: 1063 More actions · Answer 4

OK...

Now the package seems to execute but not getting the same result that I get by executing it directly .

The stored procedure that calls the DTS is as follows;

CREATE PROCEDURE ProcessAutomation_TriggerDTS

AS

DECLARE @err int

DECLARE @cmd varchar(1000)

SET @err = 0

SET @cmd = 'dtsrun /S machine7 /E /N DTS_MeterNotification /M thePwd'

EXECUTE @err = master..xp_cmdshell @cmd

SELECT @err = COALESCE(NULLIF(@err, 0), @@ERROR)

IF @err <> 0 RETURN @err

GO

The DTS executes an ActiveX script that invokes a method of a web service and the code is as follows;

'**********************************************************************

' Visual Basic ActiveX Script

'************************************************************************

Function Main()

Dim oXmlHttp

Set oXmlHttp = CreateObject ("MSXML2.ServerXMLHTTP.3.0")

oXmlHttp.open "POST", "http://machine3/WebInterface/WebServices/EventService.asmx/DispatchMeterReadingsInformation", False

oXmlHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"

oXmlHttp.send

Set oXmlHttp = Nothing

Main = DTSTaskExecResult_Success

End Function

The method DispatchMeterReadingsInformation writes to the event log. When I execute the DTS package directly, I see event in my log. But when I execute it with the help of the Stored Procedure from the Query Analyzer, I don't find any event registered to the log and the procedure's result window displays that the execution is successful.

Please help me out of this problem