The AcquireConnection method call to the connection manager failed with error code 0xC0202009

PROBLEM:

Today I encounter a problem when deploying my SSIS packages to production environment. The package itself is basically get data from a flat file located at an input folder, populate flat file data to a database table, do manipulation with the data within the table then generate some report and output some flat file to a destinlation folder.

Configurations like connection string, are set up in the config file. There are some other parameter are setup in the database table which the SSIS package will read at the first step then use it at runtime.

 

ERROR:

Message
Executed as user: MSOIT\app_services_ppsql. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.1600.1 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  11:54:21 AM  Error: 2011-12-09 11:54:22.38     Code: 0xC0202009     Source: CBLoad_Bell_Package Connection manager "ctor-dc01sql04.ArielDC4"     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 Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'MSOIT\App_Services_PPSQL'.".  End Error  Error: 2011-12-09 11:54:22.38     Code: 0xC020801C     Source: CBLoad_Bell_Package Log provider "SSIS log provider for SQL Server"     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "ctor-dc01sql04.ArielDC4" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  COM error object information is available.  Source: "CBLoad_Bell_Package"  error code: 0xC0202009  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 Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'MSOIT\App_Services_PPSQL'.".  ".  End Error  Error: 2011-12-09 11:54:22.46     Code: 0xC0202009     Source: CBLoad_Bell_Package Connection manager "ctor-dc01sql04.ArielDC4"     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 Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'MSOIT\App_Services_PPSQL'.".  End Error  Error: 2011-12-09 11:54:22.46     Code: 0xC020801C     Source: Data Flow Task - Flat File to SQL OLE DB Destination [224]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "ctor-dc01sql04.ArielDC4" 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: 2011-12-09 11:54:22.46     Code: 0xC0047017     Source: Data Flow Task - Flat File to SQL SSIS.Pipeline     Description: component "OLE DB Destination" (224) failed validation and returned error code 0xC020801C.  End Error  Error: 2011-12-09 11:54:22.46     Code: 0xC004700C     Source: Data Flow Task - Flat File to SQL SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2011-12-09 11:54:22.46     Code: 0xC0024107     Source: Data Flow Task - Flat File to SQL      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:54:21 AM  Finished: 11:54:22 AM  Elapsed:  0.656 seconds.  The package execution failed.  The step failed.

 

CAUSE:

As per my understanding, if DelayValidation is not set to true to any connection that use config file value or any runtime value, SSIS engine uses the design time values of a task until it actually runs a task. Since we are dynamically setting the connection manager, instead of using the dynamically set value (Which it uses anayway while running the task), it tried to validate the task with the design time values set for the connection manager. Unfortunately, the service account with which the job is running doesn't have access to the server and database set during the design time for the connection manager. So, the validation of the task failed causing the job to fail.

 

Fix:

Changed the DelayValidation property of anything need to swap runtime value to true.

Comments (5) -

5/29/2013 9:52:26 PM #

click through the up coming article

Good post!

Here is my web page;  click through the up coming article - Www.Boidepot.com/.../strategies-for-moving-the-proces

click through the up coming article

5/30/2013 10:00:13 PM #

Augusta

Naturally, what a fantastic web site and illuminating posts, I'll add backlink - bookmark this web page? Regards

Take a look at my web blog ...  Augusta - volleyballmeme.com/.../

Augusta

5/30/2013 10:04:21 PM #

Get Source

That you are an incredibly brilliant man!

Here is my page  Get Source - shameme.net/.../obtain-the-health-care-insurance-you-require-without-having-a-trouble

Get Source

5/30/2013 10:28:51 PM #

careers working from home

I am not typically the place of blogs, but I would prefer to say that this spot is genuinely forced me to not be so! Critical post.

Also visit my web site ::  careers working from home - www.clavio.de/.../Want_To_Set_Up_A_Home_Based_Business__Try_These_Pointers

careers working from home

5/30/2013 11:09:23 PM #

please click the next website page

As a Newcomer, I'm generally searching on the internet for articles which can help me. give thanks you

Feel free to visit my web blog  please click the next website page - x.nicbit.com/.../we-ll-show-you-all-about-debt-co

please click the next website page

5/30/2013 11:33:10 PM #

Dominique

I usually don't answer my messages but within this case. WoW! '

Also visit my blog:  Dominique - www.guitartube.com.au/.../discover-what-search-engine-optimisation-is-capable-of-doing-for-the-site.

Dominique

5/31/2013 12:14:00 AM #

vergleich krankenversicherung privat

In searching for for web internet sites related to web hosting and specifically comparison hosting Linux strategy web, your website came up. Smile

Feel free to surf to my site;  vergleich krankenversicherung privat - businessmodelsbeyondprofit.com/.../

vergleich krankenversicherung privat

5/31/2013 3:03:08 AM #

Nina

Hi there, I found your weblog by means of Google even though searching for initially aid as well as your spot looks extremely occupying for me.

