May 13, 2013 at 2:54 am
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
May 15, 2013 at 2:49 am
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
May 15, 2013 at 5:22 am
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.
May 20, 2013 at 7:48 am
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
May 20, 2013 at 11:56 am
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
May 20, 2013 at 3:43 pm
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
May 20, 2013 at 11:49 pm
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
May 21, 2013 at 7:15 am
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