dts fired from sp

Hello,
Is it possible to fire a DTS from within a stored procedure?
tia,
bob mcclellanIf you know the command line to pass to dtsrun, and you can use xp_cmdshell,
then sure:
exec master..xp_cmdshell 'dtsrun.exe ... '
"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:u2uNrUY4FHA.2552@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Is it possible to fire a DTS from within a stored procedure?
> tia,
> bob mcclellan
>|||Select 'You''re fired' from table where job = 'DTS'
Ok, I know. It's lame but I have my reasons..
It's Friday, I'm tired, I can't wait to get home and my first grandson was
born yesterday.
"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:u2uNrUY4FHA.2552@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Is it possible to fire a DTS from within a stored procedure?
> tia,
> bob mcclellan
>|||Great.
I just needed a place to start in BOL.
Thanks much Aaron.
bob.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23mYIbYY4FHA.1396@.TK2MSFTNGP12.phx.gbl...
> If you know the command line to pass to dtsrun, and you can use
> xp_cmdshell, then sure:
> exec master..xp_cmdshell 'dtsrun.exe ... '
>
>
> "John 3:16" <bobmcc@.tricoequipment.com> wrote in message
> news:u2uNrUY4FHA.2552@.TK2MSFTNGP10.phx.gbl...
>|||Congrats Ray.
Enjoy the wend with your new grandson.
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:eY3hVaY4FHA.700@.TK2MSFTNGP15.phx.gbl...
> Select 'You''re fired' from table where job = 'DTS'
> Ok, I know. It's lame but I have my reasons..
> It's Friday, I'm tired, I can't wait to get home and my first grandson was
> born yesterday.
> "John 3:16" <bobmcc@.tricoequipment.com> wrote in message
> news:u2uNrUY4FHA.2552@.TK2MSFTNGP10.phx.gbl...
>

DTS File Created After Stored Proc executed now what?

I'm trying to fire a DTS package through a stored procedure.

After running my one stored procedure I got my DTS file to create just fine. However after this point I'm stuck...I can't do anything else with this file and I need to update data with it.

How would I use the DTS file to update my data.

Thanks,

RB

RB,

There is no direct way to do this via a SP. In my experience, I've seen three ways to work around this:

1) Use xp_cmdshell. This simply executes a command in the command shell. Usefull if you already have a batch script running your DTS.

2) Use OLE. You can create and work with Com objects via the sp_OA* methods. You will need to create the DTS objects and run properties from them.

3) Use a scheduled Job. Saw this recently, the idea is to create a new scheduled job that runs your DTS, then run the job, then delete the scheduled job.

None are pretty, but they all work.

-- Alex