How to Create Azure SSIS IR to Use Azure File Share & Deploy SSIS Package with Package Configuration in Azure Data Factory

Issue: How to Create Azure SSIS Integration Runtime to use Azure File Share & Deploy SQL Server Integration Services Package with Package Configuration in Azure Data Factory.

In this article, we are going to learn how to create Azure SQL Server Integration Service IR to use Azure File Share and Deploy SSIS package with package configuration in Azure Data Factory, in this article, we will also learn how to create an SSIS package with package deployment mode, how to create a configuration file to use in SSIS package,  How to deploy SSIS Package and configuration files to Azure File Share, and how to create a pipeline to use package saved in Azure Data Factory.

How to create SQL Database and server:

Open your Azure portal and search and click on SQL databases, then click on the + Create button to create a new SQL database.

In the basics tab, select your Azure subscription, select your resource group, name your database, to create a New server click on + New.

 Name your server, create a username and a strong password, select your region and click on ok 

Once our server is created select the compute + storage as per your requirement and then click on Review + Create then click on create.

Once our Database and server are created, go to the server and copy the server name, then go to the SQL Server Management Studio and connect with the server and create some tables which will be used as the source.

Once we are done with our database, open the Visual studio in the file tab click on the new project, select the Integration Services project then name your project and click on ok 

Right Right on the package deployment and select ''convert to package deployment model''.

Next, bring the data flow task, then inside the dataflow task bring the OLEDB source and OLEDB destination and then configure both, then back to the dataflow task, right-click on the package and execute to test the connection.

Click on the variables, and create two variables, which will be used in the connection, one variable will be user, and 2nd will be a password, select the data type, then provide the values, then in the connection manager map the variables in Source DB and also in Destination DB. 

Right-Click on the pane and click on package configuration.

Click on Enable package configuration then click on Add

Select the XML as configuration type, provide the path and click on next.

In objects select the variables that we created earlier, then click on next

Name the configuration and click on finish.

Click on the Build tab then click on Rebuild solution.

Once we are done with our SSIS package, go to the Azure portal and create a storage account, then inside the storage account click on the file share under the Data storage tab, then click on the + File share button to create a new file share.

Name your file share and then click on create and then upload the package file and configuration file from the destination where we have saved these files.

How to Create SSIS IR and connect with SSIs package & configuration in File share storage:

Open the Azure data factory, 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, select your Azure subscription, Select the Storage account name, select the file share, and click on create. 

Once our linked service is created click on Add.

our SSIS package which is on file share is added, click on continue and then click on create.

Go to the Author tab, click on pipelines then click on the new pipeline, find and drag the Execute SSIS package activity, then go to the Settings tab select Azure SSIS IR, provide the package location, provide the package path, provide the configuration path, select the domain, then provide the user name and password, and finally click on Debug to execute the SSIS package.

Video Demo: Create Azure SSIS IR to Use Azure File Share & Deploy SSIS Package with Package Configuration in ADF