Importing data from table to excel file Errors

Question

I am doing transferring data into a formatted excel sheet.My destination in data flow task is excel when i tried to execute data flow task i am getting below errors i tried to change the settings 64bit to false. Can some one help where i am going wrong.

    [Excel Destination [301]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
[Excel Destination [301]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "Excel Destination Input" (312)"
 failed because error code 0xC020907B occurred, and the error row disposition on "input "Excel Destination Input" (312)" 
 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" 
(301) failed with error code 0xC0209029 while processing input "Excel Destination Input" (312). 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.

Are you sure that the data types coming from Excel are the same as SSIS expects them to be? — Gidil
– Gidil, Commented Jul 10, 2014 at 19:34

Manoj · Accepted Answer · 2014-07-19 13:41:36Z

2

Try This

Get Your Data in DataTable and then use this function

 public void ExportToExcel_AsXlsFile(DataTable dt, string file_name)
        {
            var grid = new GridView();
            grid.DataSource = dt;
            grid.DataBind();
            Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment; filename='" + file_name + "'.xls");
            Response.ContentType = "application/ms-excel";
            Response.Charset = "";
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            grid.RenderControl(htw);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
        }

answered Jul 19, 2014 at 13:41

Manoj

5,0913 gold badges34 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user3588007 · Accepted Answer · 2014-07-14 10:39:33Z

Reason for error:

When you create a package on your local machine and create a connection in it, you have the option to save the password of that connection. However by default it encrypts this password so that in can only be decrypted when you' re running the package on the same machine, with the same account. This holds good only if the Connection Manager uses SQL Authentication or connects to a Database which does not support Windows Integrated Authentication (e.g. Oracle). So, in the above scenario, if the package is deployed to a Remote Sql Server, it fails with the "Login failed.." error as it is not able to decrypt the password. (Note: It runs fine if deployed in the local Sql Server)

Resolution: To correct the problem, you should opt between either of the three options:

1. Change all the connection managers in the package to use windows authentication. Note: This is not an option while communicating with third party datasources which does not support windows authentication like Oracle.

2. Encrypt the package with "EncryptSensitiveWithPassword" or "EncryptAllWithPassword" and provide a package password each time User wants to edit/manipulate the package.

3. Create a configuration file to provide the connection information during Package runtime.

Reference link: http://technet.microsoft.com/en-us/library/ms140213.aspx**

Collectives™ on Stack Overflow

Importing data from table to excel file Errors

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related