CODEWITHSUNDEEP
export-to-excelssis
G33kKahuna
G33kKahuna

Reputation: 1810

SSIS export to excel

Using SSMS I created an export a query to Excel and saved it as a DTSX package. Imported this DTSX package into a BIDS project to make it run hourly and export a excel file with timestamp appended to it, dynamic name using expressions. When I run from BIDS, package executes successfully and creates the excel file without a problem. I have enabled DelayedValidation flag. I can double click on the DTSX file, run the package and it creates the excel file without a problem. When I configure as a SQL Job, it fails with the below error

Any guidance is much appreciated 

Microsoft (R) SQL Server Execute Package Utility  Version 10.50.2500.0 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    
    Started:  2:53:03 PM  
    Error: 2012-09-21 14:53:05.54     
    Code: 0xC0202009     
    Source: Export MyExportQuery to Excel Destination - Query [73]     
    Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E37.  End Error  
    Error: 2012-09-21 14:53:05.54     
    Code: 0xC02020E8     
    Source: Export MyExportQuery to Excel Destination - Query [73]     
    Description: Opening a rowset for "Query" failed. Check that the object exists in the database.  End Error  
    Error: 2012-09-21 14:53:05.54     
    Code: 0xC004701A     
    Source: Export MyExportQuery to Excel SSIS.Pipeline     
    Description: component "Destination - Query" (73) failed the pre-execute phase and returned error code 0xC02020E8.  End Error  
    DTExec: The package execution returned DTSER_FAILURE (1).  
    Started:  2:53:03 PM  
    Finished: 2:53:05 PM  
    Elapsed:  2.282 seconds.  The package execution failed.  The step failed.

EDIT: SSIS is configured to run in 32 bit mode

Upvotes: 3

Views: 4768

Answers (2)

J.C. Gras
J.C. Gras

Reputation: 5442

When you configure a SQL Server Agent job with a job step type of SQL Server Integration Services Package, the job invokes the dtexec utility. However, the version of the dtexec utility that the job invokes depends on what versions of SQL Server and SQL Server Agent have been installed and are running on the 64-bit computer. [MSDN]

To run a package in 32-bit mode from a 64-bit version of SQL Server Agent, select Use 32 bit runtime on the Execution options tab of the New Job Step dialog box.

enter image description here

Upvotes: 0

specificityy
specificityy

Reputation: 580

Most of the problems arise due to these situations:

  • Data source connection or File access issue (when the user running the sql agent doesn't have the right permissions to the db or to the destination file).

  • Package protection level (pwd are sensitive data, and sometimes are not copied depending on the protection level).

  • 64bit issue (this a common one on XL exports. Since there's no Jet Driver for 64bit systems, when running on a 64bit OS you need to either use the 32bit DTEXEC in your sql job or set the "Use 32bit runtime" option, it's on the "Execution options" tab when you're creating a SSIS job step, check the image on link below for seeing it).

32Bit runtime option image

For information on this topic, have a look at this article, it has detailed information on situations when ssis packages run on BIDS but not on SQL jobs..

How do I troubleshoot SSIS packages failed execution in a SQL Agent job?

Hope this helps you..

Kind regards,

Upvotes: 3

Related Questions