Excel and SSIS integration challenge error:Cannot expand named range

1

Error message:Error in Microsoft.SqlServer.Dts.Runtime.TaskHost/Excel Destination [22] : SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft Office Access Database Engine" Hresult: 0x80004005 Description: "Cannot expand named range.".

Error in Microsoft.SqlServer.Dts.Runtime.TaskHost/Excel Destination [22] : SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Excel Destination.Inputs[Excel Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "Excel Destination.Inputs[Excel Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Task: Trying to export SQL data to Multiple Excel spread sheets. Each state would be one sheet. 50 states would be 50 excel spread sheets within single excel file.

How i achieve this: 1. Script task: Checks for excel file existence, If it exists then deletes the file. 2. SQL task with excel connection manager(2007) would create excel file/sheet. I have 50 sql task in a container these tasks just create new sheets within the same file. Excel connection manager 2007 has expression which has Provider=Microsoft.ACE.OLEDB.12.0.

Expression:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +  @[User::DynExcelFilename] + ";Extended Properties=\"Excel 12.0 XML;HDR=YES\";"

@DynExcelFilename
"\\ABC_" + (DT_WSTR,4) YEAR( GETDATE()  )  + "_" +
 (DT_WSTR,2) MONTH( GETDATE()  )  + "_" +  (DT_WSTR,2) DAY( GETDATE()  ) + ".xlsx"

So dynamic file name every day. 3. Data flow task> OLE Source with SQL query & Excel destination connected to excel connection manager. I have 50 data flow task's pushing data to multiple spread sheets(since the file & multiple sheets already created in #2).. i use them here.

This used work perfectly fine earlier until recently it made server upgrade and added SQL 2012 & SSDT 2012 wrappers. Since then I get above error.

Any suggestion would be appreciated.

excel
ssis
sql-server-2012
ssis-2012
asked on Stack Overflow Apr 30, 2015 by user1810575 • edited Feb 10, 2016 by JodyT

2 Answers

0

I found the answer in another post. I was trying to write to the multiple sheets from the same Data Flow task. Creating a different Data Flow task for each sheet resolved the issue!

Creating SSIS Package in SSDT. Conditional Split in Data Flow Task Causes Package To Break

answered on Stack Overflow Feb 10, 2016 by user3735317 • edited May 23, 2017 by Community
0

I had the same issue due to another problem. I had to export an ordered count into excel, but the numbers kept saving as text. I found a solution, which consisted of including a dummy row with the right type of variables. The problem is that probably the dummy row somehow interferes with the ordered list. When I removed it, everything got fixed.

answered on Stack Overflow Jun 1, 2016 by GeorgiG

User contributions licensed under CC BY-SA 3.0