July 16, 2007 at 9:06 am
I am just starting to explore SSIS as a replacement for DTS. My project involved importing from Excel into a table. Simple, eh?
Well, the work went fine when I used the Wizard. But I wanted to learn to handle the studio. I loaded an Excel connection manager and pointed it to my spreadsheet file. I loaded an OLEDB manager and pointed it to the table I had created. I added the Data Flow source (Excel) and the Data Flow Destination (OLEDB), mapped the columns and looked at the Preview. The data was there.
But when I run the program, I get a Validation error: Class not registered, and the AcquireConnectionmethod call to the Excel Connection Manager failed.
When I attempt the same task using the Wizard it works fine. And when I save the Wizard's package and look at it, I don't see the difference with my hand-built package.
Any ideas?
Thx, Elliott
July 16, 2007 at 9:03 pm
Just out of interest, is this a 64-bit install of SQL (and in particular, SSIS)? If so, you may want to change your (project) settings to execute the package 'not' under the 64-bit runtime as apparently there isn't an Excel driver for the config (go figure).
Cheers,
Steve.
July 16, 2007 at 9:57 pm
Steve--
Good catch. We have 64 bit Enterprise Edition.
So what do I do to cause the package to execute under some other environment?
Halfway there,
Elliott
July 16, 2007 at 10:11 pm
right click the project within your BI Dev studio (in the solution explorer pane). Select properties. I think under the debug (there's not too many things to look thru if that's not exactly it), there's a settings along the lines of 'Run in 64-bit runtime' or thereabouts. Change this from yes to no. I think you'll need to look at/manage the deployment of the same package to the actuall SSIS service if you deploy it in Production to that 64-bit environ. I would guess there's a similar setting for when it's loaded up in to the service but to be honest have only ever used the setting within the Dev Env.
Cheers,
Steve.
July 17, 2007 at 8:41 am
I will be back in the office on Wednesday. I'll try to change the run-time environment and will post the results then.
Thanks again,
Elliott
July 18, 2007 at 10:05 am
Yes, taking the run-time environment out of 64 bit immediately fixed the problem.
As Steve suggested, in Design time right-click on the Project (in Solution Explorer) to expose Properties. Then under Debug Options, set Run64BitRuntime to False.
Good tip if anyone is having trouble building a solution that accesses Excel.
Elliott
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply