Solved

ConnectionManagerType = Excel with dynamic connection string

Posted on 2012-03-26
4
549 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

751 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