Knight's 24-Hour Trainer: Microsoft® SQL Server® 2008 Integration Services

Chapter 14. Extracting Data from Sources

Generally when you create SQL Server Integration Services (SSIS) packages, it is for the purpose of moving data from one point to another. A source is where you specify the location of the data you want to move or transform.

Most sources point to the connection manager in SSIS. By pointing to the connection manager, you can reuse connections throughout your package because you need only change the connection in one place. In this lesson the most frequently used sources (OLE DB, Excel, and flat file) are -described thoroughly.

OLE DB Source

The most common type of source used is the OLE DB Source, which can point to any Object Linking and Embedding Database (OLE DB)–compatible data source such as SQL Server, Oracle, and DB2. To configure the OLE DB Source, double-click the source once you've added it to the design pane in the Data Flow tab. In the Connection Manager page of the OLE DB Source Editor, shown in Figure 14-1, select the connection manager of your OLE DB Source from the OLE DB Connection Manager drop-down box. You can also add a new connection manager in the editor by clicking the New button.

The Data Access Mode option sets how you can retrieve the source data. The OLE DB Source has four different data access modes available:

• A table or view

• A table or view indicated in a variable

• The results of a SQL statement

• The results of a SQL statement initiated in a variable

SSIS does allow for a stored procedure to be accessed when using the SQL ...