April 27, 2009 at 8:06 am
Hi there,
I have a bunch of packages that were designed on a 32bit machine that use OLEDB Provider for SQL Server drivers. They are all configured using a configuration file maintained via an environment variable. The protection level of my packages is set to DontSaveSensitive. When it came to deploying them on my target server today, my SQL Agent Job running my package threw the following error:
Message
Executed as user: COMPUTER_NAME\SYSTEM. ...rsion 9.00.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:45:44 AM Error: 2009-04-27 09:45:47.92 Code: 0xC0202009 Source: ETL_Package Connection manager "OLEDB - Store" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user DOMAIN\COMPUTER_NAME$'.". End Error Error: 2009-04-27 09:45:47.92 Code: 0xC020801C Source: Retrieve Info from Staging Table [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "OLEDB - Store" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method cal... The package execution fa... The step failed.
The thing is that after I went and opened my package in BI Studio on the target server and saved it again, the job worked. I'm wondering if there's some sort of information that would need refreshing because it seems to be that way. It's weird because we have deployed them before on other test environments in 32bit without any issues hence my hunch about a 64bit problem with an OLEDB driver...
If anyone has any idea how to potentially avoid having to do this, it would be greatly appreciated.
Thanks,
Greg
April 28, 2009 at 2:17 pm
Greg --
This sounds like an issue in the way the package is being executed and the drivers being used. You may want to try something like below in your SQLAgent job used to execute the SSIS package.
Your job step would be an Operating System task (cmdexec) rather than SSIS package task. Adapt the command below to suit your needs. The command below assumes the package is stored in msdb.
C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe /SQL "\SSISSubfolder\YourSSISPackage" /SERVER YourServerNameHere /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
Hope that helps.
Nivek
May 5, 2009 at 12:01 pm
Hello and sorry for the late reply. We ended up finding out what that the problem was related to an environment variable that was registered under the user section. When the package would run as a SQL agent job, SYSTEM would try to run it but would not be able to find the configuration variable to load the config file. Setting it as a system variable fixed this issue.
Thanks for the help Grasshopper!
Greg
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply