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

3
votes

I'm trying to export a table from SQL SERVER 2012 to An Excel 2007 Workbook.

The simple task from OLE DB Source -> Excel Destination works perfectly.

I have a field called [POD Assignment], I want to export to one sheet in the workbook if [POD Assignment] is equal to "UNKNOWN", and to another sheet if it's not.

Link OLE DB Source to Conditional Split.

I have an output named "Unknowns" where the condition is "[POD Assignment] == "UNKNOWN"". I link the the output "Unknowns" to an Excel Destination.

At this point, when I run the package, everything works as it should. The rows were exported correctly to the workbook, and the count was right. As seen here:

[] http://imgur.com/iKcJCfY,rHnW8ax#0

Now I connect the conditional split to a second excel destination where the output is the Default Output of the conditional split. The excel second destination uses the same exact Excel Connection as the first. And all the data is supposed to exported to a different sheet in the same workbook.

Running this fails. As seen here:

[]: http://imgur.com/iKcJCfY,rHnW8ax#1

The task prematurely aborts on error.

I receive the following error codes:

[Excel Destination 1 [101]] Error: 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.".

[Excel Destination 1 [101]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Excel Destination 1.Inputs[Excel Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "Excel Destination 1.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.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Excel Destination 1" (101) failed with error code 0xC0209029 while processing input "Excel Destination Input" (112). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. [OLE DB Source [188]] Error: Setting the end of rowset for the buffer failed with error code 0xC0047020. [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on OLE DB Source returned error code 0xC0209017. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

Any help would be appreciated. This is driving me crazy. I don't understand why using one destination for the conditional split works perfectly but when I add a second destination everything fails.

4
excelssissql-server-data-tools
I not sure that you can write to two different sheets in the same workbook at the same time with OLE DB, can you? - RBarryYoung
That was my thought also. The error that matters is 'Cannot expand named range". Why don't you try writing to a different sheet and see if it stops the error, just to troubleshoot. - Nick.McDermaid

4 Answers

3
votes

I had this exact error message. I tried the suggestions on the other answers and none worked.

My problem turned out to be that I was trying to write to different tabs at the same time.

Instead of writing to every tab in the same dataflow task I put each tab into it's own dataflow so that each write would happen one at a time. This fixed the problem.

2
votes

Check that your Excel destination is empty of data (except the header row if applicable).

I had the Hresult: 0x80004005 Description: "Cannot expand named range." error and my issue wasn't immediately obvious:

  • I had an existing Union All of two Excel files into a single Excel file
  • The first column from one source was a number. The other source was alphanumeric.
  • I changed the source's first column to be from a new Derived Column and it became alphanumeric.
  • (I neglected to clean-up the destination - it still had existing data, where the first few rows of data were numeric)
  • The Union All worked, but the destination failed (the source files also showed a red X)

I fixed it by "truncating" the excel file, back to just the header row. (And I could recreate the error by setting a few sample rows in Column A back to numeric). I think I'll now use an empty Excel file template and overwrite the destination first (unless there's an easier way?)

1
votes

You cannot write to different sheets from the same workbook at the same time because the excel file is write-locked when used as a destination

0
votes

I had the similar problem with you except it failed on a random worksheet (task). After spending more than a week to try all the possible solutions without any success, I found out that I had Microsoft Office 2007 installed in my computer (32 bit) while everything else is in 64-bit. I deployed and tested on server with everything in 64 bit and it works fine.

I am not sure if it is your case but hope it helps you.