Execute DTS Packages

I have created a functioning DTS package inside Enterprise Manager and
now I want to be able to execute it outside of EM. The package imports
data from an SQL dbase to a Visual FoxPro dbase. I would like this DTS
package to execute everytime an Insert(of certain critera) is made on
the SQL dbase. I am thinking that using an Insert Trigger on the SQL
table is the way to go about this. I am seeking advice as to... is this
the best way to go about this.

Also, and if so, in a trigger which route is the best to take...
1. use dtsrun command line utility or
2. setup a COM object to run the DTS package

I have tried both in SQL query analyzer and am having troubles with the
syntax. Could anyone possibly send the code for both ways. Thanks.
Good Day to all,

BrettIts not clear why you would want to do this... What are you doing in
the DTS that you cant do thru normal trigger? It would help if you can
explain more....

Not sure if you can run the package thru trigger.. May be set up a job
and invoke a job... I am also curious...|||I have tried linking SQL database to the VFP tables with T-SQL and have
had no luck. I have tried for weeks to link the two via Remote Servers
or T-SQL and working with directly in triggers. So far this is what I
have been able to get to work. If you have other ideas or any help
would be appreciated.

Brett|||You should be able to fire up DTSRUN using sp_oacreate in your trigger.
Have you tried this?

Having a job that calls the DTS is another option, then start the job
in your trigger with msdb..sp_start_job or something like that.
I have done both of these.|||Yes I have tried this without sucess. The package executes and works
properly when I run it manualy in EM. However, when I place the code
listed below in SQL Query Analyzer it does not execute. The code
parses fine, no errors and when it is executed QA returns 'completed
the command successfully', but nothing has happened. Here is the
code...

DECLARE @.object int
DECLARE @.hr int

--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
BEGIN
print 'error create DTS.Package'
RETURN
END

EXEC @.hr = sp_OAMethod @.object,
'LoadFromSQLServer("ShannonPC", "sa", "", 256, , , ,
"MiTekTransfer")', NULL
IF @.hr <> 0
BEGIN
print 'error LoadFromStorageFile'
RETURN
END

EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
print 'Execute failed'
RETURN
END

Any ideas as to why this is not running my DTS package.

Thanks|||This code shows an example, I have used it before with success:

http://www.databasejournal.com/feat...cle.php/1459181

The first problem I see is, your LoadFromSQLServer parameter "256" is
incompatible with passing in "sa" as your username. "256" means
DTSSQLStgFlag_UseTrustedConnection, which says to use a trusted
connection. So my guess is that you're having a permissions problem.
Change the "256" to "0" and try again.

I am surprised that you got no error message back from sp_oacreate
though...

Gary|||Yes, I recongized this issue after I made the post and did some further
searching. If I use win. auth. in the package I set parameter to 256,
with no user or pass, and if I set SQL auth. in the package I set
parameter to 0 with user sa. I have tried both with the same
results... no errors and no execution.|||Do you have DTS package logging turned on? Can you tell it's not even
starting the package?

Try just starting DTSRUN.exe from a command line, and see if that
works.

sp_oacreate may be disabled as a security measure, but if it was I
would expect that you would get an error back.

Can you try putting a dtsrun.exe call in a job? Run the job and see if
it starts the DTS package. If not you have bigger problems.

If it works in the job, use msdb..sp_start_job to start the job from
the trigger.|||It seems that the issue might be with connecting to a Visual Fox Pro
table outside of the DTS program. The package which transfers from SQL
to VFP works in DTS but not in SQL Query Analyzer. I setup up another
dts package which transfers the same data but from SQL to a text file.
This package executes properly in DTS and SQL QA. It operates under
the same code as posted above, with changing the package name. The VFP
table are on the local machine though.

I also tried exec master..xp_cmdshell 'dtsrun /Sshannonpc /E256 /
Npackagename. This executes the package with the text file but not the
VFP tables. I get the error 'Invalid class string.' However, no
errors occur when runing the package manually in DTS. Ideally I would
like to import right into the VFP tables and not have another app that
reads from the text file then to the VFP tables. ?|||When you say "The VFP table are on the local machine", what do you
mean? On your local desktop machine, or on the database server?

Sometimes people get local/server paths mixed up. The path to the VFP
needs to be reachable via the server, not your local PC. Is this the
problem?|||Gary,

I am running SQL Sever 2000 on my local desktop machine and the Visual
FoxPro tables are located on the c:\ drive on this same computer.

Maybe you could answer this... In SQL Query Analyzer this command
**(exec master..xp_cmdshell 'dtsrun /SShannonPC /E256
/NMiTekTransfer')** to run the DTS Package gives me this error:
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147221005
(800401F3) Error String: Invalid class string

However if I run **(dtsrun /SShannonPC /E256 /NMiTekTransfer)** from
the command prompt it execute fine. The DTS package also executes
manually form the DTS window.
Any thoughts? Thanks for helping.

Brett