In our last lesson, we showed you how to use SSIS to export data to Excel. In fact, all of our previous SSIS lessons have focused on exporting data from SQL into some other format. In this lesson, however, we will show you how to import data from Excel into SQL.
This is very helpful as you start to develop your business intelligence program in your bank or credit union. Think of how much data exists at your financial institution sitting in the form of Excel spreadsheets. By importing this data into SQL, it can be cross-referenced and/or integrated with existing SQL data. This is a key first step in any business intelligence program, and in this lesson we will help you get started!
Setting up our Data Source
The Excel spreadsheet that I will be importing into SQL is based on data compiled by the NCUA. The spreadsheet contains a list of all credit unions and their associated member totals and total assets (as 6/30/2014). The image below gives you a quick snapshot of what the data looks like:
With our source Excel spreadsheet identified and described, open up your SSIS solution named “Credit Union SSIS Solution” if you have been following along with previous examples. Otherwise, create a new SSIS solution and name it “Import to SQL.dtsx”. Just like we have in past examples, drag a “Data Flow Task” from the “SSIS Toolbox” onto the “Control Flow” designer. Right click on the task, click “Rename”, and then rename the task “Import NCUA File into SQL”.
Now, double-click on the newly renamed task to be brought to the “Data Flow” editor. From the “SSIS Toolbox”, drag an “Excel Source” item onto the data flow designer. Rename this item “NCUA Excel File”.
Double-click on the renamed item; the “Excel Source Editor” window will appear on your screen. Click the “New” button to the right of the “Excel connection manager” dropdown. Click “Browse”, then locate the file you will be importing (in my case, the NCUA file), press “Open” and then press “OK” when you are brought back to the “Excel Connection Manager” window.
In the “Name of the Excel sheet” dropdown, choose the specific spreadsheet within the Excel workbook that you wish to choose as your data source.
Press “OK” once this has been completed.
Defining the Destination to Import Data into SQL
With the Excel source defined, drag an “OLE DB Destination” item from the “SSIS Toolbox” onto the data flow designer. Rename this item “SQL Destination”. Click the “NCUA Excel File” item so that it is selected then drag the blue arrow from the source item to the “SQL Destination” item.
Next, double-click on the “SQL Destination” item. Here, we defined where the NCUA data will reside in SQL. Click the “New” button to the right of the “OLE DB connection manager” dropdown. Select the connection from the left panel on the “Configure OLE DB Connection Manager” window that appears or define a new one by clicking the “New” button at the bottom of the window. Press “OK”.
For this example, we will need to create a new table in the database for this Excel data to reside. To do this, click the “New” button to the right of the “Name of the table or the view” dropdown. The “Create Table” window will appear on screen containing the SQL code that will be used to create a new table. I want to make one subtle change and alter the name of this table. After “CREATE TABLE” replace “[SQL Destination]” with “NCUA_Data”.
Press “OK” once you have done this. If you open SQL Server Management Studio, you will see that this new table has been created in the database. This is important; the table is created once you press “OK” from the “Create Table” window – NOT when you execute the package for the first time.
To finish setting up the destination, press the “Mappings” option from the left side of the “OLE DB Destination Editor” window. Ensure that the column mappings are correct, and then press “OK”.
Execute the package (by pressing F5 or the green play button on the toolbar) to see if everything works. Assuming you successfully execute the package in SSDT, open up SQL Server Management Studio. We want to view the contents of the table we just sent the Excel data to in order to validate the process properly imported all data.
It’s that easy! The process required to import data into SQL from Excel is nearly identical to the previous export examples we have completed throughout the series. We simply define the source, define the destination, and then the mappings between the two. Learning this relatively simple task in SSIS can open up tons of new opportunities for you to be able to use your data more effectively. Eliminate silos of Excel data by bringing that data into SQL. Doing this will allow you to integrate and cross-reference it with your other SQL data sources ultimately bringing you closer to business intelligence success!