SSIS package establishment-Connection Manager

In the previous article, we used an example to introduce SSIS package development. next we will learn how to use the tabs in the package. such as the Connection Manager tab, control flow tab, data flow tab, and event processing tab. This article describes the functions and usage of the Connection Manager.

The Connection Manager is used to connect to different types of data sources to extract and load data. Source data is required for development of any package.

The following table lists the Connection Manager types provided by SQL serverintegration services.

Type	Description
ADO	Connect to the ActiveX Data Object (ADO) object.
Ado. net	Use the. NET provider to connect to the data source.
Cache	Reads data from a data stream or from a cached file (. Caw) and saves the data to a cached file.
Excel	Connect to an Excel Workbook file.
File	Connect to a file or folder.
Flatfile	Connect to data in a single flat file.
FTP	Connect to the FTP server.
HTTP	Connect to the web server.
MSMQ	Connect to the message queue.
Msolap100	Connect to an SQL serveranalysis services instance or an Analysis Services Project.
Multifile	Connect to multiple files and folders.
Multiflatfile	Connect to multiple data files and folders.
Oledb	Use the ole db access interface to connect to the data source.
ODBC	Use ODBC to connect to the data source.
Smoserver	Connect to the SQL Server Management object (SMO) server.
SMTP	Connect to the SMTP mail server.
Sqlmobile	Connect to the SQL Server compact database.
WMI	Connect to the server and specify the scope of Windows Management Instrumentation (Wmi) management on the server.

The following describes the commonly used Connection Manager types.

• Connect the ole db access interface to the data source (database connection)

1. First open the previous article to create a project named "ssisdemo.
2. In the Connection Manager window, right-click and choose new ole db connnection. The following window is displayed:

LeftData ConnectionThe connection shown in the dialog box is the database connection we have created. Right sideDatabase Connection PropertiesThe corresponding property information is displayed. You can also create a new database connection. ClickNewButton. The following dialog box is displayed.

 

In this dialog box, you can enter the corresponding content as needed. This example is to connect to the local adventureworks database. ClickOKButton. The following information is displayed in the Connection Manager:

 

In the figure above, localhost. adventureworks. Sa is the database connection we just created. Right-click localhost. adventureworks. SA and the Properties dialog box is displayed. You can modify these attributes as needed. For exampleNameModify the property to adventureworks. The establishment of this simple ole db connection method is complete. The meanings of these attributes. You can gradually learn about it later.

•  File connection to a file or folder (flat file connection)

The flat file Connection Manager is much more complex than the ole db connection method. The flat file connection mode is mainly used to connect non-database files. The following describes how to create a flat file connection. Assume that we have a file named user.txt. The data format is as follows:

Each row has two columns of data. Separated by "tab. Next we will introduce how to connect to this text file in the Connection Manager.

1. Still inConnection Manager boxRight-click, selectCreate a flat file connection, Pop-up dialog box

As shown in the figure, you can see that the flat file connection manager needs to be configured.Connection Manager name,General,Column,AdvancedPreview this attribute. The following describes how to configure these attributes.

• Connection Manager Name:Provide a unique name for the flat file connection in the workflow. The provided name is displayed in the Connection Manager box.
• General Tab:You can select the file and data format on the "General" page of the "flat file Connection Manager Editor" dialog box. You can use a flat file connection to connect a package to a text file. This tab contains the following attributes:File Name:Type the path and file name to be used in the flat file connection.
  Region settings:In the region settings drop-down box, select the specified region location to provide language-specific information for sorting by date and time format. It is best to choose when designingEnglish (USA)Option
  Unicode check box:Indicates whether Unicode is used. The code page cannot be specified if Unicode is used.
  Code Page:Select the code page of the specified non-Unicode text in the code page. Best choice during design1252 (ANSI-Latin I)Format:Select the text format in the format drop-down box.

Attribute	Description
Separator	Each column is separated by a separator specified on the "column" page.
Fixed width	Fixed column width
Not aligned on the right	In the unaligned text on the right, the width of each column except the last column is the same. It has line delimiters

Text qualifier:Specifies the text qualifier to use. For example, you can specify that text fields must be enclosed in quotation marks. If you select a text qualifier, you cannot re-select"None"Option, type none to deselect the text qualifier.
　　　Subject Line separator:Select from the separator list of the header line or enter the separator text.

Value	Description
{Cr} {lf}	The title line is separated by a combination of carriage returns and line breaks.
{Cr}	The line names are separated by carriage returns.
{Lf}	The line names are separated by line breaks.
Semicolon {;}	Line names are separated by semicolons.
Colon {:}	The header rows are separated by colons.
Comma {,}	Line names are separated by commas.
Tab {t}	Line names are separated by tabs.
Vertical bars {|}	The title line is separated by a vertical line.

