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

Use Azure Data Factory V2 to load data into Dynamics 365


Let us take a simple example where we will set up an Azure Data Factory instance and use Copy data activity to move data from the Azure SQL database to Dynamics 365.

Login to Azure Portal.

https://portal.azure.com

Search for Data factories

Create a new data factory instance

Once the deployment is successful, click on Go to resource

Inside the data factory click on Author & Monitor

Click on Author in the left navigation

Create a new Pipeline

And drag the Copy data activity to it

Go to the Source tab, and create a new dataset.

Below is our Azure SQL database with contacts table which will be our source here.


Select Azure SQL Database as the source dataset.


Create a new linked service to specify the connection properties.


Specify the details to connect to the Azure SQL Database.


We have selected the contacts table here.


Similarly, let us define a new dataset for Sink which will connect to our Dynamics 365 Instance.



Select the Dynamics data set and specify the linked service.

Specify the details of the Dynamics 365 instance to connect to.

We have selected contact entity as the destination.

Within the Mapping tab, we can specify the fields to be mapped.

Below is how we have specified the mapping.

Click on Validate and after successful validation, click on Debug to run the pipeline.

Within the Output window, we can see the status.

After the successful run, we can see the contact records created inside Dynamics 365.

We can specify a trigger for the pipeline as shown below.

Publish All will publish the changes to the data factory.

Hope it helps..

Failed to get response from server error while trying to connect to Dynamics 365 using linked services – Azure Data Factory


Recently, while trying to connect to Dynamics 365 data set through Linked Service

we got the below error

Seems like a product issue, so the workaround is

Opening Azure Data Factory in a new incognito or in-private mode.

Or

Cancel and do not select the certificate, while testing the connection.

Ignoring the certificate fixed the issue for us.

Hope it helps..