SSIS create excel file

Today let as talk about an old elementary subject of SSIS create excel file. I see most peoples still struggle with sending output to Excel ins SSIS so why not say few things about that. Let us begin by creating new SSIS package and create connections to our local data database and an excel file.
Right click on the connection manager and select New OLE DB Connection…

Specify the server for now I am working on the localhost so I simply typed “.” Then select you Authentication type if you are using SQL Authentication type your user name and password. Select the database you want to connect for now let us work with AdventureWorks and click on OK.

Before we create connection the Excel let us create a template file we could connect to from SSIS

Next let us create connection to Excel by right clicking on Connection Managers and select New Connecton…

On the new dialog select EXCEL then click on Add…

Now select the template file we created before and click on OK.

Now we have our connection next drag Data Flow Task to your work area and double click on it our click on it and select Data Flow tab at the top of your work area

From your toolbox drag OLE DB Source from Other Sources section. The red stop sign indicate we have to make few more changes to it before we could actually use it for our ETL and let us double click on it and make changes

Next you will get the next dialog box select the OLE DB connection we created earlier. Select table or view for Data access mode then select one of your table or if you want to write your own query use select SQL Command for your data access mode. Let us export Person table for our example then if you don’t want all columns click on columns on the left

Then uncheck the columns you don’t want to export then click on OK

Now after we properly make changes the red stop sign is gone next let us add our destination to the work area

Next from Other Destinations section of SSIS toolbox drag and drop Excel Destination to your work area

When you click on OLE DB Source you will see a blue arrow drag it and drop it on Excel Destination

Make sure the previous connection to the excel file is selected for Excel Connection Manager since we don’t have a matching table or worksheet on our template file let us create that by clicking on New…

Then you will have a create table SQL Statement will appear change work sheet name to appropriate name in our case from ‘Excel Destination’ to ‘Person’ then click on OK .

Now from the list of Excel tables or works sheets select Person$ then click on Mappings on the left

On the mapping make sure you have the correct mapping and Click on OK.

Now we have properly configured our SSIS package and ready to execute.

Let’s execute it by pressing F5

Oops what happen? We have everything ready and yet the package failed. The best ways to understand what went wrong is click on the Progress tab and check the log

We have the error message as:
[Excel Destination [2]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

What we are missing is an appropriate access engine that will work with our ETL. Next let us download the appropriate access engine runtime for the Excel version we have by following this linkhttp://www.microsoft.com/en-us/download/details.aspx?id=13255

Now that we install our Access runtime plugin let us try it again

It is possible your this the above step is successful for you but if it fails just like mine you have to change your package runtime from 64bit to 32. Now let us do that by going to project menu then click on project Properties…

Click on Debugging Configuration Properties then change the Run64BitRuntime to False and click on OK

Perfect we are able to transfer 19,972 records from AdventureWorks to Excel.