Compile a simple synchronization conversion component for SSIs

The previous article introduced the concepts of synchronous and asynchronous conversions. In general, most conversions are synchronized, that is, one row is processed when a row is received. (Note: This may not be very accurate. In fact, even if it is a synchronization mechanism, data streams will be properly cached, but this cache is transparent to the number of users, the user can understand that a row is processed when a row is received)

The synchronization conversion component can be simple or complex. It depends on your design logic.

I will use a simple example to explain this article, aiming to let everyone know the design process of the synchronization conversion component.

Requirements:This component is simple, that is, to convert all the letters in the text column in the input column to uppercase.

 

1. Prepare a type

[Dtspipelinecomponent (







Componenttype = componenttype. Transform,







Description = "this is my custom data conversion"






,







Displayname = "capital conversion"






)]







Public






Class






Mytransfomation: pipelinecomponent







{















}

This type is prepared last time, but is not implemented. [Note] I changed displayname to "uppercase conversion"

If you are starting from scratch, note that the following references and using statements are added.

 

2. Implementation Code

The code will be composed of several parts

The first step is the providecomponentproperties method. This method prepares some metadata. It generally defines the attributes of input and output. The placeholder code for this method is as follows:

        public






override






void






 ProvideComponentProperties()







        {







base






.ProvideComponentProperties();







        }

