DataMigration: KingswaySoft using Merge Join

Merge Join component is used when we want to merge the data coming from different source or data flow. Merge Join provide us the data as a combine source or single source after merging the data from different sources.

Point to be noted: For using Merge Join it’s mandatory to use Sort component.

Merge Join: Combine two sorted data flows into one using the FULL, LEFT, or INNER join. It’s recommended that during transformation of data flows sorting to be used at their sources.

Figure 1: We can find this component from SSIS toolbox
Figure 2: Using Sort component for Merge Join

Merge Join Editor: Here, we get the ability to specify the type of join we want for merging the data coming from Sort components. Under this editor we can either use Inner Join, Let Outer Join or Full Outer Join.
Under merge join component we get all the fields/attributes which are passed under Sort component from their respective data source.

Now, we need to map the fields coming from different sources and pass through Sort component. If the fields is not marked for Pass Through under Sort then the field won’t populate under Merge Join Editor and mapping would not be possible for that field.

Figure 3: Merge Join Editor

Under Sort Editor we need to check the field under Pass Through column. If the field is unchecked then the field would not populated under Merge Join component editor.

Figure 4: Sort Editor

Example: If the checkbox next to UniqueID is not checked under Pass Through, then it would not populate under Merge Join Component Input Column.

Also, we need to select one field on top of which sorting order would be done.
In my case I selected a field which contains unique data in the entire data flow fields.

We can also decide the Sorting Order for the fields as it should be in ascending or descending order.

Hope this explanation help you!

DataMigration: KingswaySoft – ScriptComponent (Generate new guid)

I had a requirement where I need to generate a new unique id every time when a new record is inserted into the system. In such cases Script Component is very useful.

We can find Script Component under SSIS toolbox.

Now, going to Script Transformation Editor there we need to create a new column where we can create and store the newly generated ID.

Step 1: Click on Inputs and Outputs
Step 2: Click on Add Columns
Step 3: Name the column
Step 4: Select the required data type.

Figure 1: Script Component Editor (Create new column)

Select the datatype as GUID for generating unique ID.

Next, we need to go to Script and hit Edit Script.. button.

Figure 2: Edit Script for writing custom code

This will open a project where we need to write a line of code for generating new GUID.

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
Row.UniqueID = System.Guid.NewGuid();
}

Code for generating new GUID

Go to Input0_ProcessInputRow(Input0Buffer Row) method, under that write a line of code for generation of GUID.

Row.UniqueID = System.Guid.NewGuid();

That’s all we need to do for creating a new GUID, when a record is inserted into the destination system.

Hope this helps you!

DataMigration: KingswaySoft – Optionset Mapping

KingswaySoft provide us a Dynamics CRM OptionSet Mapping component under Common tab which lies inside SSIS toolbox. This component ease the data migration activity.

Figure 1: Optionset Mapping component

In my case, I haven’t used the Dynamics CRM OptionSet Mapping component. Reason the optionset mapping component didn’t populated the attributes which were of int type under Input Column field.

Figure 2: Optionset Mapping Input Column

Therefore we need to used Data Conversion and Derived Column components.

Assuming, Role is the name of option set attribute. So, under Data Conversion transformation editor we need to change the data type from int to string.

Select the Role attribute under Input Column, system will auto populate Copy of attribute name. In our case it will be “Copy of Role”, select the data type as Unicode string[DT_WSTR] and provide the length of the field as per your requirement. This Data Conversion transformation editor can be used for any attribute data type conversion.

Figure 3: Attributes Data type conversion.

Now, coming to Derived Column component. Here we need to create an expression for mapping the optionset label and value.

Figure 4: Mapping Optionset Label and Value

Expression used for OptionSet Mapping, if any of the option value is not found while data transmission then the system would enter “Not Found”.

 Role == 954660008 ? "Participating Person" : Role == 954660004 ? "Overlay Sales" : Role == 954660000 ? "Account Manager" : Role == 954660001 ? "Sales Reporting Manager" : Role == 954660002 ? "Vertical Head" : Role == 954660003 ? "Geo Head" : Role == 954660005 ? "Co-Owners" : Role == 954660006 ? "Service Offering Lead" : Role == 954660007 ? "Horizontal Head" : "Not Found"

There are multiple expression under Derived Column component which are very use full and can be used as needed such as Mathematical Function(like LOG, ROUND, SQUARE etc), String Function (like TRIM, LTRIM, RTRIM, REVERSE, REPLACE, UPPER, LOWER etc), Date/Time Functions (like DAY, MONTH, YEAR, DATEDIFF etc), NULL Functions(like REPLACE NULL, etc), Type Casts, Operators(like ADD, SUBTRACT, MULTIPLY, DIVIDE etc.)

Hope this post helps you!

Create your website with WordPress.com
Get started