Design a site like this with
Get started

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 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.

Figure 1: Data Format when retrieved from FetchXML

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

Figure 2: Pivot Component
Figure 3: Pivot Editor

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.

Blog\0012\2019  79320 
Blog\0013\201916527050000 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.

Figure 5: Pivot Advance Editor

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
    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.
Figure 6: Pivot Advance Editor

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.

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!

Data Migration using KingswaySoft

KingswaySoft is a tool which helps us in data migration activity.

Why Choose KingswaySoft for data migration?

User friendly UI: KingswaySoft has a very nice and easy to understand user interface. As, I was totally new with KingswaySoft tool. The UI really helped me during the data migration process as I need not struggled much by navigating here and there for searching an option.

Establishing connection between two difference system was pretty easy using connection managers. Not even a single line of code was required. If you have worked before on SSIS then KingswaySoft would be very simple.

Let jump to the scenario where I used Kingswaysoft tool.

I had to migrate date from Microsoft Dynamics 365 to Third Party application SQL(OLE DB Destination) database. In our case CRM was acting as an up stream system.

Figure 1: Data flow diagram.

Attached is the data flow diagram.

We will not go into very basic like how to establish connections using Connection Managers.

Figure 2: Screenshot of Dynamics CRM Source

Build a fetchxml with the required data/columns which needs to be inserted in the destination system. For me to push the desired data in destination I need to use source type as FetchXML.

Use of Data Conversion: This is used for conversion of an attribute data type like if the source system has an attribute of int type and the destination have the same attribute column in nvarchar datatype.

Use of Derived Column: This is where we can use multiple expression on our columns and populated the values in the destination table columns.

Expression which we can use are like:

Mathematical Functions, String Functions, Date/Time Functions, NULL Functions, Type Casts and Operators.

For attribute which are of option set type I needed to convert the int values into string format because the destination table column was of string type. Therefore I used Data Conversion.

Use of Script Component: This was used by me for creating unique id. When a new row gets inserted into destination table then generate a unique guid for each row..

OLE DB Destination: This is where we establish the connection with the destination system database. Mapping the Source System columns with Destination System columns.