Tuesday, June 24, 2014

component "Excel Destination" (40) failed validation and returned error code 0xC020801C

component "Excel Destination" (40) failed validation and returned error code 0xC020801C

machine - 64 bit
windows - win7
Sqlserver - 2008

I got below error while execute task from data flow tab. I am trying to read data from raw file (for this i use component - Raw File Source) after some sort process i was trying to write the data on excel file. which fails with below error.

SSIS package "Package.dtsx" starting.
Information: 0x4004300A at create ExcelFilefrom Raw File, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC020801C at create ExcelFilefrom Raw File, Excel Destination [40]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC00F9304.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at create ExcelFilefrom Raw File, SSIS.Pipeline: component "Excel Destination" (40) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at create ExcelFilefrom Raw File, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at create ExcelFilefrom Raw File: There were errors during task validation.
SSIS package "Package.dtsx" finished: Success.


where "create ExcelFilefrom Raw File" name of data flow.

Problem:-

If you have installed BIDS (Buisness intelligence Development Studio 64 bit package) on 64 bit machine, having connections that only support 32bit (like Excel, Access and old ODBC connections) you will get an error like above.

Solution:
The solution is to run your package in 32bit mode. This can be done within Visual Studio for debugging or within SQL Server Management Studio for scheduled packages.

This solution will also allow you to debug a Script Task, because that also doesn't work in 64bit mode.

step to be follow:
- open project
- right click on project name and select properties
- window will popup
- Look for "Debugging"
- Debug Options > "run64BitRuntime" set to "false"





After applying changes as above try to re-execute the dataflow task and successful outcome will be shown.
SSIS package "Package.dtsx" starting.

Information: 0x4004300A at create ExcelFilefrom Raw File, SSIS.Pipeline: Validation phase is beginning.
Warning: 0x80049304 at create ExcelFilefrom Raw File, SSIS.Pipeline: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available.  To resolve, run this package as an administrator, or on the system's console.
Information: 0x40043006 at create ExcelFilefrom Raw File, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at create ExcelFilefrom Raw File, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at create ExcelFilefrom Raw File, SSIS.Pipeline: Execute phase is beginning.
Information: 0x40043008 at create ExcelFilefrom Raw File, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x4004300B at create ExcelFilefrom Raw File, SSIS.Pipeline: "component "Excel Destination" (68)" wrote 538 rows.
Information: 0x40043009 at create ExcelFilefrom Raw File, SSIS.Pipeline: Cleanup phase is beginning.
SSIS package "Package.dtsx" finished: Success.

No comments:

Post a Comment

web stats