Wednesday, September 15, 2010

Use DTExec To Replace DTSRun In SSIS!....

Hello there!...

Today I was tasked with migrating a SQL Server Agent job from SQL Server 2000 to SQL Server 2008. 

This job calls a VB script file and within this file executes the dtsrun.exe command and calls a DTS package.  So as I migrated the job to the new server I upgraded the DTS package to SSIS and copied over the VB script file.  So within the VB script file I had to modify the command to go from using DTSRun to DTExec with the new parameters.  So here is what dtsrun command looked like in SQL Server 2000:
        '*DTS executable and Parameters
        DTSAPPRUN = """C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsrun.exe"" -S server
               name - E -N DTS package name

Here is what I had to change command to look like for a SSIS package:
        '*SSIS executable and Parameters
        DTSAPPRUN = """C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe"" /sq SSIS
               package name /ser server name"

Now fortunately for me, the DTS package was a simple data import package so it was converted to SSIS without issue.  So, after making the changes to the VB script file and saving... I reran the SQL Server Agent job and the VB Script executed and the SSIS package ran without issue.  I hope you find this helpful and if you have another way of doing this please post here and let us know!

Until next time...
Crazed DBA


  © Blogger template 'A Click Apart' by 2008

Back to TOP