Execute a DTS package from a Stored Procedure or Trigger

[DrSql]

I have a procedure to manipulate some data and at the end I need to out that in to a *.txt and *.csv format.
So I created a DTS package. Now I would like to call the DTS package as a last step of the stored procedure or Trigger.
Any suggestions would be great.
Thanks



Dr. Sql
vpathisseril@yahoo.com
Good Luck.

 

[mrdenny]

xp_CMDShell 'dtsrun ...'

Not pretty, but effective.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[yksvaan]

I wouldn't do it from a trigger. I quote member donutman from SQL Programming forum:

Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil!

And another, freely quoted, don't remember whose:
Cursors are good if you don't know Sql
(meaning use preferably set-based instead of row-based)

Good luck

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[mrdenny]

I agree that triggers are evil, but sometimes they are needed. Just not as often as most people think.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[DrSql]

Is anyone have any altrenate method to this.
Basicallly I want users to generate this file when ever they needed.
Thanks


Dr. Sql
vpathisseril@yahoo.com
Good Luck.

 

[Glowworm27]

you can execute a dts package from TSQL, this link has a great article, and code to run DTS packages with or without global variables from tsql, then you can just call them inside your stored proc.

http://www.databasejournal.com/features/mssql/article.php/1459181

Hope this helps


George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips