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:
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??