SSIS script component

 

Why script component

Script Component enables us to use. Net custom code in SSIs. We can use it for the following purposes, or we should consider using scriptcomponent when ETL has the following requirements:

1. If we need to perform multiple conversions on the data (for example, adding values to two columns and then calculating the average), we can use scriptcomponent instead of using multiple conversion spaces in the data stream.

2. Access some business rules that already exist in. net programs and

3. Use the following custom formulas or functions, such as using the bank card verification algorithm (luhn) to verify the credit card number

4. Check column data and skip inappropriate data, such as checking the age column and skipping all data older than 100 or less than 0 years old

We can see that the script component provides a way to use.. NET Programming Method to add a custom data processing function to the data stream. However, if our goal is to reuse the custom function in multiple packages, you should consider developing custom modules instead of using script modules.

Use scriptcomponent

Script component can be used as source, transformation, or destination. Depending on its purpose, it supports one input and multiple outputs. The Custom Script in this control is triggered once every line in input or output:

1. If it is used as the source, it supports multiple output

2. If transformation is used, it supports one input and multiple outputs.

3. If used as destination, it supports an input

4. Error output is not supported.

 

To use the script component, you only need to configure the metadata in the designer and edit the script in vsta.

Configure metadata

Compared with other modules, script component has only four simple interfaces:

Script Interface

In the script interface, the most important thing is the variable configuration. We can select some read-only and readable and writable variables to pass to the script. Then, these variables can be used as input or input values in the script:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/4B/F8/wKioL1Q2QrCh64hPAABLzWO0GPY108.jpg "Title =" 1.png" alt = "wkiol1q2qrch64hpaablzwo0gpy108.jpg"/>

Input Interface

This interface is available only when script component is used as transformation or destination. It is no different from the input of other modules. It selects columns from a certain input:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/4B/F7/wKiom1Q2QpTCY-U1AAE0TPOBEsU262.jpg "Title =" 2.png" alt = "wKiom1Q2QpTCY-U1AAE0TPOBEsU262.jpg"/>

Input and Output Interfaces

In this interface, you can add custom output and output columns and specify the Data Type of the columns:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/4B/F7/wKiom1Q2QqvSSIAYAAHKrpwn2JY749.jpg "Title =" 3.png" alt = "wkiom1q2qqvssiayaahkrpwn2jy749.jpg"/>

Connection Manager interface

Here you can add the Connection Manager that will be used in the script, but I have never used this interface in the actual project.

Script editing

On the script editing page, click Edit script on the script page. Before discussing how to customize our code, let's take a look at the skeleton of the custom code. As shown in the following figure, there are actually three file classes:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/4B/F7/wKiom1Q2Qr3zv6TuAAA578riIe8488.jpg "Title =" 4.png" alt = "wkiom1q2qr3zv6tuaaa578riie8488.jpg"/>

WhereComponentwrapperFile represents a custom script component module class-usercomponent, which inherits from the base class of scriptcomponent. The following is a simple view of the class. It mainly contains two types of things:ConnectionsAndVariablesVariables, which can be used to obtain the ETL variables and Connection Manager specified in the scriptcomponent module metadata designer. The other is some rewritable functions (not listed) to allow us to rewrite to process input and output.

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/4B/F9/wKioL1Q2QwTBXIwdAACPQf_52bM065.jpg "Title =" 5.png" alt = "wkiol1q2qwtbxiwdaacp1__52bm065.jpg"/>

BufferwrapperThe file indicates the buffer part of the control. We know that buffering is required for any data flow task:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/4B/F7/wKiom1Q2Qt2DZ_YxAADc9aqzXyU538.jpg "Title =" 6.png" alt = "wkiom1q2qt2dz_yxaadc9aqzxyu538.jpg"/>

The class contained in the main file is the real scriptcomponent entry class, which is named scriptmain and inherited from usercomponent, indicating the scriptcomponent control we created:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/4B/F9/wKioL1Q2QybD_EG8AADsa7lXQuM200.jpg "Title =" 7.png" alt = "wkiol1q2qybd_eg8aadsa7lxqum200.jpg"/>

Used as source

When the script component is used as the source, because only the output has no input, the scriptmain class in the main file will contain a function named createnewoutputrows, all the content we need to process and output is directly or indirectly put in this function. If we split other custom logic, the function has only two functions:

1. Add a row of data to the output buffer, which is implemented by calling the myoutputname. addrow () function. myoutputname is the name of the output stream we set in the metadata designer.

2. Assign values to each column of the newly added row of data, which is implemented by myoutputname. columnname = Val.

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/4B/F7/wKiom1Q2SBmC7M0UAACkFh8LxNA747.jpg "Title =" 11.png" alt = "wkiom1q2sbmc7m0uaackfh8lxna747.jpg"/>

Used as Transformation

When it is used for conversion, I think there can be multiple outputs on msdn, but it is not possible to achieve this in the actual code. I don't know if I have made a mistake. Now we can see that for conversion and destination, the scriptmain function has an inputname_processinputrow () function. For example, the parameter of this function is the data row in the input buffer, however, you can also find and assign values to columns in custom output. For example, in the following example, the name column is the input column and the outputname column is the output column, which can be found in input0buffer. In this case, the main two-step functions of the script control are implemented in the same way:

1. Obtains the input value through row. colname.

2. Assign values to output columns, and assign values through row. outputcol

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/4B/F9/wKioL1Q2SGKha-rKAADDaww-rsQ331.jpg "Title =" 22.png" alt = "wKioL1Q2SGKha-rKAADDaww-rsQ331.jpg"/>

SSIS script component