Executing DTS in query analyzer

[Chef]

I am trying to execute a DTS package from the query analyzer and eventually putting that code into a stored procedure. The DTS package is a simple import from a text file into a table. When I execute the package from the Enteprise Manager it works fine. When I try to execute it from the Query Analyzer it says the 'The command(s) completed successfully.' however no data was inserted.

Here is the code that I am using:

DECLARE @oPKG int
DECLARE @hr int

EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
RETURN
END


EXEC @hr = sp_OAMethod @oPKG, 'LoadFromSqlServer', NULL,
@ServerName='MyServer', @PackageName='MyDTSPackage', @Flags=256
IF @hr <> 0
BEGIN
PRINT '*** Load Package failed'
RETURN
END


--Executing the Package:
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
PRINT '*** Execute failed'
RETURN
END



--Cleaning up:
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
RETURN
END

If anyone can help, I'd really appreciate it. Thanks in advance.

 

[Antzz]

Did you try executing it from the Query Analyzer inside Enterprise Manager?

 

[Chef]

Yes, I opened the Query Analyzer from Enterprise Manager.

 

[Antzz]

When you execute the package from the Analyzer, it says &quot;'The command(s) completed successfully&quot; because it seems like it means that the command to execute the package was successful. It does not mean that the contents of the package were successfully executed.

I would suggest, you create a logging table where you log the various steps in the package through insert statements(like an audit trail - like the PRINT statements you have created). You will know where it is failing when you execute the package in Analyzer.

Let me know if this helps.

 

[Chef]

Thanks for your help Antzz but unfortunately I do not have sa rights in this database so I have no idea what the sp_OA... stored procedures do so I'm not sure what to log. I found a work around by doing a Bulk Insert instead of using DTS. Thanks again.