Updated March 14, 2023
Introduction to SSIS in Azure
- SSIS is an abbreviated form of SQL Server Integration Services, which works as a feature pack in Azure and is defined as an add-on that delivers the modules itemized on this page for SSIS to associate to Azure services, handover data amid Azure and on-premises data sources & also the development of data warehousing in Azure.
- This SSIS in Azure is known to be a platform to form initiative level data integration and files renovations clarifications which is intended to explain difficult business complications by replication or moving files, stuffing data warehouses, handling SQL Server objects and data & cleansing and excavating data.
How to Create SSIS in Azure?
Let us view some steps for setting up an Azure integration runtime (IR) in ADF (Azure Data Factory) which provisions:
- Running package installed into SSIS catalog i.e. SSISDB introduced by Azure SQL Database server/Managed Instance i.e. Project Deployment Model.
- Running packages installed into the file system, SQL Server database i.e. MSDB or Azure files introduced by Azure SQL Managed Instance i.e. Package Deployment Model.
After an Azure-SSIS IR set up, Azure aware tools can be implemented to install and execute the packages, which are Azure-enabled already and consists SSDT (SQL Server Data Tools), command-line utilities like AzureDTExec and dtutil & SSMS (SQL Server Management Studio).
For creating an Azure-SSIS IR through the Azure Data Factory app or portal, we need to do some optional jobs mentioned below:
- Configure settings and permissions for Azure SQL Database server having IP firewall rules or organized instance having private endpoint or virtual network service endpoints for hosting SSISDB.
- Add Azure Active Directory i.e. Azure AD, authentication having the definite system or user assigned organized identity applied for data factory to associate to an Azure SQL Database server or even organized instance.
- Design a self-hosted IR being proxy for Azure SSIS IR or connect Azure-SSIS IR to a virtual network for accessing data on-premises.
Few prerequisites for creating Azure are as follows:
- Need to have an Azure subscription.
- SQL Managed Instance or Azure SQL Database server (optional) should be created in the Azure region in the Azure portal as the Integration runtime.
- Must be available with an Azure Resource Manager virtual network which is an optional one.
- You can also execute a PowerShell script for provisioning Azure SSIS IR.
After this Azure integration runtime is configured, one can install and execute packages in Azure directly.
Comparing SSIS and Azure
- SQL Server Integration Service is introduced as a desktop tool requiring a good sized server which needs to be managed and installed SQL Server.
- Azure is known to be a cloud based service and functions as a PaaS tool that does not need any hardware or installation.
- Azure implements JSON scripts intended for its orchestration i.e. coding, while SSIS implements drag and drop jobs i.e.no coding.
- Azure is pay-as-you-go through the Azure subscription option whereas SSIS in Azure is a license cost being as a fragment of SQL Server.
- Azure is able to fire up HDInsights clusters along with running Pig and Hive type scripts. This is also done through the Azure Feature pack for integration services.
- It includes a robust GUI, debugging, and intelligence, while Azure includes a basic editor and has no debugging or intelligence.
- Azure is controlled by means of a portal whereas SSIS is controlled through SSMS.
- SQL Server Integration Service comprises a broad range of data sources plus destinations supported by it.
- Azure is deprived of programming SDK but has automation through Powershell and no third-party modules while SSIS includes a programming SDK, automation y means of BIML, and third-party modules.
- SSIS consists of an error handling option while Azure does not.
- ADF includes ‘Data Lineage’, tracking plus tagging the data available from varied sources whereas SSIS does not possess this.
Migrating SSIS to Azure
On the migration of database workloads to Azure database services, specifically Azure SQL Managed Instance or, Azure SQL Database from SQL Server on-premises then ETL workloads present on SQL Server Integration Service defined as one of the chief value-added services will require to be migrated.
In Azure Data Factory, Azure-SSIS IR (Integration Runtime) provisions executing SSIS packages that apply acquainted tools like SSDT (SQL Server Data Tools) or SSMS (SQL Server Management Studio) and also command line utilities like dtexec/dtutil/dtinstall, for installing and running packages in Azure.
This migration method includes two phases listed below:
- Assessment: For launching a comprehensive migration plan, we need a systematic assessment that helps to recognize issues having the source SSIS packages which would avoid a successful migration. For this purpose DMA (Data Migration Assistant) tool can be downloaded freely, set up, and run locally for evaluating SSIS packages in batches and recognizing compatibility problems which are offered in the below types:
- Migration blockers: It gives compatibility problems that block the migration source packages to execute on Azure-SSIS IR. To resolve this problem DMA supports the user.
- Informative issues: It gives denounced features or partly maintained which are implemented in source packages. DMA offers a complete set of references, substitute methodologies present in Azure and modifying steps to resolve.
Below are the four types of storage for SSIS Packages:
- SSIS catalog i.e. SSISDB, presented with SQL server 2012.
- File system
- MSDB (SQL server system database)
- SSIS package store
- Migration: On the basis of storage types of the SSIS packages source along with the migration destination of the database workloads, the phases to migrate SSIS package and the SQL server agent jobs which plan SSIS package executions may differ. This gives a couple of scenarios:
- Azure SQL Managed Instance functioning as database workload destination
- Azure SQL Database working as database workload destination
This can be said as a practical method for using SSIS DevOps Tools, for performing the batch package redeployment for migrating destination.
Conclusion
- SSIS in terms of an Integration service helps to extract and convert data from a varied selection of sources like XML files, relational data sources, and flat files and then load the files into single or additional destinations.
- It includes graphical tools for designing solutions devoid of writing any piece of code but is able to program object models for creating packages to perform custom tasks.
Recommended Articles
This is a guide to SSIS in Azure. Here we discuss some steps for setting up an Azure-SSIS integration runtime (IR) in ADF (Azure Data Factory). You may also look at the following articles to learn more –
