Can not run SSIS package using job
-
My package has two connections to SQL server (pulling data) and one connection to an Access DB (receiving data). to rule out security I tested this way. I can run the package using the execute package utility on the server while logged in as domain admin. The job step set to run as "SQL Agent Service Account" (is being started as domain admin) using the same options as the package utility and will not run.
How can the package run manually on the server as domain admin but not under the Agent account which is also running as domain admin? My other packages work fine but they also do not have a connection to Access.
The error I get from the log is...
Validation phase is beginning.
Validating
Validating
The AcquireConnection method call to the connection manager "proval mdb" failed with error code 0xC0202009.
component "OLE DB Destination" (61) failed validation and returned error code 0xC020801C.
Validating
One or more component failed validation.
There were errors during task validation.
EDIT:
Forgot to mention that this is 64 bit Standard SP1 on Win 2003k Standard but I don't think this has anything to do with that since it will run on the server just not through a SQL job.
-
I don't think this KB is the problem but I could be wrong
-
You cannot run an SSIS task that gets data from Excel or Access as an SSIS package job step - there is no 64-bit OLEDB provider for Excel or Access and when SQL Agent executes an SSIS package it runs in 64-bit.
The workaround is to use an Operating System CmdExec job step - and call the 32-bit dtsexec to run the package that uses an Access or Excel OLEDB provider. Sample command line:
C:\ProgramFiles (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec /SQL "\SSIS_Package_NAME_Here" /SERVER SQL_Server_NAME /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
Your package and server names have to be substituted. Another way is to:
1) Create the job step as an SSIS Package type.
2) Change to Operating System (CmdExec) job step.
3) Insert “C:\ProgramFiles (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec “ ahead of the “/SQL......” in the Command window.
-
For some reason I didn't get the notify of your reply.
This is the command line that is generated when I successfully run the package on the server...
/DTS "\MSDB\rewDataToPromap" /SERVER BUZZ /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
This is what is the command line generated in job
/DTS "\MSDB\rewDataToPromap" /SERVER buzz /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
The only difference that I see between the two is the reporting option. Does one of these run the 32 bit and one 64 bit? That is why I didn't think this was an issue with the 64 bit. If one does run as 32 bit... why/how one and not the other?
-
To use the 32-bit dtexec within a SQL Agent job you need to use the Operating System (Cmd Exec) job step type - ahead of your command line "/DTS ...." use:
"C:\ProgramFiles (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec "
This will use the 32-bit dtexec in the (x86) folder.
After you use this a few times it becomes fairly transparent. Initially it seems there must be a simpler way to perform a 'vanilla' operation like connecting to Excel or Access. Complicating matters is that most client tools you use are 32-bit and use 32-bit providers so it's only when you run a job through SQL Agent that it actually runs as 64-bit - that why it "works everywhere except in a SQL Agent job"....
Hope this helps!
-
Guess I was a little slow on that one getting through my head. I understand now. TheI was using to run the package was 32 bit so it worked but it was trying to run the same thing in 64 bit mode because it was going through the agent.
Thanks for sticking with me on that one
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply