Run SSIS package. Works in BIDS, not on server.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Run SSIS package. Works in BIDS, not on...

Run SSIS package. Works in BIDS, not on server.

Rate Topic

Display Mode

Topic Options

Author

Message

Ginga

Posted Wednesday, February 13, 2013 8:38 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:38 AM
Points: 6, Visits: 87

Hello.

I have an issue that has consumed a lot of my time to date, so I've turned to the SSC community.

I have a dtsx package that quite simply uses a DB table as a data source, does a quick conversion of ASCII strings to unicode strings and outputs to an Excel document on a local share.

I am able to run this package from BIDS and I have no issues at all, however when attempting to execute the package from the server, I get the following error stack:

Error: 2013-02-13 15:12:00.64
Code: 0xC0202009
Source: Extract Workstation data for Excel document Excel Destination [1020]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80040E37.
End Error
Error: 2013-02-13 15:12:00.65
Code: 0xC02020E8
Source: Extract Workstation data for Excel document Excel Destination [1020]
Description: Opening a rowset for "Excel Destination" failed. Check that the
object exists in the database.
End Error
Error: 2013-02-13 15:12:00.68
Code: 0xC004706B
Source: Extract Workstation data for Excel document DTS.Pipeline
Description: "component "Excel Destination" (1020)" failed validation and ret
urned validation status "VS_ISBROKEN".
End Error
Progress: 2013-02-13 15:12:00.68
Source: Extract Workstation data for Excel document
Validating: 100% complete
End Progress
Error: 2013-02-13 15:12:00.69
Code: 0xC004700C
Source: Extract Workstation data for Excel document DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2013-02-13 15:12:00.69
Code: 0xC0024107
Source: Extract Workstation data for Excel document
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 15:11:59
Finished: 15:12:00
Elapsed: 1.467 seconds

I feel I must add that I am running a 32bit version of SQL Server 2005 on a 64bit edition of Windows Server 2008 R2. I attempted to run the package using the 32bit version of dtexec. SQL Server is at version 9.0.5069 (SP4).

If anyone could offer some advice, I'd be very grateful. I have a funny feeling that my issue may be 64/32 bit related.

Post #1419572

opc.three

Posted Wednesday, February 13, 2013 3:37 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 6,696, Visits: 11,713

The info you provided about bitness sounds OK.

From the error I am wondering about the existence of the destination Excel file. Does it exist in the prod environment at the destination? How is it being created at runtime? SSIS will not create an Excel file on the fly by default. Maybe you have the file sitting there on your machine and it's working for testing but there is no provision to stage a file in prod to accept the data during each execution?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1419761

Ginga

Posted Thursday, February 14, 2013 2:04 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:38 AM
Points: 6, Visits: 87

Hi opc.three and thanks for your reply.

The file won't exist at the point this package is ran. The job that will eventually invoke this package, deletes the old file to make way for the new. I will try running this job in isolation with the file created and see what results I get, however when running the package from BIDS, the file is created without issue.

I will post back with the results.

Thanks again.

Post #1419876

Ginga

Posted Thursday, February 14, 2013 7:46 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:38 AM
Points: 6, Visits: 87

Okay, placing the file there does seem to have fixed that issue, thanks for the suggestion.

Post #1420071

opc.three

Posted Thursday, February 14, 2013 8:01 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 6,696, Visits: 11,713

You're welcome. Thanks for the feedback.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1420091

« Prev Topic | Next Topic »

Permissions