Thanks KingswaySoft

Thanks KingswaySoft for coming up with one of the best tools for Data Integration and especially the SSIS Integration Toolkit for Microsoft Dynamics 365..

https://www.kingswaysoft.com/partners/MVPsMVP_SSIS

Advertisements

Optimum batch size and thread while deleting records using SSIS Integration Toolkit for Microsoft Dynamics 365

Recently we had to delete few records for one of our entities. It was a user owned entity with 1 custom 1 – n relationship with contact having around 190 fields.

It was Dynamics 365 CE Online on version 9.1 and had ExecuteMultiple max limit set to 20. (by raising support ticket with Microsoft)

https://msdn.microsoft.com/en-us/library/microsoft.xrm.sdk.deployment.throttlesettings.executemultipleperorgmaxconnectionsperserver.aspx

Batch Size and the Thread settings were used

Message – Delete

Sample Batch Size – 10000

The result: –

It seems that total thread between 10 – 15 with batch size 50, 100, 250, 500 gives similar results. Batch size of 50 with 15 threads could result into time out issue

https://nishantrana.me/2018/12/18/system-aggregateexception-one-or-more-errors-occurred-ssis-integration-toolkit-for-microsoft-dynamics-365-error-in-kingswaysofts-ssis-integration-toolkit/

So the optimum would be around 100 batch size with thread between 10 to 15.

Check out the below post as well while using UPSERT

https://nishantrana.me/2018/06/04/optimum-batch-size-while-using-ssis-integration-toolkit-for-microsoft-dynamics-365/

Hope it helps..

System.AggregateException: One or more errors occurred. (SSIS Integration Toolkit for Microsoft Dynamics 365) error in KingswaySoft’s SSIS Integration Toolkit

Occasionally we might get the below error in our SSIS Package.

[Dynamics CRM Destination [2]] Error: An error occurred with the following error message: “System.AggregateException: One or more errors occurred. (SSIS Integration Toolkit for Microsoft Dynamics 365, v10.2.0.6982 – DtsDebugHost, v14.0.3002.113)KingswaySoft.IntegrationToolkit.DynamicsCRM.CrmServiceException

: CRM service call returned an error: CRM service call returned an error: The request channel timed out while waiting for a reply after 00:01:59.6189794. Increase the timeout value passed to the call to Request or increase the SendTimeout value on the Binding. The time allotted to this operation may have been a portion of a longer timeout. (Error Type / Reason: Timeout)KingswaySoft.IntegrationToolkit.DynamicsCRM.CrmServiceException

(Status Reason: Timeout): The operation has timed out”.

The way we can fix is either increase the time out in the CDS/CRM Connection Manager as suggested.

However the better solution is to keep the batch size low around 100 or so in the CDS/CRM Destination Component. We have observed that decreasing the batch size helps here in case of this error.

Hope it helps..

CrmRecordId and IsNew Output in CRM/CDS Destination Component

In CRM/CDS Destination Component of SSIS Integration toolkit we can found 2 output column as part of error handling

In case of Create

  • CrmRecordId will have the GUID of the newly created record, which could be used for any further processing
  • IsNew will list whether this is a new record or not.

Our package: –

Suppose we have the following record in our source (here ID is the alternate key column used for Upsert)

If all the records are new and are created we will get the following output.

Now if we delete the Test User 2 and Test User 3 record and run the package again, which should now create 2 records and update one record i.e. Test User 1 during Upsert.

The output would be as below

It will return the GUID of the record irrespective of Update or Create and IsNew will specify whether the record is newly created or updated.

Hope it helps..

The following job steps cannot be reached with the current job step flow logic in SSIS

Recently while scheduling our SSIS Packages in SQL Server Agent Jobs we got the below error

WARNING: The following job steps cannot be reached with the current job step flow logic:

[1] Fashion Spend package

We just need to make sure that we have set correct package in the Start Step properties in Job Properties Dialog Box

To: –

Hope it helps..

Monitoring SSIS package Job Status

Suppose we have scheduled our packages through SQL Server Agent Job and we receive the notification that one of our packages has failed.

To get its details, within SSISDB, we can check the Standard Reports like Integration Services Dashboard report, All Executions etc. to get the details.

This will provide the information about the operation that have run or are currently executing.

We can click on Failed record link to filter the report.

Or we can Filter it through Filter Settings dialog box as shown below

Open up the report for which we want to check the messages and click on View Messages link to get the details.

We can also view these standard reports (All Execution and All Validations) for a specific project as well.

We can also make use of Active Operation Dialog Box to check the status of currently running SSIS packages and stop it if needed.

Hope it helps..