Number of titles to be skipped:Specifies the number of titles or the number of initial data rows to be skipped (if any ).
　　Display the column name in the first data row:Indicates whether the column name is required or whether the column name is provided in the first data row.

AccordingGeneral TabThen, we can set the text display format as needed. For example

• Column tab:Use"Flat file Connection Manager Editor"Dialog box"ColumnYou can set row and column information here. And preview the corresponding file. For example:

Includes the following attributes:

Row and column delimiters:This attribute is the same as the title line Separator in the General tab. You can set the display mode of rows and columns as needed.
Preview:View the sample data in the flat file. The data has been divided into columns and rows by the selected options, as shown in.
Refresh:Click"Refresh"View the effect of changing the separator to be skipped. This button is visible only after you change the row or column options.
Reset column:Click"Resetting columns"You can delete all columns except the original columns. You can only go back to the"Column"Tab, this button is visible.

In this exampleColumn SeparatorOptionSelect a tab (t ),ClickRefreshButton.

• Advanced Tab:Use"Flat file Connection Manager Editor"Dialog box"Advanced"Page to set the attributes of how to read and write data in a flat file. You can change the names of columns in a flat file and set attributes including the data type and delimiter of each column in the file. By default, the length of a string column is 50 characters. You can adjust the length of these columns to avoid data truncation or exceeding the column width. You can also update other metadata to make it compatible with the target column. For example, you can change the data type of a column that only contains integer data to a numeric data type, for example, dt_i2. You can manually modify these changes, or click the "select type" button to use the "provide column type suggestions" dialog box to evaluate the sample data and make some changes automatically. In the Advanced tab, we can do the following:

Configure the attributes of each column:Select a column in the left pane to view its attributes in the right pane. See the following table for details about data type attributes. Some properties listed are only configurable for certain flat file formats.

Attribute	Description
Columntype	It indicates whether the column is separated by a separator, fixed width, or not aligned on the right. This attribute is read-only. In an unaligned file on the right, the width of each column except the last column is fixed. It is separated by line delimiters.
Outputcolumnwidth	The specified value is stored as the number of bytes. For Unicode files, this value corresponds to the number of characters. In a Data Flow task, this value is used to set the output column width of the flat file source. Note: In the object model, the attribute name isMaximumwidth.
Datatype	Select from the list of available data types.
Textqualified	Indicates whether there are text delimiters (such as quotation marks) around the text data ). Value description The text data in the true flat file is limited. The text data in the false flat file is not limited.
Name	Provide descriptive column names. If you do not enter a name, integration services will automatically create a name in the format of "column 0" and "column 1", and so on.
Datascale	The number of decimal places in the specified number. The number of decimal places is the number of digits after the decimal point.
Columndelimiter	Select from the list of available column delimiters. Select a separator that cannot appear in the text. This value is ignored for columns with a fixed width. Value description The {Cr} {lf} column is separated by a combination of carriage returns and line breaks. {Cr} columns are separated by carriage returns. {Lf} columns are separated by line breaks. The semicolon {;} columns are separated by semicolons. The {:} colons are separated by colons. The comma {,} column is separated by a comma. The tabs {t} column are separated by tabs. The vertical bars {|} are separated by vertical bars.
Datapretries	Specifies the precision of digital data. Precision refers to the number of digits.
Inputcolumnwidth	The specified value is stored in bytes. For Unicode files, this value is displayed as the number of characters. This value is ignored for separated columns. Note that in the object model, the attribute name isColumnwidth.

 

New:ClickNewAdd a new column. By default, clickNew"Button will add a new column at the end of the list. This button also includes the following options, which can be selected from the drop-down list.

Value	Description
Add Column	Add a new column at the end of the list.
Insert before it	Insert a new column before the selected column.
Insert after	Insert a new column after the selected column.

Delete:Select a column and click"DeleteTo delete the column.

Recommended type:Use the "provide column type suggestion" dialog box to calculate the sample data in the file and obtain suggestions on the data type and length of each column.

After fully understanding the properties of the Advanced tab, we willColumn 0The name and datatye attributes of are changed to the customerid and dt_i8 types. SetColumn 1The name and datatye attributes of are changed to the skulist and dt_str types, and the outcolumnwidth attribute is changed to 1000.

• Browsing tab:Click the Browse tab to view the displayed data.

After the first three tabs are successfully configured, You can preview the data display format. Last clickOKButton. The flat file Connection Manager is successfully established. FinallyConnection ManagerThe box displays the flat file manager created just now, named paiuserout.txt.

This section describes two commonly used connection managers. For other connection managers, refer to these two examples to try to establish a connection.