Excel and SSIS integration challenge error:Cannot expand named range

Question

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.

Answer 1

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

Answer 2

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.