Let us take a look at the budget Excel file. It can be found in the C:\TU\Data folder. Apparently it contains data about the expected budget, month by month. The first column is the Year, the second is the Month number and the third is the expected Budget figure.
As we have already learned from previous lessons, we might just as well start out by adding these new data to our SSIS package – we will have to do it at some point anyway.
By adding the Excel data to the SSIS package we will treat these data like any other data, i.e. like data that may potentially be updated from time to time. In this way the CFO now has the opportunity to update this Excel sheet as he or she likes. The only requirements are that the structure (the three columns) should not be changed, and the file should be kept in the designated folder location.
First, we will need an Excel Connection Manager in our SSIS package. Right click the Connection Manger panel to add a new (undefined) connection.
NOTE: On the virtual machine prepared for this course Microsoft Office 2010 has been preinstalled.
Had this not been the case, an import of excel sheets with the suffix xslx could result in the following error message:
The ‘Microsoft.ACE.OLEDB 12.0’ provider is not registered on the local machine.
In that case googling the error message should help you navigate to a Microsoft site where this missing provider can be downloaded.
The name of the provider is (a little confusing) AccesDatabaseEngine.exe – it facilitates transfer of data from both Access and Excel to e.g. Microsoft SQL Server.
Pick ‘EXCEL’ from the list of available connection types.
And finally, define path, version and whether first row contain column names.
Although not necessary, but for sake of providing a better overview of the SSIS package, we will create a new, separate Data Flow Task for the Budget data.
In the Data Flow Task we can now select an Excel Source.
The Excel Source needs to be defined. As an Excel file may contain multiple sheets, we must select the on containing the data that we want. In this case Sheet1.
Next, we will need to add an OLE DB Destination:
- Add the OLE DB Destination.
- Name it properly.
- Connect the Excel Source to the OLE DB Destination.
- Make sure that the OLE DB Destination uses the Stage Connection Manager.
- Click the ‘New’ button to get an SQL script to create a new Budget table.
Run the Data Flow Task once – just to get data into our Budget table.