Executing a DTS package from Sql Server 2005

views:

175

answers:

Executing a DTS package from Sql Server 2005

I am trying to run several DTS packages from a sql 2000 box. The DTS calls will originate from .net 2.0 - 3.5 code. I have been unable to find a good way to programmatically accomplish this. What I have read is running a sql job from a stored procedure that calls the DTS package. Does anyone has any experience with this or know of a good way to call the DTS?

Note: For the forseeable future these packages will remain DTS. Eventually we will convert them to SSIS.

Any insight or experience would be very helpful.

I was in a similar situation at work, and we ended up 'wrapping' our DTS packages with single-step SSIS packages that use DTSRun (installed with Sql 2005) in a command-line fashion to call to invoke a DTS package on the file system. If you were to do that, then it'd be a matter of programmatically launching an SSIS package which I'm not familiar with but it's got to be just as possible as DTS on 2000.

ray2k 2010-04-15 21:25:09

The issue with using SSIS is the machine that is running the code has to have Sql Agent running on it. Currently the users environment does not have this setup. So ideally since there is a solution now which it (vb 6 code), we would like to not have extra overhead on there end.

Doerr 2010-04-16 13:12:52

Of course unless it is possible to call an SSIS package from a stored procedure?

Doerr 2010-04-16 13:28:53

Not easily. You could enable xp_cmdshell on the db and call whatever command line you want, but I'd recommend against that for the security exposure. Or go with Sql Agent, and use something like Sql Management Objects (SMO) to connect to the server remotely and start a job programmatically, accepting the 'overhead'. Pick your poison.

ray2k 2010-04-16 14:43:12

Ahhh.. I have found a Com Object in the .net library that allows me to interact. Its is Microsoft DTSPackage Object Library. Problem averted.

Doerr 2010-04-16 17:42:07

ansaurus

tags:

views:

answers:

Executing a DTS package from Sql Server 2005

related questions