my blog post ...  Nina - Scispy.discovery.com/.../

Nina

5/31/2013 3:38:59 AM #

private krankenversicherung vergleiche

I've been browsing about for the top web-site to get 1.Smile

My blog -  private krankenversicherung vergleiche - www.oyunadresiniz.com/blogs/modestoro-55350.htm

private krankenversicherung vergleiche

5/31/2013 4:04:51 AM #

Tristan

Thanks !! incredibly assistive spot!

Visit my website  Tristan - raymonrpm.xanga.com/.../

Tristan

5/31/2013 4:08:05 AM #

click through the following post

Hi, I cannot comprehend ways to add your website in my rss subscriber. Are you able to aid me, please Smile

Have a look at my blog ::  click through the following post - www.ibounion.com/.../How-To-Get-A-Effective-Home-Based-Business

click through the following post

5/31/2013 4:09:28 AM #

student loan interest rate

In exploring for web-sites related to web hosting and especially comparison hosting linux strategy net, your website came up.

Feel free to surf to my blog post;  student loan interest rate - test.victimsofslaveryandtorture.com/approaches-obtaining-best-declaring-individual-bankruptcy

student loan interest rate

5/31/2013 4:12:19 AM #

service business ideas

It was fascinating. You appear quite experienced within your sphere.

Also visit my website -  service business ideas - mangdienthoai.com/.../

service business ideas

5/31/2013 5:10:45 AM #

debt elimination service

Hello. significant operate. I did not anticipate this. This can be a superior story. Thank you!

Also visit my web-site -  debt elimination service - www.snafu.co/.../need-to-discover-more-on-consoli

debt elimination service

5/31/2013 5:13:21 AM #

Adriene

Hello, I saw your blog through Google as well as your spot appears incredibly occupied for me.

my blog post -  Adriene - hooknhound.com/index.php

Adriene

5/31/2013 7:05:42 AM #

How To make money clickbank

Hi. wonderful job. I didn't anticipate this on a Wednesday. This can be a good story. Offers Thanks!

Feel free to surf to my web site  How To make money clickbank - Www.Energie-Glasnost.de/.../Techniques_For_Advanced_Affiliate_Marketers

How To make money clickbank

5/31/2013 8:26:08 AM #

designer schuhe günstig

Thanks for any help, man.

Here is my blog post;  designer schuhe günstig - Mumsnews.com/.../Increase-Your-Style-With-These-Fashion-Tips

designer schuhe günstig

5/31/2013 8:28:42 AM #

mouse click the next webpage

There is certainly obviously quite a bit of knowledge this was. I think that if some prominent points of functions.

my website ::  mouse click the next webpage - Www.clavio.de/.../Easy_SEO_Suggestions_To_Get_The_Internet_Site_To_The_Top_Level

mouse click the next webpage

5/31/2013 9:12:09 AM #

cheapest all inclusive

Hi, I identified your blog using the Google search engine and although the initially position, enable seems to be incredibly beneficial to me.

My page  cheapest all inclusive - teensnpa.com/members/wxyfrankl/activity/39450/

cheapest all inclusive

5/31/2013 2:00:31 PM #

zins kredit

Within the Main I do not post on blogs, but I would want to say that this place actually pushed me to accomplish so! definitely upstanding spot.

Here is my web site:  zins kredit - www.Joraweb.com/.../

zins kredit

5/31/2013 2:52:17 PM #

Terrence

Redirect post. Exactly where is all of the entropy from ... Smile

Feel free to surf to my webpage  Terrence - 50-87-72-80.Unifiedlayer.com/.../do-you-have-bad-credit-read-thro

Terrence

5/31/2013 3:31:40 PM #

mouse click the up coming website

Nice post!

Also visit my site  mouse click the up coming website - www.cubeclub.net/fixing-your-credit-rating-not-tough

mouse click the up coming website

5/31/2013 7:00:18 PM #

business ideas canada

It was occupying. You appear really experienced in your topic.

my blog post:  business ideas canada - onebigcoop.com/.../

business ideas canada

5/31/2013 7:18:57 PM #

Merle

Wow! thank you! I usually wanted to write in my website something like that. Can I take part of your place to my net log?

Also visit my web-site:  Merle - https://www.goviralmag.com/viewupdate.php?id=65154

Merle

6/1/2013 8:00:43 AM #

Wade

Is not generally the location of blogs, but I would also like that this post actually pushed me to become so! Pretty superior location.

Feel free to visit my blog ...  Wade - http://www.10birr.com/article/article.php?id=43124

Wade

6/1/2013 10:18:59 AM #

wohnungskredit vergleich

Hi. very good job. I didn't count on this on a Wednesday. This can be a crucial story. Thanks!

Take a look at my homepage  wohnungskredit vergleich - www.anti-politics.ws/.../

wohnungskredit vergleich

6/1/2013 11:49:36 AM #

sofortkredit online

Give thanks! Helped by the spot!

