SSIS data conversion component _ copy conversion, data conversion, and row count

1. Copy and convert

Copying column conversion is a simple conversion mechanism. It copies data from one or more columns. Then, you can perform any operations on the data in the copied column. Data in the copied column is not affected. Next, we will introduce the conditional splitting package in the previous article to explain how to use replication conversion.

SetCopy column ConversionFrom the toolbox to the data flow tab. AndExcelsaleordercoutRow count connection. The connection direction is to convert the row count to the copy column. Double-click to open the copy column. The Edit page is displayed. Select the column to be copied and rename it. The result is as follows:

Now that you have configured the copy column conversion tool, you can drag any control from the toolbox to connect to it. Use the copied column.

Data Conversion)

Data conversion is also a simple conversion component, which converts the type of the input column to another type and serves as the new column output. You can apply Multiple conversions to a single input column. Let's take a look at how to use this feature.

1. Open the intergration Service Project and create a package named "dataconversiondemo. Drag and Drop a Data Flow Task component in the control flow. :

 

2. Create an ole db connection in the Connection Manager to connect to the database adventureworks. The result is as follows:

3. On the data flow tab, move an ole db source and a data conversion task. And drag the connection line (green or red arrow) from the source or previous conversion to the data conversion to connect the data to the data flow.

4. Double-click data conversion. The window is displayed. Select the column to be converted.Datatype drop-down listSelect the required data type.Output alias ColumnThe alias used to set the output.

5. ClickOK,The Data Conversion Function is configured.

 

Three-row count

When a row passes the data flow, the row count is converted to count, and the final counting result is stored in a variable. Before using the row count control, we need to create a custom variable.

1. Click the "SSIS (s)" option in the menu bar and select the "variable" option in its drop-down box. The following window is displayed:

2. ClickAdd variableButton with a red circle in it. A variable is added:

Change "variable" in the name to "count". The user-defined variable has been added. If you want to delete a custom variable, select the variable to be deleted, and click the red "X" button in the figure.

3. Drag and Drop a row count to the "dataconversiondemo" package and link it to the data conversion. The arrow points to the row count. Right-click the row count and select the edit option. In the displayed window, set variablename to user: count. ClickOKButton to complete the row count configuration.

If you want to view the converted data during package execution. We need to configure a data viewer.

Right-clickData conversion and row count conversion connections, select"Data Viewer", The following dialog box is displayed:

Then select "data viewer", on the rightClick AddButton, as shown in the following figure:

In"Regular"Tab, select grid, and clickOK. OK. data viewer configuration is complete,The configuration of the entire package is as follows:

At this point, we have learned how to use the three conversion functions: Copy conversion, data conversion, and row count. At the same time, this article also describes how to add User-Defined variables and data viewer.