SSIS export to Excel - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

SSIS export to Excel

Author

Topic

stranter
Starting Member

17 Posts

Posted - 2010-04-19 : 09:52:46

Hello,

I've developed an SSIS package to export a suite of reports (views) into Excel sheets.

I'm having real trouble getting this to work. I recently rebuilt the entire SSIS package, I also enclosed every field in every view in a CAST (to VARCHAR) statement to ensure there were no out-of-range errors (I was previously getting some of these).

I ren it for the first time today and it ran part of the way through. It complained about a particular component having un-synchronised metadata. So I fixed this and reran it And it failed at the first step with the following error:

SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Emergency Care, DTS.Pipeline: Validation phase is beginning.
Warning: 0x800470C8 at Emergency Care, ae_paed_lta [1484]: The external metadata column collection is out of synchronization with the data source columns. The column "attendance_number" needs to be updated in the external metadata column collection.
The column "investigation_1" needs to be updated in the external metadata column collection.
The column "investigation_2" needs to be updated in the external metadata column collection.
The column "treatment_1" needs to be updated in the external metadata column collection.
The column "treatment_2" needs to be updated in the external metadata column collection.
The column "ae_site" needs to be updated in the external metadata column collection.
The column "attendance_month" needs to be updated in the external metadata column collection.
The column "em_hrg" needs to be updated in the external metadata column collection.
Information: 0x40043006 at Emergency Care, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Emergency Care, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Emergency Care, DTS.Pipeline: Execute phase is beginning.
Error: 0xC0202009 at Emergency Care, ae_paed_nlta [1581]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
Error: 0xC0209029 at Emergency Care, ae_paed_nlta [1581]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Excel Destination Input" (1592)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Excel Destination Input" (1592)" 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.
Error: 0xC0047022 at Emergency Care: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "ae_paed_nlta" (1581) failed with error code 0xC0209029. 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.
Error: 0xC0047021 at Emergency Care: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread1" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047039 at Emergency Care: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread3" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
Error: 0xC0047039 at Emergency Care: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
Error: 0xC0047039 at Emergency Care: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread2" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
Error: 0xC0047021 at Emergency Care: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread3" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC02090F5 at Emergency Care, vw_ae_adult_lta [1630]: The component "vw_ae_adult_lta" (1630) was unable to process the data.
Error: 0xC0047021 at Emergency Care: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047021 at Emergency Care: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread2" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047038 at Emergency Care: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "vw_ae_adult_lta" (1630) returned error code 0xC02090F5. 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.
Error: 0xC0047021 at Emergency Care: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread2" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC02090F5 at Emergency Care, vw_ae_paed_nlta [1533]: The component "vw_ae_paed_nlta" (1533) was unable to process the data.
Error: 0xC0047038 at Emergency Care: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "vw_ae_paed_nlta" (1533) returned error code 0xC02090F5. 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.
Error: 0xC0047021 at Emergency Care: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread1" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC02090F5 at Emergency Care, vw_ae_paed_lta [1436]: The component "vw_ae_paed_lta" (1436) was unable to process the data.
Error: 0xC0047038 at Emergency Care: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "vw_ae_paed_lta" (1436) returned error code 0xC02090F5. 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.
Error: 0xC0047021 at Emergency Care: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC02090F5 at Emergency Care, vw_ae_adult_nlta [1727]: The component "vw_ae_adult_nlta" (1727) was unable to process the data.
Error: 0xC0047038 at Emergency Care: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "vw_ae_adult_nlta" (1727) returned error code 0xC02090F5. 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.
Error: 0xC0047021 at Emergency Care: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread3" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
Information: 0x40043008 at Emergency Care, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x40043009 at Emergency Care, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Emergency Care, DTS.Pipeline: "component "ae_paed_lta" (1484)" wrote 2248 rows.
Information: 0x4004300B at Emergency Care, DTS.Pipeline: "component "ae_paed_nlta" (1581)" wrote 0 rows.
Information: 0x4004300B at Emergency Care, DTS.Pipeline: "component "ae_adult_lta" (1678)" wrote 0 rows.
Information: 0x4004300B at Emergency Care, DTS.Pipeline: "component "ae_adult_nlta" (1775)" wrote 0 rows.
Task failed: Emergency Care
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (22) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.

This was previously happening and why I rebuilt the package. It always fails at this first step. Previously, prior to package rebuild, the package would fail at random steps - on one execution it would run and complete twenty steps, the next execution would complete fourteen steps and so on. Totally random. I'm out of ideas, can anyone help?

Edited to add:

Running
SQL Server: Microsoft SQL Server Standard Edition 9.00.4035.00
OS: Windows(R) Server 2003, Enterprise Edition, Version 5.2.3790 Service Pack 2 Build 3790

SSIS Package not running in 64-bit mode (Run64BitRuntime = False)

Thanks,

Stewart

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 10:54:31

it seems like you've changed the source query or table structure which caused metadata of package to burst. you need to open package and fix those metadata inconsistencies before you can continue.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stranter
Starting Member

17 Posts

Posted - 2010-04-19 : 11:09:00

Thanks for the reply.

But no, I promise you I haven't. The database has remained unchanged since I rebuilt the SSIS package.

But, just to be sure, I updated all the metadata for that data flow task (double click on the data reader source, click refresh. Then double the Excel destination and use the "Restore Invalid Column Reference Editor" to refresh the metadata) and reran - same error, different data flow task. If I rerun it, the data flow task on which the package errors will change.

Is it because the DataReader source is looking at a view and not a table?

Stewart

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 11:14:04

did you change view recently? or did you make any changes to underlying tables and left view unrefreshed?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stranter
Starting Member

17 Posts

Posted - 2010-04-19 : 11:17:47

Just created tables from the data in the views, refreshed the metadata and...same error. Well, same error, different step.

Stewart

stranter
Starting Member

17 Posts

Posted - 2010-04-19 : 11:18:26

The view hasn't changed at all.

Stewart

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 11:28:18

which is one you're using as source in ssis? table or view?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stranter
Starting Member

17 Posts

Posted - 2010-04-19 : 11:38:27

View...

Stewart

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 11:48:45

then it shouldnt be a problem. b/w were you using select * inside view?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stranter
Starting Member

17 Posts

Posted - 2010-04-20 : 03:57:29

No, the view is a select of various fields and some aggregate functions with some joins. The DataReader tasks are SELECT * FROM <view name>. It's very bizarre behaviour indeed.

Stewart

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-20 : 09:43:53

replace * with actual column names and try

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stranter
Starting Member

17 Posts

Posted - 2010-04-20 : 10:53:16

That's fixed it!

Thanks ever so much!

I have to say, this is very illogical and surely "buggy" behaviour to not work with "SELECT *".

Oh well, it's sorted and lesson learned - thanks again.

Stewart

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-20 : 10:58:04

nope. select * will screw up metadata if any changes in structure of underlying tables occur and ssis will throw error in those cases

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources