How to Create Azure SSIS IR with MSDB Package Store

 Issue: How to Create Azure SSIS IR with MSDB Package Store.

In this article, we are going to learn how to create Azure SSIS Integration Runtime with the MSDB package store, let's start our demonstration.

How to Create Virtual Network:

Open the Azure portal, then find and click on Virtual Networks, then click on the + Create button to create a new virtual network, then in the basics tab select your Azure subscription then select your resource group, name your virtual network, select your region and then click on Review + create and then create.

Once we are done with our Virtual network, let's create three subnets, the one we will use for our Managed instance, the second one for RDP, and the third one for the Azure data factory, Open your virtual network and inside the virtual network click on the subnets under the settings tab, then click on the + Subnet button to create a new subnet, then name your subnet and click on save.

How to create a Managed instance:

On the Azure portal find and go to the SQL Managed Instance, then click on the + Create button to create a new managed instance, then select your subscription, select your resource group, name your managed instance, then select your region, then provide a unique login name and create a strong password, then click on Next to networking.

Then select the subnet we created earlier for our managed instance, click next to the additional settings, then next to the review + create it will show the summary of your managed instance, and then click on Create.

How to Create a Virtual Machine:

On the Azure portal find and go to the Virtual Machines, then click on the + Create button to create a new virtual machine, then in the basics tab select your Azure subscription, then select your resource group, then name your virtual machine, select your region, then select your required operating system.TBC

Then, create a username and a strong password, then select ''Allow selected ports'', in the inbound ports select '' RDP(3389), then go to the Disks tab.

In the disks tab, select the required OS disk type as per your requirement and then next to the networking tab. 

In the networking tab, select the virtual network, then select the subnet which we have created for our virtual machine and then leave the rest as it is and click on Review + create and then create your virtual machine. 

Once our Virtual machine is created, go to the resource, then go to the connect under the settings tab, download the RDP file, then provide the user name and password,  and then connect with our Virtual machine, then open the server manager and click on the local server and turn off the IE Enhanced security configuration, then download and install the visual studio 2019 from the web then download and install SQL Server Management Studio.

Once our Visual Studio 2019 is installed, open the Visual studio 2019, click on the file, New then click on the project, search for integration, select Integration services project, and click on Next.

Name your project, select the location and then click on create.

Right-click on the project, and convert it to a package deployment model. 

Find and bring the data flow task and then save the package.

Once our managed instance is ready, go to the resource and copy the hostname, then go to the Virtual machine open the SQL Server Management Studio and connect it to the Managed Instance, provide the hostname, select the authentication type, provide the user name and password then click on connect.

Once we are connected with our managed instance, let's create databases and tables, in my case, I have created two databases one named source and the other one named destination, then open the Visual studio, create a new SSIS package, then bring the data flow task inside the data flow then bring the OLEDB source and OLEDB destination and then configure both, and connect with the SQL server database.

Click on OLEDB source, in the connection manager click new, then provide the SQL Server name, select the authentication type, provide the username and password, provide the database name from where you will read the data, test the connection and then click on ok, then select the table and click Ok. 

Once our OLEDB source is ready connect it with the OLEDB destination and click on OLEDB destination, in the connection manager click on new, Provide the server name, select the authentication type, provide the user name and password, select the database name, test the connection click on ok, then select the table name and then click on ok  

Now, go to the Variables tab, click on the new variable button, provide the variable name, select the type, and provide the variable values.

Next, right-click on the source, go to the properties, then click on expressions, it will open a new window, then select password, click on add expressions button, it will open another window then select the variable we created earlier, then click on ok, then follow the same for the destination and then save the SSIS package.

Create a new Azure data factory, then Open the Azure data factory studio, go to the Manage tab, click on integration runtimes, click on the + New button to create an Integration runtime.

Select Azure SSIS then click on continue.

Name your Integration Runtime, select your location, select node size, and number as per your requirement and budget, then click on continue.

Select the deployment settings and then click on the + New button.

Name your package store and click on + new to create a linked service that will be pointing to our package store.

Name your linked service, Select the type, account selection method will be enter manually, provide the fully qualified domain name, select the database,  select authentication type, provide the username and password, then click on create.

Once our linked service is created click on Add.

Our package store is added, click on continue.

Select the maximum parallel execution per node, Checkmark the VNet, select the VNet name, Select the Subnet name, click on continue, then click on Create.

Video Demo: How to Create Azure SSIS IR with MSDB Package Store(Store SSIS Packages in MSDB on Managed Instance)