September 30, 2011 at 8:55 am
I know very little about creating SSIS packages,
Here is my scenario,
Original server SQL6 (SQL 2005 32bit) – SSIS package 32bit (written by someone else)
This package is run via a SQL agent job once a week. (CMDExec)
Command is (DTExec.exe /f "\\sql6\share\Contacts Update.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /DECRYPT password)
12 months back I moved the db and SQL agent job to a new server SQL8 (SQL 2005 64bit) but left the package where it was. This caused four issues.
1)I needed to change the package database to point to the new server.
2)I needed to change the job to point a UNC for the job
3)Set SQL agent service user permissions for the folder location
4)The package was 32bit so I need to run the job using DTexec.exe (32 bit)
This run fine for 12 months
I have now moved the DB and job again to SQL10 (SQL 2005 64bit) cluster. I have followed my steps (as above) but I now have issues running the job.
If I open BIDS and run the package within BIDS it works fine.
I have run the command manually in SQL I get the follow.
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.4035.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 2:03:24 PM
Error: 2011-09-30 14:03:24.86
Code: 0xC0011007
Source: {47C78371-C57C-4939-A5B4-7A820052F597}
Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted.
End Error
Error: 2011-09-30 14:03:24.86
Code: 0xC0011002
Source: {47C78371-C57C-4939-A5B4-7A820052F597}
Description: Failed to open package file "c:\share\Contac~1.dts" due to error 0x80070005 "Access is denied.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. Thi
s can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
End Error
Could not load package "c:\share\Contac~1.dts" because of error 0xC0011002.
Description: Failed to open package file "c:\share\Contac~1.dts" due to error 0x80070005 "Access is denied.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This c
an be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
Source: {47C78371-C57C-4939-A5B4-7A820052F597}
Started: 2:03:24 PM
Finished: 2:03:24 PM
Elapsed: 0.031 seconds
NULL
I have given the user Admin rights on the both Cluster Nodes, Admin on the server where the package lives and SA rights on SQL.
I have checked the path several times (and asked someone else to also check as these seemed like the problem for most people).
I have moved the package to one of the cluster node in case it was a UNC issue.
I have converted to 8dot3 naming.
I have two versions of SSIS running on the server. (Ver 9 and Ver10 both 64bit)
I have Added the SQL agent user to the DCOM Launch and Activation Permissions for the DCOM Object MsDtsServer (bacause of an Event error message)
The application-specific permission settings do not grant Local Launch permission for the COM Server application with CLSID
{ABF05265-635E-44B0-A28F-AEA45247ACA0}
to the user XXXXXX\XXXXXXXX SID (S-x-x-xx-xxxxxxxxxx-xxxxxxxxxx-xxxxxxxxxx-xxxxx) from address LocalHost (Using LRPC). This security permission can be modified using the Component Services administrative tool.
Any help would be much appricated.
September 30, 2011 at 9:13 am
Taggs (9/30/2011)
I have two versions of SSIS running on the server. (Ver 9 and Ver10 both 64bit)
I would start with this. Sounds like you're inadvertently trying to run a package of one version in an SSIS installation of another version. Check out the following link, and do a bit of research as well - there's plenty of stuff out there on this issue:
John
October 3, 2011 at 3:31 am
Hi John,
Thanks for the reply. I have had a look at your link but I'm not sure this is the issue. I do not know much about SSIS so I'm not sure if this is the issue or not but I have tried to look at this (including starting the old service of SSIS v9, stopping version 10, both running) but to no avail. I should have explained that running both versions was my last state before posting. I have already configured SSIS to see all 4 named instances on my cluster.
This probably show my lack of SSIS knowledge but the package is not within the MSDB package list (on any of the servers old or new) and the package is run from a SQL agent Job (CmdExec). I had to run like this as a 32bit package does not run on 64bit SSIS.
This package is run via a SQL agent job once a week. (CMDExec)
Command is (DTExec.exe /f "\\sql6\share\Contacts Update.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /DECRYPT password)
I have already spent several hours on technet and google before posting asking for help as I have drawn a blank up to this point. Any help is much appricated.
Many thanks
October 3, 2011 at 4:13 am
What may be happening is that the job system is running the wrong version of DTExec.exe. I take it your job step uses the SQL Server Integration Services Package type? There may be a registry hack or something like that in order to point it to the correct path, but the safest way I've found is as follows:
(1) Find the path to DTExec.exe for the version of SSIS that you want to us
(2) Open the offending job step and click on the Command Line tab
(3) Copy the text from the Command line box on to your clipboard
(4) Change the Type of your job step to Operating system (CmdExec)
(5) Type the full path to your executable and then paste in the paramaters
John
October 3, 2011 at 4:18 am
4) The package was 32bit so I need to run the job using DTexec.exe (32 bit)
[
Hi John,
Thanks for the reply. I have already done that as that was needed on the first database move (moving from 32bit to 64 bit sql) 🙁
Many thanks for your input
Taggs
October 3, 2011 at 4:25 am
Taggs
The command you posted earlier doesn't include the full path to the executable. That was my point. If you don't specify the path, then it'll use the first one it finds in the PATH environment variable, which may not be correct for the version you're using.
John
October 3, 2011 at 4:31 am
Hi John,
Please accept my apologies, I did miss the file location from the original post but I have pointed it to the version 9 32bit version of DTexec.exe.
🙂
Your help is much appreciated!
Many thanks
Taggs
October 3, 2011 at 4:41 am
And is it working now? If not, you say you can open it in BIDS and run it. Have you tried saving it and running the newly saved package from the job?
John
October 3, 2011 at 4:58 am
Hi John,
sadly not 🙁 when I moved the job across to my cluster this was already pointing at my 32bit version of DTExec.exe.
I have rebuilt the SSIS package (pointing to the new server and DB) and tried to run but it failed.
From this I am guessing the job is fine. I think the problem lies in one of the following areas
1) Permissions (I think I have negated this by adding SQL agent account to Administrators onboth Nodes of the cluster, the Server file location for the job and admin on the file location of the AD extract) I have also moved all of these to one location on one of the Nodes on the SQL cluster.
2) A SQL cluster issue running a package?
3) SSIS problem?
4) Moving from SQL default instnace to a named instance?
5) DCOM issue for MsDtsServer (may be on a cluster?)
but I'm really just "clutching at straws" as I'm pretty stuck
Thanks
Taggs
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply