Re: Executing DTS from Code

Ok so who will be executing the VB.Net code on the server?

Get that account and log in to the server as that account. Now go to QA and execute the proc.

Can you?

Allan

"WhiskyRomeo" <WhiskyRomeo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:E8426EBB-4F4B-4FC2-AFE6-EAFF074C52C1@xxxxxxxxxxxxx:

I have VB.Net code that executes a store procedure that executes a DTS
package.

Everything works fine on my development workstation which has SQL Server
installed locally.  I moved the DTS package to the production server and
installed the application on the production server.  I can execute the
DTS
package directly (in Enterpise Manager) on the production server
sucessfully;
and I can execute sucessfully the stored procedure (in Query Analyzer)
that
runs the DTS package.  When moving the application code I only have to
account for a different Server Name, otherwise everything else is
identical.

However, when executing the Package from code ( via calling the store
procedure)  the DTS package does not execute.  I have verified the store
procedure executes with the correct parameters.  If the DTS package
executed,
it would have written to the log file for that DTS (whether sucessfull
or
not).

I am logged in as Administrator and the package owner is Administrator.

I suspect this is a persmission issue; but I am cluesless at this point.
Any help is appreciated.

WR

Here is the stored procedure( which works just fine):(I have verified
that
that the parameters values passed to it are correct)

CREATE PROCEDURE spExecuteDTS_CompImport

@LabOrderNbr varchar(15),
@Server varchar(255),
@PkgName varchar(255), -- Package Name (Defaults to most recent version)
@ConnSubject varchar(25) = '',
@ConnAssociate varchar(25) = '',
@ConnLink varchar(25) = '',
@PathSubject varchar(255) = '',   -- Path to Source File
@PathAssociate varchar(255) = '',
@PathLink varchar(255) = '',
@ServerPWD varchar(255) = Null, -- Server Password if using SQL Security
to
load Package (UID is SUSER_NAME())
@Security bit = 1, -- 0 = SQL Server Security, 1 = Integrated Security
@PkgPWD varchar(255) = '' -- Package Password

AS
SET NOCOUNT ON

DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000), @Connection
varchar(255)

Select @ret = 0

Delete tblImportPerson where LabOrderNbr = @LabOrderNbr --this will
cascade
to tblImportAddress
Delete tblImportLink where LabOrderNbr = @LabOrderNbr
print 'after delete tblImportLink'

-- Create a Pkg Object
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT
print 'created package'

SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "", "", 256, "' +
@PkgPWD +
'", , , "' + @PkgName + '")'
Print 'Loaded from server'
EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL
Print 'Executed sp_OAMethod'
-- Set Connections property
Select @Connection = 'Connections.Item(' + @ConnSubject + ').DataSource'
print 'Connection' + @Connection
EXEC @hr = sp_OASetProperty @oPKG,  @Connection, @PathSubject
If @hr <> 0
BEGIN
PRINT '*** OASetProperty for Connection Subject Failed***'
EXEC sp_OAGetErrorInfo @oPkg
RETURN
END

Select @Connection = 'Connections.Item(' + @ConnAssociate +
').DataSource'
EXEC @hr = sp_OASetProperty @oPKG,  @Connection, @PathAssociate
If @hr <> 0
BEGIN
PRINT '*** OASetProperty for Connection Associate Failed***'
EXEC sp_OAGetErrorInfo @oPkg
RETURN
END

Select @Connection = 'Connections.Item(' + @ConnLink + ').DataSource'
EXEC @hr = sp_OASetProperty @oPKG,  @Connection, @PathLink
If @hr <> 0
BEGIN
PRINT '*** OASetProperty for Connection Link Failed***'
EXEC sp_OAGetErrorInfo @oPkg
RETURN
END

-- Execute Pkg
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
print 'Executed Package'
-- Unitialize the Pkg
EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'

-- Clean Up
EXEC @hr = sp_OADestroy @oPKG

UPDATE tblImportPerson SET AssociatedSubject_ID  = IL.SubjectImport_ID
FROM tblImportPerson IP INNER JOIN tblImportLink IL ON IP.Import_ID =
IL.AssociateImport_ID
WHERE IP.LabOrderNbr = @LabOrderNbr



RETURN @ret
GO


.

Relevant Pages

  • Cross post recommended on this seemingly sinple problem
    ... >I have an ActiveX Script task in a DTS package which executes fine ... Why does the scheduled job not execute ... > schedule and the account is a domain account with windows ... even with both the package and the job owned by the same account (I ...
    (microsoft.public.sqlserver.dts)
  • Re: Missing rows from Transform - Intermittent Issue??
    ... server memory while running a DTS package. ... Bouncing SQL Server did not help. ...
    (microsoft.public.sqlserver.dts)
  • Re: Execute DTS Package from C#
    ... If you want the package to execute actually ON the remote server then look at firing off a job that fores the package ...
    (microsoft.public.sqlserver.dts)
  • Re: setup an offline connection
    ... If you are calling the package in VB then you can simply use the object ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... > the laptop to a client, I would like to modify the DTS package to ... > delete the connection to my computer and put the connection to their ...
    (microsoft.public.sqlserver.dts)
  • Re: Yet Another Execution was Canceled by User Problem SOLVED
    ... I've found that if I attempt to execute an execute dts package task ... > disconnected edit window). ...
    (microsoft.public.sqlserver.dts)
Quantcast