Containers and data streams in SSIs-Data sources

In SSIs, most data sources direct to a connection manager. by specifying a Connection Manager, you can reuse the connection in the package so that all connections can be reset once, there are 6 different data sources in SSIs.

OLE DBData Source

The ole db data source is used to connect Ole EB data. It is the most common data source. Click the data flow label at the top, drag and drop an ole EB source, and double-click the data source to open the editing page 4-8. Select the database to be connected from the ole db Connection Manager drop-down list.

Figure 4-8

In vs2005, label Data Access Mode sets how to retrieve data. It has two options: Table/view and SQL command, or you can obtain the option value from the variable. When you select table/view, you must specify a table. When you select SQL command, you must enter a statement.

Like other data sources, you can select the columns to be output in the column label. 4-9. Selecting the columns you want to output can improve the running performance of the package.

Figure 4-9

You can set error handling on the error output page 4-10. For example, you can output any data conversion errors. You can also set whether to ignore the errors when a conversion error occurs in a row, or re-execute or report an error. In the truncation column, you can set error handling when the truncation operation occurs. It has the same options as error handling.

Figure 4-10

ExcelData Source

Just like its name, the Excel Data Source settings are connected to an Excel worksheet. After creating an Excel connection, you can choose to connect to a sheet in an Excel worksheet and change the data connection mode. 4-11

Figure 4-11

Flat file datasource)

The flat file data source is used to connect a flat file with no relevance to the Data. flat files are generally separated by commas (. CSV) using commas (,) and return signs, or the data format is set to a fixed length. The latter is a commonly used file format on the mainframe. Each line of it has a fixed start and end symbol. This type of file processing speed is faster, but it takes a longer time for developers to process data columns. The settings of the flat file data source are similar to those of the Excel Data Source. After adding the flat file data source in the data flow panel, specify it to a connection manager. You can select the column to be processed on the column tab page. All detailed settings, such as the separator type, are automatically set during file loading. 4-12

Figure 4-12

Original file data source

The original file data source is the best file source for fast processing in SSIs. You cannot add a column to the original file data source, but you can delete a column like other file data sources. Because data does not need to be converted during processing, the processing speed is faster than that of other data sources, the cost is low flexibility.

XMLData Source

The XML data source is a very powerful data source that can connect to an XML file locally or remotely (via HTTP or UNC). Its configuration is somewhat different from that of the ole db data source, it is preferred to connect to XML files through local or UNC paths, and to connect to XML files through HTTP addresses. This method is useful for collaborative development. Once you connect to an XML file, you need to click Generate XSD to generate an XSD file. The format definition can also be an inline XML file. Other settings are similar to those of other data sources. You can filter out unnecessary columns. 4-13

Figure 4-13

There is also a data source that can be directly readProgramGenerally, the package in SSIs provides a. NET data source. It uses the ADO. Net Connection Manager to connect to the provider.