Using SQL Server Management Studio to deploy and run SSIS package in Azure Data Factory


In our previous post, we created the SSIS Catalog (SSISDB) in Azure and deployed the SSIS package using SSDT.

Supported version for SSDT – SQL Server Data Tools to deploy SSIS package to Azure.

  • For Visual Studio 2017, version 15.3 or later.
  • For Visual Studio 2015, version 17.2 or later.

In this post, we’d use SSMS to deploy the packages in Azure.

Connect to the Azure SQL Server

Expand the Integration Services Catalog, right-click the Projects folder, and select the Deploy Project option.

Enter the source details in the deployment wizard

Select the option SSIS in Azure Data Factory

Select the existing or create a new folder for the project

Click on Deploy after successful validation and review.

Here in our case, it failed with the below message

There is no available node. Please check node status on the monitoring page of the ADF portal and ensure that at least one node is in running 1 and try again. (Microsoft SQL Server, Error: 50000)

The error is because the Azure-SSIS Integration runtime is in the status Stopped.

navigate to your Azure Data Factory instance, and start the runtime.

After around 10 minutes or so the service would be up and running.

This time deployment is successful.

We can see the packages available within the pipeline.

Hope it helps..

Deploy and run SSIS Integration Toolkit for Dynamics 365 on Azure Data Lake (KingswaySoft)


In the previous post, we saw how to deploy and run SSIS packages on the cloud.

Here we take it one step further and will deploy and run the SSIS packages that use KingswaySoft’ s SSIS Integration Toolkit components.

Here we will need an Azure Subscription, where we will host the SSISDB, followed by provisioning Azure-SSIS Integration runtime instance.

We will also need the Azure Blob Storage account along with Azure Storage Explorer to upload the installation files of the SSIS Integration Toolkit.

Let us first start by creating an Azure SQL Server instance.

We have specified the below details.

Now next create the database inside the server.

Now with Azure SQL Server and Database created, the next step is to create the Storage account.

With the Azure Storage created, now let us connect to Azure using the Azure Storage Explorer.

Create a new blob container in the Azure Storage account created.

For the blob container created, right-click and select Get Shared Access Signature

Specify the expiry time along with Write permissions, this is for logging purpose when the Azure-SSIS IR is being provisioned.

Copy the URL (it will be used in the PowerShell script later)

Now let us get the installation files and programs from the KingswaySoft Shared Blob Container, which we’d place in the blob container we just created.

https://kingswaysoftgeneral.blob.core.windows.net/ssis-integration-toolkit-ultimate?st=2019-07-04T16%3A10%3A25Z&se=2059-07-05T16%3A10%3A00Z&sp=rl&sv=2018-03-28&sr=c&sig=LAGvouFpkZHEk%2BH8%2B0pK%2FDNg7B3jPUf%2FJ91%2BJ%2FEeKg0%3D

Right-click Storage Accounts and select Connect to Azure Storage

Select Use a shared access signature (SAS) URI

Paste the KingswaySoft blob container URL.

We can see the below contents added to the blob container.

Select all and copy all the files.

Paste it in the blob container we had created earlier.

With things now setup, let us get the PowerShell script to provision the Azure-SSIS Integration Runtime Initializations.ps1 and update it.

Specify the appropriate values and run the script. Get the Azure PowerShell.

Also, make sure to update the firewall rules to allow the client to connect.

Update the PowerShell Script appropriately

We can check the status as shown below.

In parallel, we can see our Azure Data Factory created with the integration runtime, which is in Starting status.

After a few minutes, we will have integration runtime up and running.

Below is our SSIS Package that we would be deploying to the cloud.

It uses Data Spawner Component to generate test data for Contacts and the CDS Destination component to create those records inside CDS.

Right-click the integration project and select Deploy

Specify connection details along with Path

After successful deployment, let us create a new pipeline inside the Azure Data Factory.

Drag and drop the Execute SSIS Package and click on the Settings tab.

Connect to the package deployed followed by Validate and Debug to test the pipeline.

The pipeline will be in Queued status

After successful execution,

navigate to our Dynamics 365 Sales Hub

We can see 10 contact records created by the SSIS Package.

Hope it helps..

Deploy and run SSIS package in Azure Data Factory


Before the SSIS package can be deployed to Azure Data Factory we need to provision Azure-SQL Server Integration Service (SSIS) runtime (IR) in Azure Data Factory.

In the previous posts, we had created an Azure data factory instance had used Azure SQL Database as the source.

Within Azure Data Factory in the Let’s get started page, select Configure SSIS Integration.

Specify the appropriate values to integration runtime.

Select Create SSIS Catalog option to deploy packages in SSISDB, provide Azure SQL Database server endpoint, and the admin credentials to connect.

Test the connection.

Specify advanced settings as appropriate.

This starts the creation of Azure-SSIS Integration Runtime.

Meanwhile below is our SSIS package that we would be deploying to Azure Data Factory.

It extracts a text file named contacts.txt from the blob source and loads it into destination blog storage.

Right-click the project  and select Deploy.

(Deploying individual package is not supported right now)

Select SSIS in Azure Data Factory.

Specify Server name and credentials and connect.

Click on Browse.

Create a new folder or select an existing folder and click on Ok

Once the validation is successful, click on Deploy and start the deployment.

After successful deployment, create a new pipeline in the Azure Data Factory, and drag the Execute SSIS Package activity

Connect to the package deployed.

Click on debug to trigger and test the pipeline.

On the successful run, we can see the contact.txt file extracted from mycontainer1 and loaded to mycontainer2.

Hope it helps..

Using Data Spawner component (SSIS) to generate sample data in Dynamics 365


At times we need to generate sample data for our entities in Dynamics 365 for various reasons, performance testing is one of them.

Data Spawner component which is part of KingswaySoft’s
SSIS Productivity Pack provides us the most efficient way of doing so.

Download the component here –

https://www.kingswaysoft.com/solutions/ssis-data-generation-anonymization-components/data-spawner-component

Let us generate the sample data for Contact Entity.

Add the Data Spawner component to the Data Flow along with the CDS Destination component in the integration service project.

Double click the Data Spawner to open the editor.

Click on Add + button to specify the columns, here we have specified four different columns.

We have kept the name for each of the columns, same as the schema name so that it is easy to map them in CDS Destination.

For the First Name column, we have specified Data Type as nvarchar and Spawn Type as the First Name, which will generate the string similar to first name value.

In Gender property for the First Name column, we can specify either to generate Male or Female first name.

Random will generate both Male and Female first name.

For the email address field, we have selected Spawn Type as email (personal), the other option is email (business).

For our option set field preferred contact method, we have selected data type as an integer and Spawn Type as Custom, which will allow us to specify the list of available values, which is 1 to 5 in our case.

We have specified the total number of records to be generated as 100000.

Lastly, we have set the output of Data Spawner to the CDS Destination component. (use the Map Unmapped fields to auto map the fields as we have set the column name same as the schema name of the attributes)

Execute the package

We can see our sample contact records start getting created.

Read more about Data
Spawner

https://www.kingswaysoft.com/products/ssis-productivity-pack/help-manual/data-generation-and-anonymization/data-spawner

Here are few other articles on SSIS (KingswaySoft)

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

Hope it helps..

Migration of multi-select option sets in Dynamics 365 – SSIS (KingswaySoft)


Let us take a very simple example to understand how to migrate the multi-select option set in Dynamics 365.

Few points to consider before we use Multi-select option set

https://nishantrana.me/2018/04/27/limitations-of-multi-select-option-sets-in-dynamics-365-ce/

Here we will take excel as our source.

Create a new SSIS package, add the Excel Source component and Dynamics CRM Destination component in the Data Flow.

https://www.kingswaysoft.com/products/ssis-productivity-pack

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

Specify the source file path.

Here Favourite Color column will be mapped to multi-select option set field

Source excel file

The field in CRM

Within the Dynamics CRM Destination, specify the mapping. As the field is of nvarchar type in CRM we need to make sure that we are passing multiple values to it separated by a semicolon.

The way we map the normal option-set field is quite different than the multi-select

https://nishantrana.me/2018/09/20/easily-map-optionset-using-crm-optionset-mapping-editor-in-ssis/

Let us run the package.

After successful execution, we can the records created with the options specified.

Now let us take another scenario, where we have text or label specified instead of values in the source

On successful execution

We can see the records created.

Thus, we can specify either values or labels separated by a semicolon (if multiple) for migration to work seamlessly with the multi-select option set.

We need to make sure there are no spaces after the semicolon else we would get the exception

Error message

To get started with SSIS with Dynamics 365

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

Hope it helps..

Error: The “Derived Column” failed because truncation occurred, and the truncation row disposition on “Derived Column.Outputs[Derived Column Output].Columns[FilePath]” specifies failure on truncation. A truncation error occurred on the specified object of the specified component.


We might get the error while using Derived Column in our SSIS Package

To fix it right click the Derived Column component and select Show Advanced Editor ..

Go to Input and Output Properties tab, expand Derived Column Output and select the field and update the length in the properties window.

Hope this helps..

Fixed – DTS_E_PROCESSINPUTFAILED. The Process input method on component “Script Component” failed with error code 0x80131501 in SSIS


Recently I had written an article for Nigel Frank that list down step by step process of using XML Source Component of KingswaySoft for integration with Dynamics 365 CE

https://www.nigelfrank.com/blog/integrate-dynamics-365-for-customer-engagement-with-xml/

We have used XML Source Component to read the XML files.

The packages that we developed were running fine from Visual Studio SSDT but on deployment for one of our package that was using Script Component we were getting the below error while running from within SQL.

After a lot of debugging and spending a good amount of time to figure out the issue, we realized that the value of one of the input columns was not being passed to Script Component from XML Source component i.e. _ParentKeyField

This is a special column which XML Source Component adds for the child records through which we can associate it to the parent record.

This happens when we are running it from SQL and only for the Script Component. This worked fine while running it from within SSDT and in other packages even from SQL when we weren’t using Script Component.

As a quick fix, we applied XLST Transformation to put the same tag for the child records also and to refer that instead of _ParentKeyField

In parallel, we also asked the same question to the KingswaySoft Support team (info@kingswaysoft.com)

One thing I would like to explicitly mention is that KingswaySoft support team is one of the best out there, not only you will get the response immediately, but also their team is very knowledgeable.

Below is the response we got

As suggested after setting the RunInOptimizedMode property to False for the Data Flow components the issue got fixed.

This property indicates whether the Data Flow task runs in optimized mode (RunInOptimizedMode property). Optimized mode improves performance by removing unused columns, outputs, and components from the data flow.

Hope it helps..

Fixed – The Script Task uses version 15.0 script that is not supported in this release of Integration Services


Recently after deploying the SSIS packages, on one of the packages that uses Script Component, we started getting below error message

This happened because SSIS packages had target version SQL 2017 and the SQL Server version was 2016 (select @@VERSION)

The fix is to update the target version that matches the SQL Server Version.

Right click and go to project properties and update the Target Version to match and redeploy the project.

Hope this helps..

Fixed – Script Component has encountered an exception in user code. Value cannot be null. Parameter name: keyword in SSIS


Recently while trying to use KingswaySoft’ s CRM Connection Manager in the Script Component we got the below error

error

Turned out the issue was because of the wrong version of KingswaySoft.IntegrationToolkit.DynamicsCrm assembly being referred.

C:\Windows\Microsoft.NET\assembly\GAC_MSIL\KingswaySoft.IntegrationToolkit.DynamicsCrm

Referencing the correct one fixed it.

Hope it helps..

Check out my Step-by-Step guide to integrating Dynamics 365 for Customer Engagement with XML @NigelFrank


Here’s an article I wrote for @NigelFrank detailing how Dynamics 365 for Customer Service users can integrate their solution with XML. I hope you find it useful! #MSDyn365 #MsDynCRM

Integration with XML for Dynamics 365 for Customer Service users: my illustrated guide for @NigelFrank

Follow the link here

https://www.nigelfrank.com/blog/integrate-dynamics-365-for-customer-engagement-with-xml/

 

User Defined Variable value not getting updated inside Data Flow in SSIS


While implementing integration packages between XML and Dynamics 365 CE, we had a requirement which required us to write a Script Component (custom code) in Data Flow task to check the status of a record in CRM and based on the value of it take appropriate action.

The initial thought was to create a user-defined variable and update the variable in the Script Component and use its value inside a new Derived Column and further in the CRM Destination Component.

However, to our surprise, although we can see the variable’s value being updated within Script Component while debugging, the same was not being reflected in the Derived Column and the Destination Component in that Data Flow task.

But within the next Script task of that Control Flow, we were getting the updated value.

Below is our sample source XML File

The package

The data flow task reads the above XML file and within Script Component it sets the value of fullname variable and we are putting its value within a Derived Column. SQL Server Destination is inserting all the values into a SQL table.

The Script Component à

The Derived Column à

The SQL Server Destination à

On running the package, the result à

It is still showing the default value.

Although we can see the value correctly updated

Now within the Script Task in the control flow, we are using a message box to show the value of that variable.

The result à

The reason for the above problem is SSIS package variables does not update during the execution of the data flow task. Those variables get updated once the Data flow task finishes. Therefore, we cannot use the updated value of the variable during the execution of a dataflow task.

Even though you could change the variable values within pre-execution or post-execution methods, it only takes effect on the value of the variable after the execution of the data flow task.

http://dineshpathirana.blogspot.com/2011/05/ssis-package-variables-cannot-update.html

So here instead of using the User Defined Variable, we used the Output column of the Script Component.

And we set the same inside the script editor.

Hope it helps.

Using Precedence Constraint Editor to control the task execution in Control Flow (SSIS)


Precedence Constraint component in SSIS allows us to control the flow of the execution of tasks within the Control Flow.

Suppose we have the below package wherein we are traversing through the folder using Foreach Loop Container and are processing XML and docx files within that folder.

And we want them to be processed differently.

We have the following user-defined variables specified.

Within the Script Task, we have specified FilePath as the ReadOnlyVariables to read the full file path and figure out the extension and FileExtension as the ReadWriteVariables to which we will write the result.

Below is our code for the Script Task, here we are reading the file path and getting the extension and setting the FileExtension variable.

Now back in our control flow, open the Precedence Constraint Editor and select Evaluation expression as Expression and Constraint.

The constraint should be success and for the expression, we have defined the FileExtension variable’s value to be .docx, and both the condition to be true i.e. Logical And.

Similarly, define the Precedence Constraint for the second brand of the Script Task as shown below.

Now run the package and based on the file extension we would see the execution taking the appropriate path, processing the next component and showing the correct message.

Hope it helps..

Using Foreach Loop Container in SSIS to loop through all files in folders and subfolders


Recently while working on an integration requirement we had to loop through all the XML Files within a folder and subfolder and create corresponding records inside Dynamics 365 CE.

For this, we used the XML Source component which is part of KingswaySoft’s Productivity Pack https://www.kingswaysoft.com/products/ssis-productivity-pack/help-manual/json-and-xml-processing/xml-source

Below are the steps we need to perform to loop through the files.

Create an SSIS Project and drag the Foreach Loop Container to the control flow.

Here we are using 3 variables, one for the full path to the file, the other for extension and the third one to specify the folder.

Double click and the Foreach Loop Editor, set the enumerator as Foreach File Enumerator and set the Directory and FileSpec property expressions to use the variables that we had defined.

Check the Traverse Subfolder if we want to traverse the subfolders.

Click on Variable Mapping and set the FilePath variable that will be used to iterate through the files in the Folder and Subfolder specified.

To see it in action, we have added a Script Task inside the container.

Here we have set the FilePath variable as the ReadOnlyVariables that we will read within the Script Task code and show it in the Message Box.

Our source code that reads the FilePath and displays it in the Message Box.

Now just execute the package and we will see it looping through all the XML files with the folder specified.


Hope it helps..

Using CDS/CRM Connection Manager of KingswaySoft’s SSIS Dynamics 365 Integration toolkit in Script Component (SSIS)


Recently for one of our requirements we had to use the CDS/CRM Connection of KingswaySoft’s SSIS Integration Toolkit for Dynamics 365

Below are the steps to be followed –

Drag the Script Transformation component, specify any input columns and inputs / outputs as required.

In the Connection Managers add the connection to Dynamics 365

Click on Edit Script to open the Visual Studio.

Add references to below 3 assemblies. We need to search for the 2 KingswaySoft assemblies in the machine and then add them.

The sample code –


public override void Input0_ProcessInputRow(Input0Buffer Row)
{
var connMgr = this.Connections.CRMConnectionManager;
var connectionString = (string)connMgr.AcquireConnection(null);
var conn = new CrmConnection(connectionString);

var orgService = (IOrganizationService)conn.GetCrmService();

RetrieveRequest retRequest = new RetrieveRequest();
retRequest.ColumnSet = new ColumnSet();
retRequest.ColumnSet.AddColumn("statuscode");
retRequest.Target = new EntityReference("dxc_contract", "dxc_contractnumberinternal", Row.dxccontractnumberinternal);
var response = (RetrieveResponse)orgService.Execute(retRequest);

if (response.Entity != null)
{
// status is closed
if (((OptionSetValue)response.Entity.Attributes["statuscode"]).Value == 282210002)
{
Row.IsApprovedContract = "N";
}
else
{
Row.IsApprovedContract = "Y";
}
}
}

Get all the details here

http://www.kingswaysoft.com/blog/2013/06/24/Writing-Script-Component-or-Script-Task-using-CRM-Connection-Manager

Hope it helps..

Scribe Insight Limitation – XML Source doesn’t support repeating sibling nodes


Recently we were evaluating Scribe Insight as our Integration tool for one of our clients. The requirement was to read an xml and create records in Dynamics 365 CE.

The basic structure was something like below, with Account as the parent tag and Payment, Assets, Consumer as repeating child nodes of Account i.e. 1 – n.

However, to our surprise when we started defining the same structure in the XML Component within Scribe Workbench we got the below error message à

—————————

XML Component

—————————

Repeating nodes are not supported for cousins, siblings, etc. Multiple repeating nodes must be related linearly to repeating nodes that already exist. Thus, a new repeating node must be a grandparent, parent, child, grandchild, etc., to an existing repeating node.

—————————

OK

—————————

Interestingly enough the same limitation doesn’t exist in Scribe Online. Had posted something similar few days’ back on that

https://nishantrana.me/2019/01/14/scribe-online-working-with-hierarchical-data-in-dynamics-365-ce/

As we wanted to go with something that works in On Premise setup we went ahead with SSIS Productivity Pack of KingswaySoft https://www.kingswaysoft.com/products/ssis-productivity-pack which had XML components that works perfectly well with this kind of schema or structure.

Hope it helps..

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

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

Solved – Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException: The version of the Script Component is not compatible with this version of the DataFlow


While trying to run one of our SSIS Packages from SQL Server Job, which had script component in it, we got the below error. It was running fine within the SSDT in our Dev Machine. In fact, the other packages deployed to SSISDB were also running fine, the ones which were not using the Script Component.

To fix it, we updated the project version of SSDT to match the SQL Server where we were deploying the package inside Project Properties.

To find the SQL Server version.

And deployed
only that package instead of the project in SSISDB.

However, on running the package from within the SQL Server again, threw the same error. We tried a few other things like opening the same package in SSDT in the Server and then try deploying that particular package from there. We also tried by deleting the existing the script component in the package and using the script component that was available in the toolbox in SSDT in the Server.

The package clearly showed the difference in the version for the Script Component. However again deploying that single updated package gave the same mismatch exception.

Eventually, we then deployed the Project (after updating the Target SQL Server version to the Server’s SQL version) and not the individual package. And it ran successfully this time.

So basically we need to make sure our target version is correct and deploy the entire Project to fix this issue in our case.

Hope it helps..

Deploying Package to SQL Server Integration Services Catalog (SSISDB) from Visual Studio (SSDT)


Deploying packages to SQL Server from SSDT is straightforward. We can either deploy the project or an individual SSIS Package i.e. Project Deployment or Package Deployment. (SQL Server 2016 Onwards).

Here we will see the package deployment.

Right-click the package that we would like to deploy and select Deploy Package.

This opens the Integration Services Deployment Wizard

Click on Next and Specify Server Name and the credentials to connect to the SQL Server an click on Connect.

For the Path specify the existing Project or create a new Project in SSID where we would like to deploy our packages.

Review the details and if all the information is correct, click on Deploy.

The result page would show the status of the deployment.

Inside Integration Service Catalogs we can see our Package Deployed.

Another option for deploying the package is through the Deploy Project option of the Project inside SQL Server Management Studio’s SSIDB node.

It will open the Integration Service Deployment Wizard.

And after selecting the Package folder we can click on Next and follow the wizard as we had done earlier to deploy the package.

We can also run the Wizard directly from Windows Explorer or command prompt – IsDeploymentWizard.exe

Through Stored Procedure à

catalog.deploy_packages

[catalog].[deploy_packages] [ @folder_name = ] folder_name, [ @project_name = ] project_name, [ @packages_table = ] packages_table, [ @operation_id OUTPUT ] operation_id OUTPUT ]

Through Management Object Model API.

Hope it helps..

Audit Information not getting retrieved using SSIS/KingswaySoft Adapter


Update – 29 Oct 2018 –-> We are getting the same issue in one of the entities and it has audit enabled. We have a raised a support ticket for it. Will update the post with the solution.

A few days back I wrote about how we can use CDS/CRM Source component of KingswaysSoft Adapter to get the audit information.

https://nishantrana.me/2018/10/08/using-kingswaysofts-cds-crm-source-component-to-get-audit-information-in-dynamics-365-ce-ssis/

Recently while writing a package for getting audit details against one of the entities, we realized that the records were not getting retrieved and also, we were not getting any error or exception. The package kept on running.

Setting timeout in the CRM Connection Manager also didn’t stop the package, it kept running without retrieving the records.

After spending a good amount of time, we realized the silly mistake that we had made. We were running the package against the entity for which Audit was not enabled.

Thanks to KingswaySoft Support team for their relentless support.

Ideally, we should have been more careful before running the package against that entity, and also it will be helpful if the tool can also check if the audit is enabled for an entity before beggning the execution and if the audit is not enabled inform back the user or at least return 0 rows and complete its execution successfully.

Hope it helps..

The collection of variables locked for read and write access is not available outside of PostExecute error in SSIS


We might get this error while trying to access a ReadWrite variable within Script Component outside Post Execute.

Here we were using it within ProcessInputRow method

As per the official documentation – the reason for this error is because ReadWriteVariables are only available inside PostExecute method for improved performance and minimizing locking.

https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-scripting/data-flow-script-component/using-variables-in-the-script-component?view=sql-server-2017

Updating the code to use PostExecute method fixed the issue for us.

Hope it helps..

Using OverriddenCreatedOn or Record Created On field to update Created On field in Dynamics 365


While working in data migration project, when creating records in the target system, we would want the “created on” field to hold the original value instead of it being set to the actual value when it was created in the target system, which is set by the system or the platform itself.

Suppose we are having the “created on” field in our source SQL Server Table and we are creating the lead records in our target Dynamics 365 organization using KingswaySoft’s CRM Destination Component.

Here we have mapped the CreatedDate of our source table to overrriddecnreatedon field of Lead entity.

On executing the package, back inside Dynamics 365, we can see the following values for Created On and Record Created On (overrriddecnreatedon) field for the lead records.

Record created on will have the time when the record was created in Dynamics 365 and Created On field will have the values that we passed from our source SQL Table.

Get all the details here

https://blogs.msdn.microsoft.com/emeadcrmsupport/2012/08/01/the-truth-about-override-created-on-or-created-by-for-records-during-data-import/

Hope it helps..

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