We are currently moving existing SSIS packages from one server to another. The former server is 32 bit and the new server is 64 bit clustered (not sure if that's relevant or not, though).
One package in particular is giving me a headache. It pulls from an access file to sql server 2005 table. We set up the security as don't save senstive and put the connection strings in a config file. We had to set the original job up to run the package step under a proxy account to get it to work.
If we right click on the package through the SSIS Store interface and execute it runs fine. However when we try to run it through the scheduled job, it fails. I very much appears to be a permissions issue on the proxy account. The person setting up the server is somewhat new to the area of SQL Servers, so they cannot provide much feedback. We're very much in a tweak until it works position, unfortunately.
Here are the errors that I am getting, one from the history of the job, one from the SQL Server logging. Does anyone have a suggestion, and if it is a permissions issue, where to look? I've been at this for days, so I've tried several different approaches. It seems like right now that I almost got it to work, but it seems like the connection to the access db is failing. But if that's the case, why does it run fine through the proxy on the original server (using same account)? I guess that very well could come down to the 64 bit problem (saw this on some other posts). I should mention that I set up the package as Run64BitRuntime = false. Do I need to rethink this as a batch file using DTExec? That was actually our original solution on the former server, but we could never get to work.
Job History Error:
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Cost_WorkDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2007-03-23 16:19:14.92 Code: 0xC0047017 Source: Data Flow Task DTS.Pipeline Description: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C. End Error Progress: 2007-03-23 16:19:14.92 Source: Data Flow Task Validating: 100% complete End Progress Error: 2007-03-23 16:19:14.94 Code: 0xC004700C Source: Data Flow Task DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-03-23 16:19:14.94 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation.... The package execution fa... The step failed.
sysdtslog90 error:
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Cost_WorkDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
OK I solved the problem myself. This was in fact a 32-bit vs. 64-bit problem, even though the lovely errors I was getting didn't even come close to explaing that to me. I provided a link below on how to run a package in a scheduled job that hits a Jet 4.0 connection (I think excel may have this problem also).
But, I hate when people simply do that (include link), so here's how I fixed it.
Create a new scheduled job.
Add a new step.
When you edit the step, the 'Type' will be "Operating System (cmdExec)"
You can use DtExecUI to create your command line step, but here is what I used exactly - quotation mark locations are VERY important. Also, I think you may need to have the complete call on one line:
\\Server\E$\Program Files\Microsoft SQL Server (x86)\90\DTS\Binn\DTExec.exe /DTS "\MSDB_ATC\PACKAGE_NAME" /SERVER "SERVER_NAME" /CONFIGFILE "\\Server\R$\\PACKAGE_CONFIG.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
Edit command to match yours, and paste into job step edit.
Hope this helps someone, it took me DAYS to figure out.
http://msdn2.microsoft.com/en-us/library/ms141766.aspx
|||
thank you... It's realy helpful to me.
The following is my update list,
\\Server\E$\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe /DTS "\MSDB_ATC\PACKAGE_NAME" /SERVER "SERVER_NAME" /CONFIGFILE "\\Server\R$\\PACKAGE_CONFIG.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
|||Thanks Jay, this saved my day
No comments:
Post a Comment