Tag: SSIS

Recently we were exploring ways of extracting attachment from notes to a local drive for our case entity. We also wanted to organize it based on Case Number.

For this, we used KingswaySoft’s SSIS components.

Other posts on SSIS and Dynamics 365 –

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

This is how our final package looked like

For CDS Source, we used Source Type as Fetch XML.

And the below Fetch XML to fetch notes details where objecttypecode is incident and isdocument property are true (i.e. has an attachment) and case as the linked entity to get the case ticket number.

Result View –

CDS / CRM Source Component –

Make sure to use the alias for the attributes of the linked entity.

Next drag the Premium Derived Column, here we will use the DecodeBase64 encoding function to decode the file content stored in the documentbody field using as base-64 encoded string.

DT_Image output column will have the binary content of the attachment

Next, we have added another Premium Derived Column, this time to write the binary content to the local drive, using the WriteBinaryContent function.

Click on the ellipsis to specify the expression.

Here we are creating a folder based on the case ticket number and storing the corresponding attachments inside it.

Let us run the package

On successful execution, we can see the folders created with case ticket number and attachments added to it.

Get all the details here

http://www.kingswaysoft.com/blog/2017/03/21/Extracting-CRM-Attachments-with-Ease

Hope it helps..

KingswaySoft provides 3 components specific to Azure Blog Storage

• Azure Blob Connection Manager
• Azure Blob Storage Source
• Azure Blob Storage Destination

Check other blog posts on SSIS

https://nishantrana.me/2020/10/16/ssis-kingswaysoft-and-dynamics-365/

Let us take a simple example where we are extracting \ reading contact data from Dynamics 365 \ CDS and saving it as a text file in Azure Blob Storage.

Create a new SSIS Package, and drag the Dynamics CRM Source component to it.

(Define the connection to Dynamics CRM instance using Dynamics CRM Connection Manager)

We have selected emailaddress1, first name, and the last name attributes.

Next drag an Azure Blob Destination Component, we will first define the connection to Azure Storage first

Navigate to the Access keys in the existing Storage account to get the account name and the key (or create a new Storage account)

Paste the values and test the connection.

Below we have specified the name of the existing Blob Container, the name of the file to be created there along with the format.

The mapping column will have the mappings defined

Let us execute the package

We can see the file created in the Blob storage

We can see it populated with the source columns specified in CRM Source component.

Similarly using the Azure Blog Source component, we can read the files and can use it for any destination component including Dynamics 365 Destination.

Hope it helps..