How to move data from Excel to SQL Server 2008/2012- Act 1 Scene 2

Today, I will discuss on how to configure variables and excel connection manager in BIDS 2008. The same can be applied to BIDS 2012 and later too.

I spent over a week trying to find out a method by which I could read multiple files into a database. Initially, I began with trying to read multiple excel files into SQL Server database table. No luck, I had to face many errors to which I couldn’t find any satisfactory solution. Then I devised a strategy. First break the problem at hand into sub-problems and then try to solve them. This strategy in Computer Architecture is termed as “Pipelining”. Anyway, here I’m not going to discuss on Pipelining. Continuing further, I decided to try reading a simple excel file and writing its content to a text file. By the way I’m using MS Excel 2013. I then created two excel files named Book1.xlsx and Book2.xlsx. This example will work for MS Excel 2007 too. The following figure shows the file content of the two Excel files.

Book1Figure 1: Book1. xlsx

Figure 2: Book2.xlsx

As already stated in my previous post I’m using Business Intelligence Development Studio (BIDS) 2008 and SQL Server Enterprise edition. Now, I will detail on how to transfer data from multiple excel files into a flat file or a text file. In the next post, I will describe on how to achieve the same task but the destination will be SQL Server database.

Before I proceed any further, a couple of points you must remember if you don’t want to pull your hair like I did while grappling with BIDS. I’m going to underline them because they are important points to remember.

Important points to remember

  1. Ensure that the structure of excel file is same. By similar structure I mean if you look at Figure 1 & Figure 2 above, you will see that both of these files have exactly two columns each. So this is meaning of same structure. Your extraction project will fail if the source files (in this case the excel files) are different to each other in structure.

Alright, let’s begin now. Open up BIDS 2008 or Visual Studio 2012. Steps on how to create a new package has already been detailed in my previous post. If you have not read it, I recommend you do so before continuing further here. Assuming, you have created a new project and given it a worthwhile/suitable name. So the very first step is to create two variables with package level scope. How to do this is easy. Ensure that you are in the Control flow tab as shown below in Figure 2.0


Figure 2.0 Control Flow

 Then click on the variable menu as shown in Figure 2.1

variable2 Figure 2.1 Variables menu

Next you create two variables with Package level scope and give them the default values as shown below in Figure 2.3

createVariable variable3

Figure 2.3 How to create Variables and assign values to them

The next step is to create an excel connection manager string. So right click on the Connection manager as shown in Figure 3

connectmgr Figure 3: Add a connection

and then choose “New Connection” as shown in Figure 4 connectmgr1 Figure 4: Add Excel Connection Manager

When you have clicked on the button Add as shown above in Figure 4, you will then see the following as shown in Figure 5 connectmgr2 Figure 5: How to configure Excel Connection Manager

As shown in Figure 5, step 2 states that you have to choose the version of your excel software. So if you are using a version of Microsoft Office earlier than 2010 then you will choose “Microsoft Excel 97-2003” but if you are using a version of Microsoft Office 2010 and later then you will choose “Microsoft Excel 2007” from this drop down list. Also I have suggested in step 3 of Figure 5 that you remove the check mark on the box because if you leave it there, then the data will be copied with the row names each time a file is read. This anomaly is shown below in Figure 6. If you do not want the output data in this format then remove the check mark. column names So when you click on the Ok button, you will on the bottom pane of the Connection Managers, your Excel connection manager created as shown in Figure 6 excelconnmgr Figure 6: Excel Connection Manager

Now, you want to set some properties of the Excel connection Manager which are as follows; If you click on the Excel connection Manager once it will be highlighted and you will see the properties window as shown below in Figure 7, excelconnmgrProperties Figure 7: Excel connection Manager Properties

You will notice a small + sign to the left of the Expression. I have magnified it and shown below in Figure 8 expressn1 You have to click on this ‘+’ sign so as to expand it. When you do so you will see an ellipsis button on the right of it. ellipsis Figure 8: Ellipsis button

Click this button and the Expression builder window will open up as shown in Figure 9 expressn2  Figure 10: Expression Builder Property

In Property column click on the row to choose “Connection String” as shown in Figure 10 and then Under “Expression” click on the ellipsis button and the “Expression builder” window will open up as shown in Figure 11 expressnBuild Figure 11: Expression Builder

Important Point to remember

Ensure that the expression is typed as follows in the “Expression” box as shown above in Figure 11. And the same is shown below in Figure 12.

“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=”+@[User::filePath]+@[User::fileName]+”;Extended Properties=\”Excel 12.0 XML;HDR=YES\”;”

 expressn Build Figure 12: Expression Builder and Evaluation

Please note that it is this expression that iterates from one excel file to another excel file. So if you get this wrong, you will not be able to read multiple excel files.

Now that we have successfully configured the connection manager, perhaps you need a break for I need one too. In the next post I will detail how to use this connection manager and the variables defined above to dynamically read multiple excel files and populate them into a text file.

Till then eat healthy, stay safe and take care.