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.
Add Data Flow Task to the Control tab.
SSIS Data Transform Connection Setup
Go to the Data Flow tab and add an OLE DB Source and Right Click on it.
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.
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.
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.
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).
Select the newly created worksheet from the Drop Down box.
Click on the Mappings to view the Input and Destination columns. Click OK to exit.
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.
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.
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 File System Task
In the Control Flow tab, click and drag a File System Task over and right click to Edit.
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.
Make sure the Precedence Constraints (Green Arrows) are attached in sequence. Run the Package to verify that the run was successful.
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).
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.