Chapter 8: Accessing Heterogeneous Data

Overview

In Chapter 7 you discovered the many scripting enhancements available in SSIS. In this chapter, you'll learn about importing and working with data from heterogeneous, or non-SQL Server, sources. In today's Enterprise environment, data may exist in many diverse systems, such as mainframes, Oracle, Office documents, XML, or flat files, to name just a few. The data may be generated within the company, or it may be delivered through the Internet from a trading partner. Whether you need to import data from a spreadsheet to initially populate a table in a new database application, pull data from other sources for your data warehouse, or rely on a Web service to grab up-to-the-minute information, accessing heterogeneous data is probably a big part of your job.

You can load data into SQL Server using SSIS from any ODBC-compliant or OLE DB-compliant source. Many ODBC drivers and OLE DB providers are supplied by Microsoft for sources like Excel, Access, FoxPro, Paradox, Oracle, and dBase. Others are available from database vendors. A variety of data source components are found in SSIS. These include Excel, Flat File, XML, DataReader (which is used to connect to .NET sources), OLE DB (which allows connections to many different types of data), and Raw File (which is a special source used to read data that has been previously exported to a Raw File destination). If the supplied data sources do not meet your needs, it is also possible to create custom data sources.

This chapter will walk you through accessing data from several of the most common sources: Excel, MS Access, Oracle, XML, and Web Services. Each one is relatively easy to work with, but each is configured a bit differently. Excel is often used as a quick way to store data because spreadsheets are easy to set up and use. Access applications are frequently upsized to SQL Server as the size of the database and number of users increase. Even companies running their businesses on Oracle or another of SQL Server's competitors sometimes leverage SQL Server for its cost-effective reporting and business intelligence solutions. XML and Web Services (which is XML delivered through HTTP) are standards that allow very diverse systems to share data. The new XML data source allows you to work with XML as you would almost any other source of data. First, you'll take a look at Excel.