Programming Samples

Click here to go to:

Excel VBA
MS Access
T-SQL
SSIS
SSRS
Crystal Reports
C# Code
ASP .NET Code
Oracle PL/SQL
Database Diagramming

Back to Home Page

SSIS Data Transform Task - SQL to Excel File

SSIS Data Transform Task Package

Using SSIS, this tutorial will show how to create a Package that will push data from a view on a SQL Server to an Excel file. One of the topics covered is how to overcome the issue of "appending" unwanted rows to the Excel file as the task will be required to be performed repeatedly.

SSIS Project SQL Server to Excel Data Transform

To begin, opening Visual Studio, select a new BIDS project.

SSIS Open

Add Data Flow Task to the Control tab.

SSIS Data Transform Connection Setup

SSIS Data Flow

Go to the Data Flow tab and add an OLE DB Source and Right Click on it.

SSIS OLE DB

The OLE DB Source Editor will open. A New Connection will need to be added. Select the SQL server that has the AdventureWorks database. This tutorial uses the existing view Sales.SalesPerson.


SSIS Data Connection

Add an Excel Destination to the Data Flow tab. Hover over the OLE DB Source and pull the green arrow to connect it to the Excel Destination. Right Click on the Excel Destination and select Edit.

SSIS Excel Source

The Excel Source Editor will open. Click on the New button to set a new connection and type a new File Path when the Excel Connection Manager opens. Click OK to return to the Excel Destination Editor.

SSIS Excel Edit

Click on the New button next to the "Name of the Excel Sheet" dropdown box. The Create Table pop up box will appear with a SQL statement to create a new table matching all of the columns from the OLE DB Source in the Excel Workbook. Change the name from Excel Destination to another name and remove the ` marks from the field names and eliminate any white space from the field names ( or use [] (brackets).

SSIS Create Table

Select the newly created worksheet from the Drop Down box.

SSIS New Sheet

Click on the Mappings to view the Input and Destination columns. Click OK to exit.

SSIS Match Columns

SSIS Execute SQL Task to Create Table

Now that the Data Flow Task has been created, there are 2 additional tasks that need to be added to prevent the file from simply being appended with new data (and never removing the old data). The 1st is an Execute SQL Task to recreate the Excel Destination table, the 2nd is a File System Task to "delete" the file.

Click on the Control Flow tab and click and drag an Execute SQL Task from the Control Flow Items.

SSIS Execute SQL Task

Go to the DataFlow tab and Right Click on the Excel Destination and Edit. Click New next to the Name of the Excel Sheet to get the Create Table box to pop up. Highlight and select the contents and use CTL + C (to Copy). Click Cancel out of the SQL box and Cancel out of the excel Destination Editor to return to the Control Tab. Right click on the Execute SQL Task and Edit. Click on the SQL Task ellipsis button and use CTL + V (to Paste) the contents into the box. Rename to the table name you used in the Excel Destination Editor, if necessary. The information should match the columns in the Excel file, if not exact, remove tick marks and make sure the field names match the original names used in the Excel file.

SSIS Create Table

Click the drop down in the Connection Type box to select Excel. Click the Connection box to select the Connection Manager and click OK to return to the Control Tab. SSIS Execute SQL Task

SSIS File System Task

In the Control Flow tab, click and drag a File System Task over and right click to Edit.

SSIS File System Task

Click on New Connection in the SourceConnection drop down to select the Excel file that is used in the Excel Destination in the Data Flow task. Select Delete File from the Operation drop down. Click OK to close.

SSIS Delete File

Make sure the Precedence Constraints (Green Arrows) are attached in sequence. Run the Package to verify that the run was successful.

SSIS Package Debug

Open the file to verify the data was transferred. Close the file and run the SSIS Package again to verify that data was not Appended to the file (as would happen if the File System Task to Delete the file were omitted from this package).

SSIS Excel File

By using a Data Flow task with an OLE DB Source and Excel Destination, an Execute SQL Task to Create the table and a File System Task to delete the Excel File, fresh data can be transformed each and every time.