OLEDB Connection errors while writing to Excel 2007 (xlsx) files RRS feed

  • General discussion

  • Dear SQL Community,

    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

    • Changed type malasyam Tuesday, May 24, 2016 4:21 AM none responded
    Tuesday, May 17, 2016 3:17 AM

All replies

  • I solved this by recreating the package in SSDT2012. This is now working in both my local pc (dev), and the SQL Server.

    Thanks - Madhu

    Tuesday, June 14, 2016 1:42 AM