Post reply

how to run DTS Package using sql query

  • March 29, 2009 at 11:12 pm

    #73624

    Hi all,

    I have created a DTS package can u help to run the package using

    sql query

    thanks in advance

    Regards

    Durgesh

  • March 29, 2009 at 11:41 pm

    #968144

    Hi Durgesh,

    You can call the "DTSRUN" utility via "xp_cmdshell". Read more about DTSRUN here

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • March 30, 2009 at 3:23 pm

    #968731

    You could also create a job for the DTS package and then use sp_start_job to run it.

  • April 1, 2009 at 4:59 am

    #969721

    Using the procedure below enables us to check which DTS Packages are actually used and rename/delete unused packages after appropriate time to save confusion

    create procedure RunPackage

    as

    declare

    @server varchar(50)

    ,@cmd varchar(250)

    ,@cmd2 varchar (20)

    set @server = (select top 1 originating_server from msdb.dbo.sysjobs)

    if upper(@server)= {Production Server Name}

    begin

    set @cmd = 'c:\Progra~1\Micros~4\80\Tools\Binn\DTSRun.exe /S '

    end

    if upper(@server)= {Development Server Name}

    begin

    set @cmd = 'c:\Progra~1\Micros~3\80\Tools\Binn\DTSRun.exe /S '

    end

    set @cmd = @cmd + upper(@server) + ' /E /N ' + @PackageName

    exec @Error = master..xp_cmdshell @cmd

    INSERT INTO PackageRunHistory(PackageName, DateTime,Error)

    VALUES(@PackageName, getdate(),@Error)

    if @Error <>0

    begin

    set @ErrorMess = 'The Package ' + @PackageName + ' Failed'

    RAISERROR (@ErrorMess, 16, 1)

    end

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply