Professional SQL Server™ 2005 Integration Services

4.2. Sources

A source is where you specify the location of your source data. Most sources will 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. There are six sources altogether that can be used out of the box with SSIS. To reach the Data Flow tab, create a new Data Flow task in the Control tab. You can then drag the source from the Toolbox.

4.2.1. OLE DB Source

The OLE DB source is the most common type of source, and it can point to any OLE DB–compliant data source. To configure the OLE DB source, double-click the source once you've added it to the design pane. In the Configuration Manager page of the OLE DB Source Editor (Figure 4-8), select the Configuration Manager of your OLE DB source from the OLE DB Connection Manager drop-down box.

The Data Access Mode option sets how you wish to retrieve the data. Your options here are Table/View or SQL Command, or you can pull either from variables. Once you select the Data Access Mode, you will need the table or view, or you can type a query.

Figure 4.8. Figure 4-8

Like the other sources, you can go to the Columns page to set columns that you wish to output, as shown in Figure 4-9. Simply check the columns you wish to output, and you can then assign the name you wish to send down the data flow ...