Execute DTS in Query Analyzer

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. Is there anything I need to do to the package to enable it to be executed externally? Please help!

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.Hey Dkwon

I've had exactly the same problem as you. I never got the package to work through Query Analyzer, but I think I know what causes it.

When you run the package through Enterpirse manager you are using your own login and therefore have access to objects that your login has permission to access.

As soon as you use sp_OACreate it creates an instance of the OLE object (in this case a DTS package) on the server. Thus, the package is being run from the server, and the server may not have permission to execute the package. Try run the package through enterprise manager on the server and see what happens?

Hope this helps. Let us know how you fare.

Lionel|||I'm still looking for a solution to this problem if anyone has one. How do I get the server to have the right permission? --John