Microsoft Excel Business Intelligence Through Script Task
Despite the technological superiority of the SQL Server product line, Microsoft Office Excel remains a widely popular application used to store a variety of semi-structured data. As noted in this DataBase Journal report, it lacks mining and warehousing capabilities provided by database management systems. These shortcomings can be remediated by importing spreadsheets into a relational database.
“Keeping these warnings in mind and heeding the consequences associated with them, let's examine what type of actions can be carried out by leveraging types and members of the Microsoft.Office.Interop.Excel namespace. To illustrate these capabilities, we will present a generic scenario, in which we enumerate Excel workbooks residing in an arbitrarily chosen location, then identify their worksheets and named ranges for each of them, and finally display the content of their individual cells. The code will be implemented using Visual Studio Tools for Applications accessible via SSIS Script Task.
“To start, launch Business Intelligence Development Studio and create a new project based on the Integration Services template. Drag the Script Task icon from the Toolbox and drop it on the Designer interface. Display its Editor dialog box, designate Visual Basic .NET 2008 as the ScriptLanguage, and confirm your choice by clicking on the OK command button. Use the Variables window to define a new variable (we will call it FolderName) of String type and set its value to the name of a folder where the Excel spreadsheets reside. Next, activate the Script Task Editor dialog box (accessible via the Edit… option in its context sensitive menu), add the User::FolderName to the ReadOnlyVariables entry of the Script section, and click on the Edit Script… button to display Visual Studio Tools for Applications.”
Read the Full Story at DataBase Journal
