Transferring data to a Microsoft Excel file
This chapter, excerpted from the book "Beginners Guide to SQL Server Integration Services Using Visual Studio 2005" by Jayaram Krishnaswamy, shows you how to create an SSIS package to transfer data from a table in a SQL Server 2005 database to a Microsoft Excel spreadsheet.
You will also learn how to use a Character Map Data Transformation. In the hands-on exercise, you will be transferring data retrieved from a SQL 2005 table to MS Excel 2003 spreadsheet file. You will be using a Data Flow Task consisting of a source connected to a SQL 2005 Server-based connection manager, and an Excel Destination connected to an Excel connection manager.
In order to follow these steps, you will need a source and a destination: the source data is extracted from the MyNorthwind database (just a renamed version of the Northwind database) on the SQL Server 2005 Server, and the destination is loading this to an MS Excel 2003 spreadsheet file on your hard drive. You also need to establish a path connecting them. In addition, you will also interpose a Character Map Data Flow Task that will convert the text in one of the data fields, so that all characters in that column are capitalized after the transformation.
Click below to listen to a podcast of author Jayaram Krishnaswamy discussing the process of transferring SQL Server data to a Microsoft Excel spreadsheet file, and why he chose to include this chapter in his book. (Time: 8:57)
TRANSFER SQL SERVER DATA TO EXCEL FILE
Step 1: Create a SQL Server BI project and add a Data Flow Task
In this section, you will be creating a Business Intelligence project and changing the name of the default package object. Since it is data-related, you will be adding a Data Flow Task. You will also be adding a DataReader component to the data flow.
- Create a business intelligence project Ch 5 as described in Chapter 2 or Chapter 3.
- Change the default name of package from Package.dtsx to TableToXls.dtsx.
- Drag and drop a Data Flow Task from the Toolbox onto the Control Flow page.
- Click open the Data Flow tab, which displays the Data Flow page.
Now, you will be able to access the Data Flow Items of the Toolbox consisting of Data Flow Sources, Data Flow Destinations, and Data Flow Transformations (refer to Chapter 1).
- Drag and drop a DataReader Source from the Data Flow Sources group onto the Data Flow page.
Step 2: Configure the DataReader's Connection Manager
Configuring the DataReader source that connects to the local SQL Server 2005 has been described in earlier chapters. Here, only a couple of the images relevant to this chapter will be shown.
- Right-click inside the Connection Managers page below the Canvas, and from the drop-down choose New ADO.Net Connection….
If you are continuing with this chapter after Chapter 4, you will see the Configure ADO.NET Connection Manager screen displaying the previously configured connection manager. If you need to create a new one, follow the steps shown in the previous chapter.
- Click on the OK button in the Configure ADO.NET Connection Manager window.
A connection manager, Localhost.MyNorthwind.sa, will be added to the Connection Manager's page.
Step 3: Set up the DataReader Source for SQL Server data
- Right-click the DataReader Source, in the drop-down menu.
- Choose the Edit… menu item in the drop-down menu.
This opens the Advanced Editor for DataReader Source. At first, you need to indicate a connection manager that the DataReader can use.
- In the Advanced Editor for DataReader Source that gets displayed click on the Connection Managers tab.
- Click on an empty area (in grey) below the list heading, Connection Manager.
Here, you will see the connection manager that you added in step 1.
- Choose this Connection Manager.
- Next, click on the Component Properties tab to open the properties of the DataReader component.
Here, you will notice that this requires an SQLCommand (the only empty field now).
- Click on the ellipsis button along its side to display a text editor where you can type in your SQLCommand.
You may also directly type-in the SQL Command:
SELECT CustomerID, CompanyName, Address, City, PostalCode
- Click on the Refresh button.
This query will allow the DataReader to read the data from the five columns. A sample of the table data is shown in the following screenshot, taken from SQL Server 2005 Management Studio. If you recall, these were the same columns that were used in the previous chapter as well.
- Click on the Column Mappings tab.
This will open the Column Mappings page showing the columns that are the output of the DataReader.
In the last tab on this editor, Input and Output properties, you can add/remove items from the External Columns, the Output Columns and the DataReader Error output. For this tutorial, no modifications are made.
- Click on the OK button in the above window.
This completes the configuration of the DataReader which brings five columns from the SQL 2005 Server.
Step 4: Install a Character Map for SQL Server data transformation
The Character Map transformation is described in Chapter 1, but here you will be experimenting with this transformation. The transformation manipulates the text string that is coming to it and outputs the manipulated string. For example, in the screenshot we have just seen above, the CompanyName has mixed case. Using this transformation, we will capitalize all the characters that appear in the CompanyName column before it is written to an Excel File—Alfreds Futterkiste will become ALFREDS FUTTERKISTE, etc.
- Drag and drop a Character Map data flow item from the Data Flow Transformations Group in the Toolbox onto the Data Flow page of the Canvas.
- Right-click on the DataReader Source and from the drop-down click on the Add Path menu item.
- From the displayed window, Data Flow, choose Character Map for the To: field as shown.
- Click OK in the above window, and the following window is displayed.
Here, you need to indicate the output from this component, from the drop-down shown.
- Choose DataReader Output Source from the drop-down.
At present, you will not be dealing with any errors in this tutorial. When you choose the above option, the OK button gets enabled.
- Click on the OK button in the above window.
This establishes the path from the Data Reader Source to the Character Map data flow component. The path is established but it still needs configuration.
- Right-click the Character Map component and from the drop-down menu choose, Edit.
This opens the Character Map Editor, as shown in the following screenshot. Place a check mark for the Company Name column.
If the default as shown in the above were to be chosen, then an extra column will be added to the output. We choose the option In-place change from the drop-down.
- Click on the cell, New Column, under Destination in the above window. From the drop-down choose, In-place change.
- Click just below the Operation list-header and from the drop-down list choose Upper Case as shown in the following screenshot.
The output alias remains the same as it is an in-line change.
- Click on the button OK in the pick-up list and to the OK button in the Character Map Transformation Editor.
This completes the Character Map configuration.
Step 5: Add an Excel destination and create path to Character Map
In this step, we will add an Excel Destination. We will then establish a path from the Character Map to Excel Destination.
- Add an Excel Destination component from the Data Flow Destinations group in the toolbox to the Data Flow page.
This can be accomplished either by double-clicking the component in the Toolbox or a drag-and-drop operation.
- Right-click Character Map and from the drop-down menu choose Add Path.
This opens up the window, Data Flow, which allows you to establish a data flow path, and displays the "From:" location as Character Map.
- Click on the drop-down along "To:", which shows both the Excel Destination as well as the DataReader Source.
- Choose the Excel Destination and click OK to the screen.
This opens up the Input Output Selection window that shows the available output and input windows. The Output window is empty whereas the input shows Excel Destination Input. The path should connect from the Character Map to the Excel Destination Input.
- Choose the above options and click on the OK button on this screen.
You will see a green line connecting the Character Map Data Flow Component to the Excel Destination, as shown in the next screenshot. Alternately, the process of establishing the path can be simplified by just picking the green dangling line from Character Map and dropping it onto the Excel Destination object on the Data Flow page. As seen in the next screenshot, you may also edit the path after it is created by right-clicking on this green line and choosing the Edit… drop-down menu item.
Step 6: Configure the Microsoft Excel Destination component
The data is on its way through the path, represented by the green line in the previous step. The Excel Destination also requires a connection manager.
The Excel Destination connects to an MS Excel on your hard dive using the connection properties defined in a connection manager.
- Right-click the Excel Destination and from the drop-down menu choose Edit.
This displays the Excel Destination Editor. Excel requires an OLE DB connection manager and if there are no configured connection managers (by you or a previous user) the drop-down will be empty.
- Click on the New… button.
The Excel Connection Manager window gets displayed as shown in the next screenshot. Here, you need to use the Browse button and pick the Excel file as the destination. The data will be written to the destination when the package is run.
- Open Windows Explorer and create an Excel file in the C:drive. For this tutorial, TableToXls.xls is chosen.
Besides connecting to an existing file, the Excel Connection Manager supports creating a file on the folder of your choice in the machine using the Browse… button.
- Now browse to the newly created file using the Browse… button and choose this file.
- Click on the OK button in the Excel Connection Manager window.
- For the Data Access Mode, accept the default, Table or View.
You have to indicate the name of the Excel sheet that will be used. (Do not click on the drop-down for locating the sheet. The drop-down will show the three Excel sheets that are found in a newly created Excel worksheet file; all of them having just one column each.)
- Click on the New… button.
You are creating a new Excel sheet. This pops-up a Create Table window showing the columns that are being piped into the component, as shown in the following screenshot.
- Click on the OK button, shown in the previous screenshot.
A new Excel Sheet, Excel Destination, will be added to the TableToXLS.xls file. If you now open and review this file (TableToXls.xls) you will see the column headers are added to this sheet.
- Click on the Mappings in the left-hand-side pane of the Excel Destination Editor, which shows the mappings from the input to the output.
This shows all the columns from the Character Map Data Flow Component being written to the destination file, as shown in the following screenshot.
- Click on the OK button in this window. The package is now completely configured and ready for execution.
Step 7: Test data transfer from SQL Server table to Excel Spreadsheet
- Right-click the
TableToXls.dtsxin the Solution Explorer and from the drop-down choose, Execute Package.
All three components on the Data Flow page turn green, indicating that the package executed successfully without errors. You may review the Progress tab in the 'Canvas', which shows all the details of the execution of the package.
- Now open up the
TableToXLs.xlsfile and review.
A few rows of data are shown in the next screenshot. Notice that the Character Map Data Transformation component has capitalized all the characters in the CompanyName column.
This chapter described the following:
- Configuring data flow components that are necessary for transferring data from a single table in SQL Server 2005 to an Excel file.
- The usage of a Character Map Data Transformation component.
The reverse of this transfer can be accomplished by choosing an Excel Source and a SQL Server Destination.
This chapter excerpt from Beginners Guide to SQL Server Integration Services Using Visual Studio 2005 by Jayaram Krishnaswamy, is printed with permission from Packt Publishing, Copyright 2007.