Solved

Errors when pulling data from MS Access into Sql Server 2005 database using Visual Studio

Posted on 2008-10-03
3
554 Views
Last Modified: 2013-11-30
I'm working on a 64 bit server and have tried setting up a package to pull in data from an access database into sql server 2005.  I set up my source connection as  oledb and the access database file name and path is in the database file name box.  When I test the source connection it works perfectly.  Same with the destination.  The data flow task has the source and the destination - I can see the columns and the mapping works fine.  When I execute the task the package fails with the following error at the source.


Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Any ideas?  Thanks as always for your help.

0
Comment
Question by:rsmuckles
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22638468
in 64 bits machines there is always some strange things happening...
your package is not making the connection to the source... do you have ODBC connections?
Try to execute the SSIS package as 32 bits... and check if the error still ocurr...
Regards!!
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 22639483
In fact, would definitely be running SSIS as 32-bit...

Well, possibly the above opener was a bit strong... It really depends a bit on some of the features, but until the upgrades (and keep a watch), then 32-bit is probably a lot more reliable as a general experience. For a general comment, see : http://msdn.microsoft.com/en-us/library/ms141766(SQL.90).aspx

Now, if what you are doing is via BI studio, then there is a small extra problem in so much as BI studio will not support 32-bit binaries on a 64 bit machine... see: http://ssis.wik.is/64-bit_Story

When testing and running using the Execute Package Utility (dtexecui.exe) it is in fact 32 bit, but dtexec has both the 32bit and 64bit. Either that, or, there is also the difference in privilege / profile and security between SSMS and SQl Server Agent and agentservices - so may need to check thos profiles. see: http://msdn.microsoft.com/en-us/library/ms178142(SQL.90).aspx


0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22639490
And here is a great comment from Brian Nights blog (MVP) - see some of the comments that follow as well...
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question