Export Data to Excel using SQL Server Integration Services SSIS Package

In this SQL Server tutorial, I want to show how SQL developers can create SQL Server SSIS package for sql data export to Excel file and deploy SSIS package to SQL Server Integration Services.
To save a SQL Server task as an SQL Server Integration Services package enables sql professionals to reuse SSIS package later. Saving SSIS package enables to schedule SSIS package for periodically execution of the SQL Server task for SQL developers and administrators.

In previous SQL tutorial, we learn how to sql export to Excel from SQL Server
I want to continue from the above tsql tutorial where sql developers and DBA's select among the methods "Run immediately" and "Save SSIS Package".
This SSIS tutorial deals with the second option where sql data export to Excel task will be saved as SQL Server Integration Services (SSIS) package.

Within the SQL Server Import and Export Wizard mark Save as SSIS package.
Choose SQL Server to deploy SSIS package to SQL Server 2008 Integration Services instance instead of to SSIS package store in file system.
I set the SSIS package protection level to "Do not save sensitive data" not to deal with SSIS package store with password, etc.

In this step of the SQL Server Import and Export Wizard, developers can define the name of the SSIS package, provide a short descriptive information about the ssis package.
As seen in the below task wizard screen, for the SSIS package deployment the target is selected as SQL Server Integration Services instance.
I selected my local Integration Services server name as the target server name.

The later steps is just as in the previous SQL export task tutorial, normally sql developers and SQL Server administrators will be able to complete SSIS package creation successfully. But it is also possible to get the following Integration Services error.

Could not connect destination component.

Error 0xc0202009: Destination - Department [28]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
Error 0xc02020e8: Destination - Department [28]: Opening a rowset for "Department" failed. Check that the object exists in the database.  (SQL Server Import and Export Wizard)

===================================

Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
Program Location:

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
at Microsoft.SqlServer.Dts.DtsWizard.Execute.GenerateDestinationColumns(TransformInfo ti, IDTSDesigntimeComponent100 comp)
at Microsoft.SqlServer.Dts.DtsWizard.Execute.SetDestConnection(Exception& ex, TransformInfo ti)

In fact the solution for this SSIS package creation error is very simple.
In the error text it is stated the error is related with destination. So in this ssis package the destionation Excel file is causing the error.
If we go deeper in the integration services error, the target Department table in the SSIS package is causing the error.
Basically, the sql engine can not open a rowset for target table within the SSIS package because it does not exist in the target Excel file. We can reach this result from the error message "Check that the object exists in the database"

If we go a few steps back in the SQL Server Data Import and Export wizard, open the Column Mappings dialog screen
Be sure that the "Create destination table" option is selected.
This option will create the destination table, for exporting data to Excel case, the spreadsheet will be created each time the ssis package task is executed.
You might also think to mark the "Drop and re-create destionation table" checkbox.

After you complete the above column mappings settings on the export task, you can now create ssis package without any problem.

Also the ssis packape deployment will be done automatically according to the connection settings you have set before.
Please remember that I chosed to use Windows Authentication method to deploy SSIS package to SQL Server 2008 R2 Integration Services instance.
SQL Server administrators can also prefer to use SQL Server Authentication for SSIS package deployment by defining an sql user name and password instead of choosing Windows Authentication method.

In order to execute SSIS package later, or to run SSIS package, sql developers can open SQL Server Management studio (SSMS)
Then connect to related SQL Server Integration Services instance where the ssis package deployment is done.
Drill through the following nodes :
Integration Services > Stored Packages > MSDB
The SSIS package you deploy is listed under MSDB node.

If you want to see SSIS package properties, right click on the ssis package name.
Choose "Reports > Standard Reports > General
This menu selections on the context menu of the package will display SSIS package properties as seen in the above screenshot.
Package name, package creation date, package id, package data size, version major, version minor, version build number, version comments and SSIS package description are the properties that are displayed on this report.

How to Execute SSIS Package within Integration Services

In order to execute ssis package from SQL Server Integration Services, connect to target SQL Integration Services instance using SQL Server Management Studio. Then find the target SSIS package under MSDB node where we store ssis package.
Right click on the SSIS package and select Run Package from the context menu to execute ssis package directly from SQL Server Integration Services.