CODEWITHSUNDEEP
ssisbidsoledbconnection
Madhu
Madhu

Reputation: 1

BIDS - OLEDB Connection errors in SSIS while writing to Excel 2007 (xlsx) files

For the past few days I'm running into an error, which is well known, but I can't understand what I need to do, even after reading so many different solutions. But please let me start with the task.

A predecessor created a business critical SSIS package using SQL Server Data Tools (2005) 4-5 years ago that basically reads a large table in the database and then segregates the data categorically and pumps the data into separate tables in the same database. At the end it reads the data from these segregated/categorised data tables and exports the data into respective Excel files in a Network drive in the same folder. All these tables have different data dictionaries. All these Excel files are 97-2003 format (.xls).

The Production server is SQL 2005 and Windows 2003. and a New Development environment is created with SQL Server 2012 and Windows 2012, where I need to migrate all the databases, SQL Jobs, SSIS packages. Majority of them completed and are running without issue. I left the complex SSIS packages to the last, so I can deliver something to business to test on.

Now my task is to upgrade the package to write into Excel 2007 xlsx files. No changes at the database level. So, I created OLE DB Connections for all the Excel files and the connections appear to work fine when clicked on Test Connection in Connection dialog. All these Excel files sit in the Dev SQL Server in the same folder (\DevServer\p$\SSIS_Jobs\Process_Data) as the SSIS package. I set the Extended Properties = Excel 12.0 XML in Connection manager. But when I run the package in the BIDS, I'm getting

"Failed to acquire connection "Excel07_Con1". Connection may not be configured or you may not have the right permissions on this connection."

The package is set to 32 bit mode and MSOffice installed is 32bit and installed the Microsoft Access Database Engine 2010 (32-bit) drivers. And the Dev Network drive has Full rights to EveryOne to ReadWrite.

Since this is the last step in the process, the whole job is failing because of this. I'm sure gone through lot of responses to similar questions. Any help would be highly appreciated.

Thanks - Madhu

Upvotes: 0

Views: 229

Answers (2)

Madhu
Madhu

Reputation: 1

Thanks for your responses, I've solved the issue by recreating the package from scratch in SSDT2012. Now the package is working. I suspect it could be the Excel drivers.

Thanks for your time again. - Madhu

Upvotes: 0

Dariusz Bielak
Dariusz Bielak

Reputation: 415

have you checked project properties? It might be the case that the project in BIDS the following property Runas64Bit is set to TRUE.

Upvotes: 1

Related Questions