Running DTS packages on 2008 R2

  • Hope someone can help me on this one

    We have a 2008 R2 cluster which failed over recently, a number of jobs that run DTS packages failed due to the DTS components not installed. Went through the install of the DTS components and the 2005 Backwards Compatability. At this point I still couldn't open a package on the server. Read up a few articles that showed that on occasion, binary files needed to be manually copied into the program files directory where the SQL server was installed (http://msdn.microsoft.com/en-us/library/ms143755%28v=sql.105%29.aspx)

    After this was done I could open the packages but the jobs still failed.

    The code in the job step is set to run as a CmdExec with the direct call to the DTS package on the server

    e.g dtsrun /S "Server" /E /N "Package"

    I tried running this code via xp_cmdshell and got the error that the dtsrun component could not be found. Unfortunately I couldn't test adding the full path of the location of the DTSRun into an xp_cmdshell command but I'm guessing that this is what the error is down to.

    A bit more research found that there can be issues with the order of path's in a machines Environmental Variables:

    http://blogs.msdn.com/b/ramoji/archive/2008/10/16/how-to-open-dts-packages-in-sql-server-2008.aspx

    I made the necessary changes but the job still fails.

    One last thing, I compared the location of the dll's and rlls (that have to be manually moved as directed in the first link) from the node where the DTS packages run to where the DTS packages don't run and there were some discrepancies (strangely enough on the node that the packages are working, they're not in the same folder as directed in the first link). Made the necessary changes and now both nodes are identical in terms of file locations

    The jobs still failed. Can anyone else suggest something??

    Thanks

  • Another note on this, I tried recreating this in a virtual environment with an image of the instance and replicated the steps as per the MSDN blog, the package worked fine. Therefore I'm guessing that its something on the actual box that's different or that's causing a problem.

    Anyway, feel free to contribute any suggestions guys

  • Open a command prompt on each node, navigate to the root of the disk and execute "dtexec /?" (without quotes). Compare the version number in both outputs. They should be the same. If they differ, compare the PATH environment setting (command: "SET PATH") regarding the sequence of folders to be searched.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Hanshi, same version and same Path in environment variables

    Unfortunately I can't do much testing until the next time the cluster fails over onto the offending node again

  • Here is how I do it:

    "C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\dtsrun.exe" /S 127.0.0.1 /E /N dtspackagename

  • Terence - Yep I know that if I change the job step to run at the full file path for using dtexec or dtsrun it will probably work but unfortunately that means changing the job step on a production database which requires a ton of sign off.

    Thanks for the suggestions on this one guys

  • I've read your first post again and noticed something. You are using DTSRUN in your command prompt. The dtsrun command prompt utility (dtsrun.exe) that was used in SQL 2000 has been replaced by the dtexec command prompt utility (dtexec.exe) in SQL 2005 and newer.

    http://msdn.microsoft.com/en-us/library/ms345282(v=sql.105).aspx

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Thanks Hanshi, I'll try that the next time the cluster fails over onto the 2nd node

Viewing 8 posts - 1 through 7 (of 7 total)

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