Look into my web page -  sofortkredit online - mivideocristiano.com/.../

sofortkredit online

6/1/2013 11:51:06 AM #

euro kredit

I usually do not respond to messages, but on this case. WoW! '

Also visit my web-site:  euro kredit - www.hyperionholdings.co.za/...sing-these-tips.html

euro kredit

6/1/2013 12:39:17 PM #

click through the next webpage

Not surprisingly, what may be the fantastic side and I'll explain jobs to backlink - Bookmark this website? Regards

My blog  click through the next webpage - Www.Bonafides.pl/.../index.php

click through the next webpage

6/1/2013 1:03:39 PM #

Lina

my God, I believed you had been going to chip in with some decisive insight in the finish there, not leave it with ‘we leave it to you to decide’.

my page  Lina - test.sportswriters.com/.../do-it-oneself-credit-score-restoration-efficient-way

Lina

6/1/2013 3:22:40 PM #

Samira

There is manifestly a whole lot to practical experience about this.  I believe you produced some excellent shows in Capabilities also.

my blog;  Samira - remember-me.es/.../

Samira

6/1/2013 4:09:40 PM #

Dawna

Super-Duper web page! I'm loving it!! Will come back once more - taking you feeds also, Thanks.

my web blog  Dawna - http://Office.Breeman.nl:8080/scuttle/bookmarks.php/ellajeffery

Dawna

6/1/2013 10:24:52 PM #

Evie

Howdy webmaster I like your post ….

my page:  Evie - Gofriendgo.com/.../

Evie

6/2/2013 9:42:27 AM #

Shenna

I sincerely enjoyed the post. Will link back for your website. Please spot more frequently if there is certainly time. Thank you!

Feel free to surf to my weblog;  Shenna - armsoc.org/.../Get-The-Credit-Rating-You-Need-Today-With

Shenna

6/2/2013 12:05:42 PM #

mini lave vaisselle

Greetings! Very useful advice within this article! It is the little changes which will make the largest changes. Many thanks for sharing!

Check out my homepage ::  mini lave vaisselle - http://www.minilavevaisselle.org/

mini lave vaisselle

6/2/2013 10:30:56 PM #

mouse click the following internet site

Maintain working, it is crucial work!

Here is my web blog ::  mouse click the following internet site - Socialtime.Uv.ro/.../

mouse click the following internet site

6/3/2013 3:03:05 AM #

wechsel private in gesetzliche krankenkasse

Continue to work, excellent job!

Visit my web blog;  wechsel private in gesetzliche krankenkasse - www.shopatuniverse.com/.../

wechsel private in gesetzliche krankenkasse

6/3/2013 3:25:34 AM #

die günstigsten kredite

General blogs, no space, but I'd also like to say that this place is really weird to be so! extremely nice post.

Also visit my blog ::  die günstigsten kredite - Www.gooddayfishing.com/.../do-you-have-bad-credit-read-through-these-pointers-now

die günstigsten kredite

6/3/2013 5:01:54 AM #

krankenversicherung private

Hi. critical operate. I did not count on this. This is an critical story. Gives Thanks!

Feel free to visit my blog  krankenversicherung private - forum.websound.ru/.../index.php

krankenversicherung private

6/3/2013 7:19:06 PM #

gesetzlich private krankenversicherung

I usually do not respond to messages, but on this case. WoWSmile

my page -  gesetzlich private krankenversicherung - hubertmccdxlkylklwzy.bravesites.com/.../intelligent-tips-for-medical-insurance

gesetzlich private krankenversicherung

6/4/2013 3:26:29 AM #

affiliate directory

The appropriate spot!

Feel free to visit my page:  affiliate directory - www.amigosdivebelize.com/index.php

affiliate directory

6/4/2013 6:20:00 AM #

Click Home

Thanks! incredibly valuable post!

Feel free to surf to my site ...  Click Home - www.szakibazis.com/.../Methods-For-Deciding-On-An-Exceptional

Click Home

6/4/2013 4:24:38 PM #

visit the next internet site

You produced some good tips there. I did a search around the topic and discovered most people will agree with your web log.

Review my web page;  visit the next internet site - www.mccowenmills.com/.../...ffiliate-marketing.htm

visit the next internet site

6/4/2013 4:39:26 PM #

private kv anonym

Super-Duper web site! I am loving it!! Will come back once more - finding you feeds also, Thanks.

My webpage  private kv anonym - www.reelswingers.com/

private kv anonym

6/4/2013 4:51:04 PM #

please click The up Coming post

thank you for your help!

Feel free to visit my web-site ::  please click The up Coming post - www.victoryinstitute.net/.../some-approaches-to-maximize-earnings-in-multi-level-advertising-and-marketing

please click The up Coming post

6/5/2013 8:16:30 AM #

best island to vacation

Excellent articles and prestigious of the web-site ....

My web page ::  best island to vacation - udelascienciasyelarte.ac.cr/index.php

best island to vacation