December 2019 – Learning Refresh

December 23, 2019December 25, 2019

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.

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.

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!

December 13, 2019December 14, 2019

DataMigration: KingswaySoft using Pivot

Today, we will learn how to transform data into row from columns pattern.

For retrieving data using FetchXML, we may get data in vertical format like we may get multiple row fields. Depending on the entity relationship and data categorization.

Whenever we face such kind of scenario we need to opt for pivot component.

LRRequestID	LR_Split_AMT	LR_name
Blog\0011\2019	86805.56	ATM
Blog\0011\2019	9333.33	DA
Blog\0012\2019	79320	ES
Blog\0013\2019	30288.46	IMS
Blog\0013\2019	165270	ATM
Blog\0013\2019	50000	DA

Figure 1: Data Format when retrieved from FetchXML

I would say that Pivot component basically transform the data to view from different perspective.

For Pivot we will be using same 3 attribute displayed in Figure 1.

LR_name will be our Pivot Key, LR_Split_AMT will be our Pivot Value and LRRequestID will be our Set Key.
Using this LR_name as a pivot key would split its value into different columns in the destination db and LRRequestID unique for each row.

LRRequestID	ATM	DA	ES	IMS
Blog\0011\2019	86805.56	9333.33
Blog\0012\2019			79320
Blog\0013\2019	165270	50000		30288.46

Figure 4: Output result

To achieve the result as shown in figure 4, we also need to perform some additional step.
Right click in the Pivot component and click on Show Advanced Editor option.
Inside the Input and Output Properties we will see the three columns(Pivot Key, Pivot Value and Set Key) attributes in the Input Columns tab.
Now, as per the desired output (Figure 4), we need the LR_Split_AMT to be captured in a single row inside the destination db against LRRequestID. Therefore we will need to take the LineageID of the LR_Split_AMT attribute as show in the below image under blue border.

Post that we need to click on Output columns and hit Add Column button. Where we need to create 4 new columns against each LR_name.

**Points to Remember for all four new collumns:
Name the column as you desire
Under Custom Properties
PivotKeyValue: Name the value in the exactly same manner how its appears when fetched from CRM.
SourceColumn: Provide the LineageID of LR_Split_AMT as explained above.

That’s all about Pivot component.

Lastly, we will need to map the newly created output columns to the destination db columns in their data flow for proper data presentation.

Thank You.. Hope this post helps anyone.

December 9, 2019December 13, 2019

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!

December 8, 2019December 9, 2019

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.

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!