Solved

ConnectionManagerType = Excel with dynamic connection string

Posted on 2012-03-26
4
547 Views
Last Modified: 2012-06-27
System environment Windows 7 Pro, 64 bit OS

I want to import data in xls file into a table in my SSIS package. I created a ssis ConnectionManagerType = Excel (WC_Crosswalk) and gave a dynamic connection string to connect to the xls file. The connection string is copied below:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ @[User::CrosswalkPath] +";Extended Properties=\"EXCEL 8.0;HDR=YES\";"

if the value of the CrosswalkPath = "S:\\Offload\\WC\\From Client\\Images\\WCNYE1.xls"
as copied from the watch window
I understand that the connection string would be:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=S:\Offload\\WC\From Client\Images\WCNYE1.xls;Extended Properties="EXCEL 8.0;HDR=YES";

When I run this package, my data flow task fails with the following error message:
[WellcareExcelsheet [89]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "WC_Crosswalk" failed with error code 0xC00F9304.  "


Where did I go wrong?
0
Comment
Question by:patd1
  • 3
4 Comments
 
LVL 16

Expert Comment

by:DcpKing
ID: 37769166
I've never had to set the connection string explicitly: try setting the Excel file name in the expression list  as @[User::CrosswalkPath] - the connection string usually takes care of itself after that.
0
 

Author Comment

by:patd1
ID: 37769197
I also tried setting the expression property "ExcelFilePath" to [User::CrosswalkPath] . I still get the same error.
0
 

Accepted Solution

by:
patd1 earned 0 total points
ID: 37769240
I changed the Run64BitRuntime value to False. Now it works.
http://www.bisql.net/2010/10/how-to-enable-32-bit-runtime-mode-in-ssis/
0
 

Author Closing Comment

by:patd1
ID: 37790470
Easy solution found using google.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.

786 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