How to – Upload files to File Column / Attribute using SSIS – KingswaySoft (Dynamics 365/ CRM)


Let us take a simple example to understand how we can upload files to the file column through SSIS Package + KingswaySoft’s Integration Toolkit.

Here we have a File Column defined for the Contact Entity.

This is what our final package looks like –

Our SSIS package will pick up the contact’s email, file path, and file name from the CSV file as the source using the Premium Flat File Source component.

The premium derived component adds a new column that reads / stores the binary content of the file specified in excel.

Finally, in CDS Destination we are doing Upsert on contact entity based on the email id field specified in the source excel file.

The mapping –

On running the package, we can see the file uploaded to the corresponding contact records.

To extract attachment from notes-https://nishantrana.me/2021/02/10/extracting-attachments-from-notes-in-dynamics-365-dataverse/

To download files using Download Block Request –https://nishantrana.me/2022/03/14/how-to-download-file-from-file-column-using-initializefileblocksdownload-and-downloadblock-request-dynamics-365-crm-dataverse/

To upload files using Upload Block Request –https://nishantrana.me/2022/03/15/how-to-upload-file-to-file-column-using-initializefileblocksupload-and-uploadblock-request-dynamics-365-crm/

Understand File and Image Data Type –https://nishantrana.me/2021/10/01/using-file-and-image-data-type-in-dataverse-dynamics-365/

Try out the wonderful, feature-rich, and powerful SSIS Integration Toolkit for Dynamics 365 by KingswaySoft –https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-365/download

Check other articles on SSIS plus CRM – https://nishantrana.me/2018/11/26/ssis-and-microsoft-dynamics-365/

Hope it helps..

Advertisements

How to – Download files from File Column / Attribute using SSIS – KingswaySoft (Dynamics 365/ CRM)


Let us take a simple example to understand how we can download files from the file column using SSIS Package + KingswaySoft’s Integration Toolkit.

Here we have a File Column defined for Contact Entity.

Our SSIS package will run on all the contact records and will extract the file from the records to one of the folders in the local machine.

This is what our package’s data flow looks like.

It has just 2 components CDS Source and Premium Derived Column.

CDS Source is connected to the Contact entity.

For Columns, we have selected our file column fields.

Next, we have next added a Primary Derived Column component.

We have used the WriteBinaryComponent function to write the binary content (new_myfile_binarycontent field) to a file.

WriteBinaryContent(“D:\\MyDownloadedFile\\”+[new_myfile_name], new_myfile_binarycontent])

On running the package successfully, we can see the files downloaded at the folder specified.

The result – as we just had 2 contact records out of 106 having file uploaded to it we can see those 2 filed downloaded.

Hope it helps..

Advertisements

Use ExecuteWorkflowAction to call Action and Workflow in CDS Destination KingswaySoft – Dynamics 365 / Dataverse


We can use ExecuteWorkflowAction in the CDS/CRM Destination Component of KingswaySoft’s SSIS Integration Toolkit for Dynamics 365 to execute workflow and custom action.

If there is no Destination Entity defined (left blank), Workflow/Action options will list down all the global action available


On selecting a specific entity/table e.g. lead entity, it is listing down the actions tied to the lead entity and the on-demand workflows created for the lead.

Here new_TestLeadAction is a custom action and Test Lead Workflow On-Demand is an On-Demand Workflow.


Suppose we have the following input and output parameters defined in the custom action.


Within the CRM Destination Component, we can see the input fields listed for mapping along with the LeadId field where it expects the GUID of the lead record to be passed, against which action has to be executed.


Let us create a custom Global Action – Test Global Action and see if it is listed there or not.


We can see the custom global action listed when there is no destination entity selected as we saw earlier.

However, we also get the error that the Entity as input argument is not supported.


We get the same error for the input parameter of type Entity Collection.


For Entity Reference input it works properly and expects Entity Ref and Entity Ref Type to be passed.

We have removed the input parameter of type Entity and Entity Collection and added an output parameter named SampleOutput.

Surprisingly within our SSIS package, we could not figure out to get the output parameter’s value back from the Action.


We asked KingswaySoft Technical Support (support@kingswaysoft.com), and as usual, within a couple of hours the experts there were able to confirm our understanding that output parameters are not supported in the CDS Destination component for now.

Check other posts on KingswaySoft and Dynamics 365 / CRM https://nishantrana.me/2020/10/16/ssis-kingswaysoft-and-dynamics-365/

Hope it helps.

Advertisements

Interactive login option in CDS/CRM Connection Manager in KingswaySoft Dynamics 365 Integration Toolkit


With the new release, the CDS/CRM connection manager adds a new Interactive Login option in the CDS/CRM Connection Manager for Authentication Type as OAuth.

