SQL Server Performance

How to Run DTS from T-SQL

Discussion in 'SQL Server DTS-Related Questions' started by Mast_dba, Feb 7, 2006.

  1. Mast_dba New Member

    Hi All
    Can 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
  2. Luis Martin Moderator

    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.



  3. ranjitjain New Member

    try this in QA:

    declare
    @cmd nvarchar(1000),
    @servername nvarchar(20)
    set @servername=@@servername

    set @cmd=
    'dtsrun /S'+@servername+' /Nyour_DTS_Name /EMicrosoft OLE DB Provider for SQL Server'
    exec master..xp-cmdshell @cmd, no_output
  4. dineshasanka Moderator

    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
  5. Madhivanan Moderator

    >>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
  6. ranjitjain New Member

    <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 />&gt;&gt;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=':)' />]
  7. mmarovic Active Member

    Why do you need to run dts package from query analyzer?
  8. Mast_dba New Member

    Thanks buddies for ur help. I really appriciated ur quick response.
  9. Madhivanan Moderator

    &gt;&gt; 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
  10. Mast_dba New Member

    quote:Originally posted by ranjitjain

    try this in QA:

    declare
    @cmd nvarchar(1000),
    @servername nvarchar(20)
    set @servername=@@servername

    set @cmd='dtsrun /S'+@servername+' /Nyour_DTS_Name /EMicrosoft OLE DB Provider for SQL Server'
    exec master..xp-cmdshell @cmd, no_output



    Hi
    Sorry respond late. What should be the value of @cmd here.
    Thanks
    Mast
  11. Madhivanan Moderator

    Print @cmd to know what it has

    Madhivanan

    Failing to plan is Planning to fail

Share This Page