DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
The Latest "Software Integration: The Intersection of APIs, Microservices, and Cloud-Based Systems" Trend Report
Get the report
  1. DZone
  2. Testing, Deployment, and Maintenance
  3. Deployment
  4. Run Your Executables in Azure Data Factory

Run Your Executables in Azure Data Factory

In this article, I will describe how to run SSIS packages on an Azure-SSIS IR with a self-hosted integration runtime (self-hosted IR) configured as a proxy.

Muhammad Afzal user avatar by
Muhammad Afzal
·
Nov. 14, 22 · Tutorial
Like (2)
Save
Tweet
Share
3.15K Views

Join the DZone community and get the full member experience.

Join For Free

When migrating SQL Server from on-premises to the cloud, there is always an ask about how to migrate SSIS packages or other ETL jobs to the cloud. Azure Data Factory SSIS Integration Runtime (IR) offers to run SSIS packages in the cloud.

However, in certain circumstances, those ETL jobs or SSIS packages run executables such as .EXE as a part of a workflow or ETL pipelines. It becomes more challenging if you are migrating SQL Server running on-premises to PaaS as there is no access to PaaS compute to run those executables.

In this article, I will describe how to run SQL Server Integration Services (SSIS) packages on an Azure-SSIS Integration Runtime (Azure-SSIS IR) with a self-hosted integration runtime (self-hosted IR) configured as a proxy.

This feature allows us to access data and run tasks such as executables or processes on-premises or on a VM anywhere.

This feature can only be enabled on SSIS Data Flow Task and Execute SQL/Process Tasks for now. You can enable execute process task to run in Self Hosted integration runtime.

SSIS — SHIR Architecture

Below is the high-level architecture used in setting up this environment.

This architecture provisions Azure Data Factory with private endpoints.

  • ADF PEs are created on the subnet shown in red color (e.g. 10.0.2.0/24).
  • Storage account should be set up with a private endpoint and connected to one of the subnets of the Managed Instance vNet.
  • SSIS-IR should be configured with vNet injection to the same subnets (e.g., 10.0.2.0/24). if you are using the express vNet injection method, then you need a dedicated subnet.
  • In this lab environment, the VPN gateway is set up to have on-premises connectivity. setting up a VPN gateway is beyond the scope of this article.
  • Configure inbound firewall rules for the subnets where SSIS-IR is mounted to allow ports 29876 and 29877.
  • Set up Self Hosted Integration Runtime (SHIR) in ADF and install the SHIR in the on-premises machine. SHIR is establishing connectivity with ADF via the VPN connection. In an enterprise environment, this could be an Express Route (ER) connectivity as well.
  • SQL Server Managed Instance (MI) is also part of this topology for data read and write use case examples. However, setting up MI is also beyond the scope of this article.

Setup and Configure Self-Hosted Integration Runtime (SHIR)

Use the following steps to create a self-hosted IR using the Azure Data Factory GUI.

Select Integration runtimes on the left pane, and then select +New.




On the Integration runtime setup page, select Azure, Self-Hosted, and then select Continue.


On the following page, select Self-Hosted to create a Self-Hosted IR, and then select Continue. 


Provide a unique name for your IR. you can also provide an optional description. Then select Create.



This would generate two keys that would be used to configure SHIR. In this article, I’m using Option two for installing integration runtime, which is “Manual Setup.”

Download the runtime as shown below from Option two on the machine where SHIR is going to be configured.

Run the installer to install the SHIR.


Once the installation is complete, on the Register Integration Runtime (Self-hosted) page, provide the key you captured earlier and select Register. 


Note: If you are using a private endpoint for your ADF. Make sure the machine you are installing SHIR must resolve the ADF private endpoint.

 On the New Integration Runtime (Self-hosted) Node page, select Finish. 


The registration process will begin. It may take a few minutes. After the self-hosted integration runtime is registered successfully, you see the following window: 


Click “Launch Configuration Manager”; it should look as shown below.


Visit ADF GUI. Click on Manage → Integration runtime.

Verify the status of SHIR. It should be in the “Running” state, as shown in the below figure.


Setup and Configure SSIS Integration Runtime (SSIS IR) With SHIR as a Proxy

In the following steps, we will create SSIS IR and define SHIR as a proxy created in the earlier steps.

In the ADF GUI, select Integration runtimes on the left pane, and then select +New.




Select Azure-SSIS for Integration runtime setup, as shown in the below figure.

Provide the following:

  1. Name: Name of integration runtime.
  2. Location: Specify the location where integration runtime is created.
  3. Node size: The node size is the size and power of the virtual machine(s) that are running your integration runtime. If you are planning to run many packages in parallel, it is recommended to select high performing tier for that VM. Click Configure performance for the Azure-SSIS Integration Runtime — Azure Data Factory | Microsoft Docs to find out more about configuring SSIS-IR for high performance.
  4. Node Number: adjusts the scalability of the integration runtime. The throughput of the integration runtime is proportional to the AzureSSISNodeNumber. Set the AzureSSISNodeNumber to a small value at first, monitor the throughput of the integration runtime, then adjust the value for your scenario.
  5. Edition/license: Select the appropriate license as per your environment and needs. You can also take advantage of Azure Hybrid Benefits for SQL Server in licensing.
  6. Click Continue.


Note: Make sure you allow the port 29876 and 29877 on the subnet NSG, which you are going to mount the IR.

Now configure vNet injection for SSIS IR.


Select the “Set up Self-Hosted Integration Runtime as a proxy…” check box. Select SHIR from the dropdown created earlier. This also requires a storage link service.

Select your existing Azure Blob Storage linked service or create a new one that references the staging data store to be used when moving data between self-hosted and Azure-SSIS integration runtimes. For this purpose, the connect Via property of the staging storage linked service must be set to “AutoResolveIntegrationRuntime.”

Also, provide a Staging path → Specify the path in your staging data store to be used when moving data between Self-Hosted and Azure-SSIS Integration Runtimes; a default container will be used if unspecified.



Perform the vNet Validation and click “Continue.”

Click “Create” on the summary page. This will provision your SSIS-IR with SHIR acting as a proxy.

Once created successfully, ensure that the Azure SSIS IR and SHIR status shows “Running” in order to use it to run the SSIS packages.

Create a Sample Demo Executable .bat File

Create a batch file in the notepad with the following. Running this .bat file will create a demo.txt file with the contents provided, as shown below.

 
echo 'This is a test from SSIS IR' > demo.txt



Develop a Sample SSIS Package

In the following steps, we will be developing a very simple SSIS package that would call the .bat file created earlier using “Execute Process Task.”

Launch the Visual Studio.

Create a new project, as shown below, by selecting either of the integration services project templates.


Now create a package in VS. 



Select “New SSIS Package” and provide a package name.


Drag “Execute Process Task” from the SSIS Toolbox to the designer panel.



Double-click on “Execute Process Task” and click to the Process tab as shown below.



Specify “Executables” and “WorkingDirectory” as shown below. In my environment following entries were made. Please make changes according to your setup.

Exeutables → Demo.bat

WorkingDirectory → C:\Development\bat


Right Click on Execute Process Task and click Properties.

Set ExecuteOnProxy = True, as shown in the below figure.


Once the package is ready, ensure the package does not have any errors. Right-click on the project and select the Build menu item, as shown below. 

Upload Package to Azure Files

Upload your package to the Azure files. Setting up a storage account and Azure files is beyond the scope of this article. Make sure they are available for the packages to be uploaded.


Desing Pipeline in ADF

Launch ADF GUI.

You can create a pipeline as shown below.


Under the General tab, drag and drop Execute SSIS package as shown below. 


Click on the Settings tab and fill up the following as shown below.

Select the IR created.

Specify Folder, project, and package name.


Click Publish.

Make sure the IR we created earlier is in the “Running” state before we execute the pipeline. Click Manage →Integration runtime in ADF GUI.


Run the pipeline. Click Add Trigger →Trigger Now.


Now click Manage →Pipeline runs.


Click on the pipeline. As you can status shows “Succeeded.”



Verify in the on-prem or VM that the “Execute Process Task” ran via the SHIR. As you can see, the .txt file is created as a result of pipeline execution. 

Conclusion

After setting up self-hosted IR as a proxy for SSIS IR, you can deploy and run your packages to access data and or run any SQL statements. Further, you can run any executables/processes, whether on-premises or on a VM in Azure or anywhere. The pipeline runs as Execute SSIS Package activities in Data Factory.

Executable Information retrieval azure Data (computing) Factory (object-oriented programming) Integration

Published at DZone with permission of Muhammad Afzal. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Integrate AWS Secrets Manager in Spring Boot Application
  • What Are the Different Types of API Testing?
  • A Gentle Introduction to Kubernetes
  • What Is Advertised Kafka Address?

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: