Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Running DTS packages on 2008 R2 Expand / Collapse
Author
Message
Posted Monday, May 13, 2013 2:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 8:48 AM
Points: 63, Visits: 360
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
Post #1452001
Posted Wednesday, May 15, 2013 2:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 8:48 AM
Points: 63, Visits: 360
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
Post #1452981
Posted Wednesday, May 15, 2013 5:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:40 AM
Points: 2,480, Visits: 3,023
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’! **
Post #1453022
Posted Monday, May 20, 2013 7:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 8:48 AM
Points: 63, Visits: 360
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
Post #1454539
Posted Monday, May 20, 2013 11:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 28, 2014 12:22 AM
Points: 19, Visits: 354



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
Post #1454663
Posted Monday, May 20, 2013 3:43 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 8:48 AM
Points: 63, Visits: 360
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
Post #1454766
Posted Monday, May 20, 2013 11:49 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:40 AM
Points: 2,480, Visits: 3,023
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’! **
Post #1454820
Posted Tuesday, May 21, 2013 7:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 8:48 AM
Points: 63, Visits: 360
Thanks Hanshi, I'll try that the next time the cluster fails over onto the 2nd node
Post #1454989
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse