Data Flow Transformations in SSIS

SSIS has various data flow transformation components that make it easy to manipulate the source data before it can be sent to the destination for processing.

Below are some of the most frequently used one

  • Data Conversion

For changing the data type of the source column.

Simply select the input column and then specify the data type for it.

For e.g., if our source has one of the date columns as a string, we can apply Data Conversion transformation to convert its data type to Date.

  • Derived Column

Can be used if we want to apply the expression to the source column and create a new column or update the existing column.

For e.g. we are interested in the Year part of the Modified Date column. For this we can use the Year Date/Time function, get the year part and add it as a new column.

  • Percentage Sampling and Row Sampling

To reduce the number of rows in the pipeline.

Percentage Sampling allows us to define the percentage of rows for sampling. It generates two output, one output for the rows that are selected and the other for the unselected.

Similarly, Rows Sampling allows us to specify the number of rows directly instead of a percentage. It also gives the option to select the columns.

  • Multicast

Multicast can be used to pass the same data to multiple destination as shown below.

Sort transformation can be used to specify sorting for the data. In the case of SQL, we can specify sort in the query itself however in case of working with flat files, this function can be handy.

To perform aggregation on the source data we can use Aggregation transformation.

  • Union All

Union All allows us to combine data from multiple sources. Here the column structure should be identical of the sources. The data from the first source is passed to the destination, followed by the data from the second.

We’d covered some of the common transformation components in this post, in the next post we’d try to cover the remaining frequently used transformation.

Hope it helps..


Stop SSIS Package Execution in SSISDB

Suppose a package has been running for quite a long time or has been mistakenly run and we would like to stop its execution.

To do so

  • Right Click on SSISDB
  • Select All Execution Reports

  • Filter it to see all the Running packages

  • We can see our all the running packages there.

  • Now to stop any specific package, right click SSISDB and select Active Operations

  • From the Active Operation window, select the running job and click on Stop button to stop its execution.

  • Another option is to use the stored procedure à get the operation id of the running package and execute the stored procedure

Exec catalog.stop_operation  @operation_id = 199915

Hope it helps..

Using Alternate Key to set Lookup in SSIS (KingswaySoft)

Alternate Keys which were introduced with CRM 2015 Update 1, gave us the ability to Upsert and Update records, by defining one or more fields as Alternate Key and use that field(s) instead of relying only on Primary Key.

Alternate Keys can also be used for setting or updating the lookup instead of GUID.

CDS\CRM Destination Component in KingswaySoft SSIS Integration Toolkit allows us the option of specifying the Alternate Key for updating the lookup field.

Suppose we have Contact entity with following Alternate Key defined in it

  • Key1 on field last name
  • Key2 on 2 fields emailaddress1 and last name

Within the CDS/CRM Destination Component Editor for the lookup field, click on ellipsis for the Text Lookup column. Here we have selected parent contact field inside Lead Entity.

Inside Text Lookup Editor, select the option “Choose Target Field(s)”, in lookup method select Alternate Key and we will see both our alternate keys listed there.

  • Key1 on field lastname

  • Key 2 on emailaddress 1 and last name

One thing I have observed that it doesn’t list the alternate key if it is defined by using more than 2 fields. Looks like it is by design.

Hope it helps..

Using KingswaySoft’s CDS/CRM Source component to get Audit information in Dynamics 365 CE (SSIS)

Kingsway’s CDS/CRM Source component and has Source Type property having an AuditLogs value that can be used to get the Audit details from Dynamics 365 CE.

Below we have set the Source Type as AuditLogs in the CDS/CRM Source Component Editor and provided the FetchXML for the entity for which we would like to retrieve the audit information.

To get all the audit records, we can run it against Audit entity.

For AuditLogs source type there are 3 types of output:

  1. Audit Details (Attribute Changes) – Contains field level changes.

  1. Audit Details (Relationship Changes) – Contains relationship changes.

  1. Primary Output – Contains entity level audit information.

Hope it helps..

Easily map OptionSet using CRM OptionSet Mapping Editor in SSIS

Just want to share my experience of using KingswaySoft’s OptionSet Mapping Editor and how easy and efficient it makes to migrate data in Microsoft Dynamics 365 CE.

Here our SQL (OLE DB Source) was having following values for Phone Code field. Here we are doing upsert for the Contact entity.

However inside CRM, we were only maintaining or dealing with few of the values as shown below for the corresponding field.

To deal with this situation, inside SSIS Package, drag the Dynamics CRM OptionSet Mapping component.

In General page, specify the input column of the table and for target properties, specify the CRM Connection Manager, Entity and the field to which we are mapping the input to.

Here we need to explicitly define input value and option set value mapping, for each of the option set values for the field inside CRM, because the source doesn’t have the “+” appended to it i.e. unmatched values only.

Here we have defined the mapping

Now for the extra values that source have, we want it to map it to Others option set value inside CRM.

For this in Action Drop-Down select “Replace as an existing option” and in “Replace as” select the Others option set value.

If we want to create the missing input value as an option set value we can use Create Option Action

  • Set as empty will simply ignore the unmatched input values and would pass blank for them.
  • Leave it as is will not do any manipulation and will pass the input values as it is.
  • Raise an error as the name suggests will throw an exception.

Get all the details here

Hope it helps..

Using Diff Detector component for working with incremental data in SSIS (Dynamics 365 CE)

Diff Detector component of KingswaySoft’s SSIS Productivity Pack makes it extremely easy \ efficient to work with incremental data.

Let us take a simple example to understand its usage

Suppose, we have the following data in our SQL Table (source) that we have already moved to our Dynamics 365 Instance.

Inside CRM à

Now let us delete few records say for e.g. 5 from CRM and now we want only those 5 deleted records to be considered for Create by the SSIS Package from the source.

To begin with, first, download the SSIS Productivity Pack

Create the corresponding OLE DB and Dynamics CRM Connection and add the source component for the respective connection, pulling the records from respective sources.

Then drag the Diff Detector component to the Data Flow

Connect the OLE DB Source to Diff Detector and specify the input as the New Data Input as this is the new data that is being entered, the other option is Old Data. Here the Dynamics CRM Source will be used and set as Old Data that is already existing against which the new data needs to be compared.

Once both the source components are connected to Diff Detector, click on Diff Detector component to edit its properties.

Here we need to define the Key Property, which would be used to compare the data.

Here we have specified one of our alternate keys to be used as Key for comparison with match type Exact Match.

Advanced Settings allows us to define a few more properties

In the end, we have added the CRM Destination Component that will be creating the new records in Dynamics CRM.

Here we have selected Added Rows i.e. new rows that weren’t found matching with the old existing data inside CRM will be entered or created in CRM. Similarly, we can get the information \ rows that were changed, deleted or are unchanged and use them as per our need.

This is the current state of our source and destination

Now let us run our package which should detect 5 new rows to be inserted and create the corresponding records in Dynamics CRM.

Similarly, if we have deleted few records (say 5) from our source and want them to be deleted in the destination we can add one more destination component that takes as input the deleted rows and deletes them.

Get all the details here

Hope it helps..

CRM service call returned an error: The top attribute can’t be specified with paging attribute page in SSIS Package

We might face this issue while using Dynamics CRM Source Component of SSIS Integration Toolkit

[Dynamics CRM Source] Warning: An exception has occurred while processing the service request, the same request will be attempted again immediately. KingswaySoft.IntegrationToolkit.DynamicsCrm.CrmServiceException: CRM service call returned an error: The top attribute can’t be specified with paging attribute page (Error Code: -2147220989, Detail Message: The top attribute can’t be specified with paging attribute page)

To fix it à

Remove the top attribute from the FetchXML specified and instead use Max Rows Returned property of the Source Component Editor.

Hope it helps..

CRM service call returned an error: Date is less than the minimum value supported by CrmDateTime. Actual value ‘xyz’- Minimum value supported: 01/01/1753 00:00:00 in SSIS (Dynamics 365)

We got the below while trying to UPSERT contact records from a SQL table to Dynamics 365 CE using Kingswaysoft Dynamics 365 Adapter.

The source table had the Date of birth (DOB) as nvarchar type (string) and one of the rows had the following value stored for DOB à

‘0201-01-09 00:00:00’

Which was resulting into exception while creating record inside Dynamics 365 CE with the above date.

The way we fixed it was to make use of

Derived Column
à Converting empty string to null for DOB column.

Data Conversion
à Converting it to Date data type for comparison.

Conditional Split
à Updating DOB field in CRM for only those records having valid date i.e. greater 01/01/1753 00:00:00 and for records having null or date less than 01/01/1753 00:00:00, removing or not using the DOB field for Upsert.

Derived Column à

Data Conversion à Convert it to Date Type which makes it easy for use in Conditional Split Transformation editor for comparison.

Conditional Split à If the DOB is not null and is greater than 01/01/1753

!ISNULL([Copy of DOB]) && [Copy of DOB] >= (DT_DBDATE)”01/01/1753 00:00:00″

Then update the DOB for the record for Case 1 and in case of the DOB being less than 01/01/1753 and null, do not update the field (don’t map the field for update).

Inside Dynamics CRM Destination component

For Case 1: Map the birthdate field

For default output: Don’t map the field, which will set the field as null as required.

Hope it helps..

Cannot convert between unicode and non-unicode string data types error in SSIS

While using the OLE DB Source component in SSIS, on updating its SQL Command we started getting the below error for “Phone” column in our Table.

The fix is to open the Advanced Editor for the OLE DB Source component

Change the Data Type (non-unicode string) for the Phone column inside Output Columns in Input and Output Properties Tab.

Changing the Data Type from Unicode string [DT_WSTR] to string [DT_STR] fixed the issue.

Hope it helps..

How to – convert a blank string to Null for DateTime field – SSIS

Recently while writing an SSIS package, we were getting the below exception.

System.Exception: Error: Failed to set field ‘birthdate’ value to ” for entity ‘contact’ (field type: DateTime, value type: String). System.FormatException: String was not recognized as a valid DateTime. (SSIS Integration Toolkit for Microsoft Dynamics 365, v10.2.0.6972 – DtsDebugHost, v14.0.3025.39)

We were getting the exception because the source table was having the birth date field as a string with blank value instead of null and in the destination table we had the corresponding mapped field as DateTime type.

The fix is to add a Derived Column with the following expression, which converts the blank string to null à

“TRIM(ColumnName) == “” ? (DT_STR,4,1252)NULL(DT_STR,4,1252) : Columnname”

Hope it helps..