Expert SQL Server™ 2005 Integration Services

3.1. Package Connections and Data Flow Sources

The data flow sources in SSIS provide the ability to connect to most standard data repositories, such as delimited files, Excel files, Oracle databases, and so on. With these data sources, which rely on package connections, some of the connections included out-of-the-box with the Windows and SSIS install include SQL Server, flat files (of various code pages), Analysis Services, Oracle, MS Excel, MS Access, XML, and so on.

It's important to note that many of the sources that SSIS can use come from the OS-level inclusion of Microsoft Data Access Components (MDAC). Go to www.microsoft.com/data for more information.

Both data flow and control flow components use separate objects at the package level to facilitate connectivity package connection, whether to connect to an FTP site, to a relational database, or to a file system folder. All data flow sources (except the Raw adapter) leverage package connections to extract data. As a review, the Connection Manager window appears at the bottom of the control flow and data flow, and right-clicking in the window allows a new connection to be created, as Figure 3-2 highlights.

Figure 3-2. Creating a new connection

The choices include creating a connection from an existing project Data Source, plus the ability to create a data source from many other types of connection types, including flat ...