Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ConnectionManagerType = Excel with dynamic connection string

Posted on 2012-03-26
4
Medium Priority
?
553 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
[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
  • 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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

704 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