August 4, 2010 at 10:34 am
Hi,
I am developing a package that runs fine in BIDS but throws the below listed Validation Error while running the Deployment Wizard and fails when I attemp to run the package using SQL Agent Job.
Error Message:
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "remoteconnect" failed with error code 0xC0202009. There may be error messages posted before this with more info on why the AcquireConnection method call failed. (Although there are no additional messages before this one).
Error component "OLE DB Source" failed validation and returned error code 0xC020801C.
Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. Source. "Microsoft SQL Native Client" Hresult: 0x80040E4D Description failed for user: "remoteuser"
I have read several posts online about this error message but no suggested resolution works in my case. As stated above the package runs successfully in BIDS so I know the login credentials are right and my Data Sources are configured properly as well. Also the OLE DB connection error is only happenning on the remote Data Source that we use "SQL Server Authentication" for not for Data Sources using "Windows Authentication". The ProtectionLevel for the package is "EncryptSensitiveWithPassword".
Thanks for taking a look and any help is appreciated.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
August 4, 2010 at 10:43 am
How do you have your connection manager configured?
Are you using a configuration file with your package?
Is the package stored on the server that is executing the agent job?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 4, 2010 at 12:07 pm
1) No, I am not using configuration files with my package. My Data Sources are defined at the project level and added to each package.
2) Yes, the package is stored in the MSDB package store on the same server that is executing the Agent Jobs.
Thanks,
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
August 4, 2010 at 12:16 pm
From BIDS, try going to File menu and choose "Save Copy of Package As" option (there's two Save as options....choose Save Copy". Then you'll get a "Save Copy of Package" pop up window. For Package Location choose "SQL Server" and for Protection level choose "Rely on server storage and roles for access control". I've always found this to be the most straight forward way to deploy and it seemed to fix the error you are reporting. Let me know if it works.
Dave Coats
August 4, 2010 at 1:22 pm
I followed the steps above but still got the same error.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
August 4, 2010 at 3:26 pm
Thanks to all of you for your help. The solution is simple and it's an SSIS best practice as well. "Use SSIS Package Configuration". Basically at runtime my Data Sources didn't know what the password of the package connection string was, therefore, causing the error. In BIDS the password is stored but at runtime that information is not available. To resolve the issue I setup an XML Package Configuration file, added the password and pointed the package to use the file instead of the Data Source when I configured the SQL Agent job. Works like a charm!!!!
Thanks again for your time and contributions.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
August 5, 2010 at 5:12 am
Great! Glad you figured it out....and thanks for posting your solution.
Dave Coats
August 5, 2010 at 5:58 am
Data sources are to be avoided when working with multiple environments.
See the following resource (from MS):
http://msdn.microsoft.com/en-us/library/cc671619.aspx
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply