Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Running DTS packages on 2008 R2


Running DTS packages on 2008 R2

Author
Message
mitzyturbo
mitzyturbo
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 577
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
mitzyturbo
mitzyturbo
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 577
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
HanShi
HanShi
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 3633
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’! **
mitzyturbo
mitzyturbo
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 577
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
Terence Keys
Terence Keys
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 542
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
mitzyturbo
mitzyturbo
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 577
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
HanShi
HanShi
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 3633
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’! **
mitzyturbo
mitzyturbo
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 577
Thanks Hanshi, I'll try that the next time the cluster fails over onto the 2nd node
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search