SQL Server Performance Forum – Threads Archive
How to Run DTS from T-SQL
Hi AllCan you tell me how to run DTS package from query Analyzer. I know how to run it from CMD & SQL EM. Please let me know ASAP.
Many thanks in advance.
Mast
From BOL: Querying a DTS Package from External Sources
Data Transformation Services (DTS) package data can be made available to an external source, such as SQL Query Analyzer, by: Querying a package step associated with a transformation with the Transact-SQL OPENROWSET statement.
Defining the package as a linked server and joining package data in a distributed query.
When querying package rowset data, the following conditions apply: The package supplying the data must be launched by the application getting the package data. You do not execute the package to send the data to the requesting application or process.
You can only query a package step associated with a Transform Data task.
The package must have destination columns to bind to; the destination cannot be a text file.
In DTS Designer, you make package data available by selecting the DSO rowset provider check box (on the Options tab of the Workflow Properties dialog box) for a package step associated with a Transform Data task. The data from that task then becomes available to an external data consumer. Enable the DSO rowset provider check box only for packages that you intend to query. After the option is set, the package step where you set the option does not complete execution when the package is run normally. While the flag is set that pump task can only be accessed through OPENROWSET. Note These methods are used typically to query packages from an external source; however, you can also query other packages from within a package by issuing OPENROWSET queries and distributed queries in an Execute SQL task or as the source for another Transform Data task. See Also OPENROWSET Transform Data Task Using SQL Query Analyzer Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS†with no warranties for accuracy.
try this in QA: declare
@cmd nvarchar(1000),
@servername nvarchar(20)
set @[email protected]@servername set @cmd=
‘dtsrun /S’[email protected]+’ /Nyour_DTS_Name /EMicrosoft OLE DB Provider for SQL Server’
exec master..xp-cmdshell @cmd, no_output
Using DTSRUNUI you can generate the t-sql even you can generate encrypted scripts
DTSRun /S "(local)" /N "DTS_Trigger_TestonDTS" /G "{FDD693DA-82EC-46FB-8417-1C471B851712}" /E —————————————-
http://spaces.msn.com/members/dineshasanka
>>exec master..xp-cmdshell @cmd, no_output That should be Underscore not hypen exec master..xp_cmdshell @cmd, no_output
Madhivanan Failing to plan is Planning to fail
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />>>exec master..xp-cmdshell @cmd, no_output<br /><br />That should be Underscore not hypen<br /><br />exec master..<b>xp</b>_<b>cmdshell</b> @cmd, no_output<br />Madhivanan<br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />-Madhivanan,<br />Actually i should have posted this in your thread "posting error mesages"<br />As when i used [underscore] it was not posting the message and was erroring<br />so instead of that i replaced it delibrately with hyphen and i thought user must be atleast this much knowledgeable about this extended sp where its underscore and not hyphen.[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />][<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]
Why do you need to run dts package from query analyzer?
Thanks buddies for ur help. I really appriciated ur quick response.
>> i thought user must be atleast this much knowledgeable about this extended sp where its underscore and not hyphen.<br /><br />Yes it is [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
quote:Originally posted by ranjitjain
try this in QA:
declare
@cmd nvarchar(1000),
@servername nvarchar(20)
set @[email protected]@servername set @cmd=’dtsrun /S’[email protected]+’ /Nyour_DTS_Name /EMicrosoft OLE DB Provider for SQL Server’
exec master..xp-cmdshell @cmd, no_output
Hi@cmd nvarchar(1000),
@servername nvarchar(20)
set @[email protected]@servername set @cmd=’dtsrun /S’[email protected]+’ /Nyour_DTS_Name /EMicrosoft OLE DB Provider for SQL Server’
exec master..xp-cmdshell @cmd, no_output
Sorry respond late. What should be the value of @cmd here.
Thanks
Mast
Print @cmd to know what it has Madhivanan Failing to plan is Planning to fail
]]>