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.

https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-365/help-manual/crm/source

Hope it helps..

Advertisements

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. https://nishantrana.me/2018/09/10/cannot-convert-between-unicode-and-non-unicode-string-data-types-error-in-ssis/

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

The process cannot access the file .ispac because it is being used by another process error in Visual Studio (SSIS).

Recently while developing SSIS packages, the Visual Studio (SSDT) got crashed.

After restarting the Visual Studio and trying to execute the package we got the below error.

System.IO.IOException: The process cannot access the file ‘c:\folder\ssisproject.ispac’ because it is being used by another process.

at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

at System.IO.File.Delete(String path)

at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder. IncrementalBuildThroughObj(IOutputWindow outputWindow)

at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.

BuildIncremental(IOutputWindow outputWindow)

ISPAC file is the integration services project deployment file containing the packages and the parameters.

The fix is to open task manager and end the DtsDebugHost.exe task.

DtsDebugHost

DtsDebugHost.exe is the SSIS Debug runtime process that executes the package.

Hope it helps..

Using Conditional Split Transformation in SSIS

Recently we had a requirement to Upsert Customer’s Spend related information from SQL DB to our CRM Online.

We had the spend captured per brand wise in the DB as shown below.

And in our CRM, we had one custom entity that had fields corresponding to each of these Brand as shown below

i.e. one section for each Brand and inside that section 5 fields corresponding to the columns in the SQL Table.

The GCR column of the table was the Alternate Key column in our custom entity which we used for Upsert.

Now to make sure we update only the corresponding Brand fields in CRM, we can make use of Conditional Split Transformation component.

Just after OLE DB source, add a Conditional Split component

Create one output per brand i.e. (select distinct Brand from the source table records) as shown below

Now add a CRM Destination Component and bind it to the corresponding output

And inside Columns section, map only the corresponding field of the selected Brand.

The finished package à

Hope it helps..

Optimum Batch Size while using SSIS Integration toolkit for Microsoft Dynamics 365

Hi,

https://nishantrana.me/2018/12/19/optimum-batch-size-and-thread-while-deleting-records-using-ssis-integration-toolkit-for-microsoft-dynamics-365/

Recently while working in a data integration task, we were evaluating the performance of using KingswaySoft’s Dynamics 365 Toolkit from within Visual Studio by trying out different possible combinations of batch size and threads.

The environment was CRM Online and had concurrent ExecuteMultiple max limit set to 20.

By default, it is set as 2 for online, but we can raise support ticket with Microsoft to get this limit increased. However, we need be very careful while using them as large number of ExecuteMultiple running in parallel might degrade the performance.

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

Below are the 2 settings that we played around in the CRM Destination Component


These are the results we got with different batch size and thread.

  • Message Used– Upsert.
  • Total Number of Records – 20000.


It clearly shows that having batch size as 100 gives the best result.

And this is what is recommended by KingswaySoft as well.


https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-365/faq

 

Hope it helps..

CRM service call returned an error: The server is busy and the request was not completed. Try again later while inserting records using CRM Destination Component of KingswaySoft’s SSIS Integration Toolkit for Dynamics 365

While running one of our package we got the below error in few of the records

“CRM service call returned an error: CRM service call returned an error: The server is busy and the request was not completed. Try again later. (Error Code: -2147180543, Detail Message: The server is busy and the request was not completed. Try again later.)”

“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.7991956. 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)”

One way to get around this error is to increase the Timeout (secs) in the Service Timeout Setting of CRM Connection Manager.

We updated it to 600 seconds and the number of records that failed got drastically reduced.

Also make sure that following option is checked

Hope it helps..

Lookup not getting set while using CRM Destination Component of KingswaySoft’s SSIS Integration Toolkit for Dynamics 365

Recently while inserting a custom entity (Transaction) records using the CRM Destination Component we realized that Contact’s lookup was not being set.

We were using Alternate Key to set the lookup as shown below

To get it working we had to uncheck the Remove Unresolvable References option from the Optional Settings of CRM Destination Component. It seemed that it ignored it, even though values were there for the GCR (alternate key field) in the input source.

https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-365/help-manual/crm/destination

I think it might not be working as expected in case if lookup is done using Alternate Key.

BTW, I would strongly recommend using KingswaySoft’s SSIS Integration Toolkit for any or all data integration tasks. It is definitely the best tool out there.

Give it a try à

https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-365/download

Hope it helps..

View Designer not showing SSIS Package in Design mode in Visual Studio

Time to time we’d see our Package not opening in Designer mode in Visual Studio.

To fix this,

Add the package again in the solution or restart the Visual Studio.

Hope it helps..