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.

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.

Figure 4-8

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.

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 in the Output column. Select only the columns that you will want to use, as the smaller the data set, the better performance you will receive.

Figure 4-9

Optionally, you can go to the Error Output page (Figure 4-10) and specify how you wish to handle rows that have errors. For example, you may wish to output any rows that have a data type conversion issue to a different path in the data flow. On each column, you can specify that if an error occurs, you wish the row to be ignored, be redirected, or fail. The Truncation column specifies what to do if a data truncation occurs. You have the same options available to you for Truncation as you do for the Error option.

Figure 4-10

Excel Source

The Excel source is a source that points to an Excel spreadsheet, just as it sounds. Once you point to an Excel Connection Manager, you can select the sheet from the Name of the Excel Sheet drop-down box or you can run a query by changing the Data Access Mode.

Flat File Source

The Flat File source provides a data source for connections that are not relational. Flat File sources are typically comma- or tab-delimited files, or they could be fixed-width. A fixed-width file is typically received from the mainframe, and it has fixed start and stop points for each column. This method makes for a fast load but takes longer at design-time for the developer to map each column. You specify a Flat File source the same way you specify an OLE DB source. Once you add it to your data flow pane, you point it to a Connection Manager connection that is a flat file or a multi-flat file. After that, you go to the Columns tab to specify what columns you want to be presented to the data flow. All the specifications for the flat file, such as delimiter type, were previously set in the Flat File Connection Manager.

Raw File Source

The Raw File source is a specialized type of flat file that is optimized for quick usage from SSIS. A Raw File source is created by a Raw File destination (this will be discussed later in this chapter). You can't add columns to the Raw File source, but you can remove unused columns from the source in much the same way you do in the other sources. Because the Raw File source requires little translation, it can load data much faster than the Flat File source, but the price of this is that you have little flexibility. Typically, you see raw files used to capture data at checkpoints to be used later in case of a package failure.

XML Source

The XML source is a powerful SSIS source that can use a local or remote (via HTTP or UNC) XML file as the source. This data source is a bit different from the OLE DB source in its configuration. First, you point to the XML file locally on your machine or at a UNC path. You can also point to a remote HTTP address for an XML file. This is useful for interaction with a vendor. This source is very useful when used in conjunction with the Web Service task or the XML task. Once you point the data item to an XML file, you must generate an XSD file (XML Schema Definition) by clicking the Generate XSD button or point to an existing XSD file. The schema definition can also be an in-line XML file, so you don't necessarily have to have an XSD file. The rest of the source resembles the other sources, where you can filter the columns you don't want to see down the chain.

Data Reader Source

The Data Reader source allows you to make a .NET provider a source and allows you to make it available for consumption inside the package. The source uses an ADO.NET Connection Manager to connect to the provider. Consuming and contributing to external systems by using this source and destination will be discussed in much more detail in Chapter 17.