. Csharpcode,. csharpcode pre
{
Font-size: small;
Color: black;
Font-family: consolas, "Courier New", courier, monospace;
Background-color: # ffffff;
/* White-space: Pre ;*/
}
. Csharpcode pre {margin: 0em ;}
. Csharpcode. Rem {color: #008000 ;}
. Csharpcode. kwrd {color: # 0000ff ;}
. Csharpcode. Str {color: #006080 ;}
. Csharpcode. Op {color: # partition C0 ;}
. Csharpcode. preproc {color: # cc6633 ;}
. Csharpcode. asp {background-color: # FFFF00 ;}
. Csharpcode. html {color: #800000 ;}
. Csharpcode. ATTR {color: # ff0000 ;}
. Csharpcode. ALT
{
Background-color: # f4f4f4;
Width: 100%;
Margin: 0em;
}
. Csharpcode. lnum {color: #606060 ;}

We modify it. The basic knowledge we need to know is that a conversion automatically has a default input and output even if nothing is done.

In this Code, we rename the default input and output. The purpose is to make them more vivid.

/// <Summary>














/// This method provides certain attributes required by the component.














/// Here we mainly rename the default input and output to make it more vivid.














/// </Summary>














Public






Override






Void






Providecomponentproperties ()







{







Base






. Providecomponentproperties ();







Componentmetadata. inputcollection [0]. Name = "capital conversion input"






;







Componentmetadata. outputcollection [0]. Name = "uppercase conversion output"






;







// Componentmetadata. outputcollection [1]. synchronousinputid = componentmetadata. inputcollection [0]. ID;














// If base. providecomponentproperties is called, this code can be omitted.














}

. Csharpcode,. csharpcode pre
{
Font-size: small;
Color: black;
Font-family: consolas, "Courier New", courier, monospace;
Background-color: # ffffff;
/* White-space: Pre ;*/
}
. Csharpcode pre {margin: 0em ;}
. Csharpcode. Rem {color: #008000 ;}
. Csharpcode. kwrd {color: # 0000ff ;}
. Csharpcode. Str {color: #006080 ;}
. Csharpcode. Op {color: # partition C0 ;}
. Csharpcode. preproc {color: # cc6633 ;}
. Csharpcode. asp {background-color: # FFFF00 ;}
. Csharpcode. html {color: #800000 ;}
. Csharpcode. ATTR {color: # ff0000 ;}
. Csharpcode. ALT
{
Background-color: # f4f4f4;
Width: 100%;
Margin: 0em;
}
. Csharpcode. lnum {color: #606060 ;}

Step 2: rewrite the preexecute method to prepare for execution. The requirement here is relatively simple, that is, to check all the final input columns. If their type is struct (STR or wstr), we will mark them.

 

List <int






> _ Columns = new






List <int






> (); // This set stores the index numbers of the columns to be processed.






















/// <Summary>














/// This method is preparations before processinput.














/// We detect all input columns that are of the dense type and record their index numbers.














/// To enable processinput to use this information, we define a public variable














/// </Summary>














Public






Override






Void






Preexecute ()







{







Idtsinput90 input = componentmetadata. inputcollection [0];







Idtsinputcolumncollection90 columns = input. inputcolumncollection;















Foreach






(Idtsinputcolumn90 item in






Columns)







{







If






(Item. datatype = datatype. dt_wstr | item. datatype = datatype. dt_str)







{







_ Columns. Add (buffermanager. findcolumnbylineageid (input. buffer, item. lineageid ));







}







}







}

Step 3: rewrite the processinput method. This method is called multiple times by data flow tasks. We continue to process the data until the data source can no longer provide data.

Public






Override






Void






Processinput (int






Inputid, pipelinebuffer buffer)







{







While






(Buffer. nextrow () // it can be understood from this sentence. In fact, although it is synchronous, it may also be processed after several rows are buffered, not necessarily one row.







{







Foreach






(Int






Index in






_ Columns)







{







String






STR = buffer. getstring (INDEX );







Buffer. setstring (index, str. toupper ());







}







}







}

. Csharpcode,. csharpcode pre
{
Font-size: small;
Color: black;
Font-family: consolas, "Courier New", courier, monospace;
Background-color: # ffffff;
/* White-space: Pre ;*/
}
. Csharpcode pre {margin: 0em ;}
. Csharpcode. Rem {color: #008000 ;}
. Csharpcode. kwrd {color: # 0000ff ;}
. Csharpcode. Str {color: #006080 ;}
. Csharpcode. Op {color: # partition C0 ;}
. Csharpcode. preproc {color: # cc6633 ;}
. Csharpcode. asp {background-color: # FFFF00 ;}
. Csharpcode. html {color: #800000 ;}
. Csharpcode. ATTR {color: # ff0000 ;}
. Csharpcode. ALT
{
Background-color: # f4f4f4;
Width: 100%;
Margin: 0em;
}
. Csharpcode. lnum {color: #606060 ;}
. Csharpcode,. csharpcode pre
{
Font-size: small;
Color: black;
Font-family: consolas, "Courier New", courier, monospace;
Background-color: # ffffff;
/* White-space: Pre ;*/
}
. Csharpcode pre {margin: 0em ;}
. Csharpcode. Rem {color: #008000 ;}
. Csharpcode. kwrd {color: # 0000ff ;}
. Csharpcode. Str {color: #006080 ;}
. Csharpcode. Op {color: # partition C0 ;}
. Csharpcode. preproc {color: # cc6633 ;}
. Csharpcode. asp {background-color: # FFFF00 ;}
. Csharpcode. html {color: #800000 ;}
. Csharpcode. ATTR {color: # ff0000 ;}
. Csharpcode. ALT
{
Background-color: # f4f4f4;
Width: 100%;
Margin: 0em;
}
. Csharpcode. lnum {color: #606060 ;}

 

Here we only process the input buffer. Note: What is interesting here is that we are actually very simple. First read the value of a column in the buffer, convert it to uppercase, and then write it back.

 

3. recompile and deploy the project. Test in Bi Studio

You only need to check the relevant input columns. Other settings are not required.

Now you can execute the task. If no accident occurs, the task can be successfully executed, as shown in

We finally open the generated text file for verification.

We can see that all the texts in the first and third columns are processed in uppercase.

We can also add a "Viewer" to the data stream. It is clear that the characters in the channel from "folder data source" to "uppercase conversion" are in lower case.

In the pipeline of "uppercase conversion" to "flat object", the characters are converted to uppercase characters.

 

Supplement:

The conversion component is ready to work. However, we can add the following features (such as verification) to make it more complete.

Public






Override






Dtsvalidationstatus validate ()







{







// Verification component, which must have at least one input Column














Idtsinput90 input = componentmetadata. inputcollection [0];







If






(Input. inputcolumncollection. Count> 0)







Return






Dtsvalidationstatus. vs_isvalid;















Componentmetadata. firewarning (0,







Componentmetadata. Name,







"At least one input column should be selected"






,







String






. Empty,







0 );







Return






Dtsvalidationstatus. vs_isbroken;







}















Public






Override






Void






Deleteinput (int






Inputid)







{







Throw






New






Exception ("Cannot delete input"






);







}















Public






Override






Idtscustomproperty90 setinputproperty (int






Inputid,







String






Propertyname, Object






Propertyvalue)







{







Throw






New






Exception ("cannot modify input"






);







}

In this case, if you do not select a column, the following error will occur: