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] [ @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.

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.

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

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

Using KingswaySoft’s CDS/CRM Source component to get Audit information in Dynamics 365 CE (SSIS)

Kingsway’s CDS/CRM Source component and has Source Type property having an AuditLogs value that can be used to get the Audit details from Dynamics 365 CE.

Below we have set the Source Type as AuditLogs in the CDS/CRM Source Component Editor and provided the FetchXML for the entity for which we would like to retrieve the audit information.

To get all the audit records, we can run it against Audit entity.

For AuditLogs source type there are 3 types of output:

  1. Audit Details (Attribute Changes) – Contains field level changes.

  1. Audit Details (Relationship Changes) – Contains relationship changes.

  1. Primary Output – Contains entity level audit information.

Hope it helps..

Easily map OptionSet using CRM OptionSet Mapping Editor in SSIS

Just want to share my experience of using KingswaySoft’s OptionSet Mapping Editor and how easy and efficient it makes to migrate data in Microsoft Dynamics 365 CE.

Here our SQL (OLE DB Source) was having following values for Phone Code field. Here we are doing upsert for the Contact entity.

However inside CRM, we were only maintaining or dealing with few of the values as shown below for the corresponding field.

To deal with this situation, inside SSIS Package, drag the Dynamics CRM OptionSet Mapping component.

In General page, specify the input column of the table and for target properties, specify the CRM Connection Manager, Entity and the field to which we are mapping the input to.

Here we need to explicitly define input value and option set value mapping, for each of the option set values for the field inside CRM, because the source doesn’t have the “+” appended to it i.e. unmatched values only.

Here we have defined the mapping

Now for the extra values that source have, we want it to map it to Others option set value inside CRM.

For this in Action Drop-Down select “Replace as an existing option” and in “Replace as” select the Others option set value.

If we want to create the missing input value as an option set value we can use Create Option Action

  • Set as empty will simply ignore the unmatched input values and would pass blank for them.
  • Leave it as is will not do any manipulation and will pass the input values as it is.
  • Raise an error as the name suggests will throw an exception.

Get all the details here

Hope it helps..

Using Diff Detector component for working with incremental data in SSIS (Dynamics 365 CE)

Diff Detector component of KingswaySoft’s SSIS Productivity Pack makes it extremely easy \ efficient to work with incremental data.

Let us take a simple example to understand its usage

Suppose, we have the following data in our SQL Table (source) that we have already moved to our Dynamics 365 Instance.

Inside CRM à

Now let us delete few records say for e.g. 5 from CRM and now we want only those 5 deleted records to be considered for Create by the SSIS Package from the source.

To begin with, first, download the SSIS Productivity Pack

Create the corresponding OLE DB and Dynamics CRM Connection and add the source component for the respective connection, pulling the records from respective sources.

Then drag the Diff Detector component to the Data Flow

Connect the OLE DB Source to Diff Detector and specify the input as the New Data Input as this is the new data that is being entered, the other option is Old Data. Here the Dynamics CRM Source will be used and set as Old Data that is already existing against which the new data needs to be compared.

Once both the source components are connected to Diff Detector, click on Diff Detector component to edit its properties.

Here we need to define the Key Property, which would be used to compare the data.

Here we have specified one of our alternate keys to be used as Key for comparison with match type Exact Match.

Advanced Settings allows us to define a few more properties

In the end, we have added the CRM Destination Component that will be creating the new records in Dynamics CRM.

Here we have selected Added Rows i.e. new rows that weren’t found matching with the old existing data inside CRM will be entered or created in CRM. Similarly, we can get the information \ rows that were changed, deleted or are unchanged and use them as per our need.

This is the current state of our source and destination

Now let us run our package which should detect 5 new rows to be inserted and create the corresponding records in Dynamics CRM.

Similarly, if we have deleted few records (say 5) from our source and want them to be deleted in the destination we can add one more destination component that takes as input the deleted rows and deletes them.

Get all the details here

Hope it helps..