executing a DTS package

a scheduled DTS package on a commercial server fails to execute, to transfer
data between two databases that I own.Can I trigger the package to execute
from code within a stored procedure ?
thanks in advance EarnieEarnie
In order to execute a DTS Package from a Stored Procedure you will need to
call the DTSRUN executable using xp_cmdshell. If you already have a Job
created to execute the DTS Package you can copy the DTSRun command from the
Job Step.
ie. EXEC master.dbo.xp_cmdshell 'DTSRun /~Z0x...3'
For more details refer to http://www.sqldts.com/default.aspx?210
- Peter Ward
WARDY IT Solutions
"Earnie" wrote:

> a scheduled DTS package on a commercial server fails to execute, to transf
er
> data between two databases that I own.Can I trigger the package to execute
> from code within a stored procedure ?
> thanks in advance Earnie

Execute SQL Task w/ XML Output

I have a stored procedure that returns XML and have defined my own "Root" element w/ "ROOT('urlset')" in the stored procedure. When I put this into my Control Flow, I have a subsequent task that takes this XML stream and writes it to a file. The issue I am running into is that SSIS is adding it's own "Root" element before my output. Is there any way of avoiding this?
Unfortunately, the Execute SQL Task always inserts the <ROOT> ... </ROOT> tags when you ask for an XML result set. The typical approach is to strip out the values you want in an XML Task or Script Task.|||

Matt Masson - MSFT wrote:

Unfortunately, the Execute SQL Task always inserts the <ROOT> ... </ROOT> tags when you ask for an XML result set. The typical approach is to strip out the values you want in an XML Task or Script Task.

Hi Matt,

Why? What's the rationale for this behaviour?

-Jamie

|||

Good question.

The task doesn't really parse the input SQL statement (or the results, for that matter), so it appears the tags were added as a simple way to ensure we were always returning a well-formed XML document.

One could argue that this should be optional behaviour, and that we should provide a property which allows you to tell the task to leave the results alone. I've opened a tracking item to consider the change for Katmai.

Feel free to open an item on Connect if you have ideas on how it should work.

~Matt

|||

No need. You beat me to it