Interactive login allows the user to log in using his account details (to establish the connection with CRM) without the need for registering the application in the Azure Active Directory.

This is supposed to be used only during design time.

Enter User Name and the CDS/CRM URL and click on Test Connection.

The login screen pops up, where we can enter the credentials and sign in.

We’d receive the Test connection succeeded message.

Now we are ready to use the CRM Connection.

Now when we will run the package from within the Visual Studio (SSDT), it will again ask for entering the credentials.

The other option is to use the OAuth Type Password along with default Client App ID and Redirect URL

https://docs.microsoft.com/en-us/powerapps/developer/data-platform/xrm-tooling/use-connection-strings-xrm-tooling-connect

Hope it helps..

Advertisements

How to – improve data migration performance – SSIS & Azure Data Factory (Dataverse / Dynamics 365)


In one of our projects, we were executing SSIS Packages (KingswaySoft’s Dynamics 365 SSIS Integration Toolkit) under Azure-SSIS Integration Runtime in Azure Data Factory.

Check out –

Deploy and run SSIS Package in Azure Data Factory

Deploy and run SSIS Packages that use KingswaySoft’s SSIS Integration Toolkit on Azure Data Factory.

After trying out different combinations, we eventually settled with batch size as 10 and thread as 15.

https://nishantrana.me/2021/06/08/data-migration-optimum-batch-size-and-threads-for-maximum-throughput-microsoft-dataverse-dynamics-365/

Also, we used multiplexing – running the CRM Destination Component under different application users.

To be precise, 4 in our case and we can increase it get further  improvement in the throughput.

And also based on the recommendation of our Microsoft’s Fast Track Architect we raised a Microsoft ticket to increase the number of web servers allocated from 2 to 3.

Below were our findings,

the earlier run was using batch size as 100 and thread as 20 with the number of servers as 2.

On updating the batch size to 10 and thread as 15 and with the number of servers allocated increased to 3, there was a huge performance gain.

Check the table below – 

The above table is sample run in the sandbox environment, during the final run in production we got the number of servers allocated, increased to 6, gaining further improvement.

Also, check out the below blog post to understand about the affinity cookie and its affect on performance, in case if we doing migration using custom code –

https://markcarrington.dev/2021/05/26/improving-bulk-dataverse-performance-with-enableaffinitycookie/

Hope it helps..

Advertisements

Data Migration – Optimum batch size and threads for maximum throughput – Microsoft Dataverse (Dynamics 365)


For one of our projects, we were trying to figure out the optimum batch size and threads while using the CDS/CRM Destination component of KingswaySoft.

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

Now with Service Protection API limits in place, to get the maximum throughput the first point to consider is User Multiplexing.

Nicely explained here à

https://powerplatform.se/fast-data-management-in-a-limited-cds-world/

We are using the Conditional Split component to divide the load among the different CDS Destination Components which are running under different application users (using different CDS Connection Manager)

In the below data flow, we have 5 different CDS Destination components, each using a different Connection Manager configured using different users.

Here we took 50000 records as a sample for ContactCreate operation with 3 and 5
CDS Destination Component.

Below were our findings à

As suggested in the article, batch size as 10 and thread as 16 seem to give the best performance.

The trick is here to use as many threads as possible running under different users.

The CDS Destination component of KingswaySoft handles the server-side throttling automatically, it will follow the instruction and wait before trying again the same request.

On trying with 500 as the batch size, we got the error.

[Dynamics CRM Destination [18]] Error: An error occurred with the following error message: KingswaySoft.IntegrationToolkit.DynamicsCrm.CrmServiceException: CRM service call returned an error: The operation has timed out (Error Type / Reason: Timeout, Detailed Message: The operation has timed out) (SSIS Integration Toolkit for Microsoft Dynamics 365, v20.2.0.3083 – DtsDebugHost, 15.0.2000.128)KingswaySoft.IntegrationToolkit.DynamicsCrm.WebAPI.WebApiServiceException : The operation has timed out (Error Type / Reason: Timeout, Detailed Message: The operation has timed out)System.Net.WebException (Status Reason: Timeout): The operation has timed out”.

So basically try out the different combinations to get the maximum throughput.

Also check à

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

I think below will still be relevant but for CRM On-Premise

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

Batch Size and Copy of Paralleism for Azure Data Factory and Microsoft Dataverse

https://nishantrana.me/2021/05/25/write-batch-size-data-integration-unit-and-degree-of-copy-parallelism-in-azure-data-factory-for-dynamics-crm-365-dataset/

More articles on KingswaySoft and Dynamics 365 / Dataverse

https://nishantrana.me/2018/11/26/ssis-and-microsoft-dynamics-365/

Hope it helps..

Advertisements
%d bloggers like this: