SQL Server Integration Services (SSIS) Blog articles

SQL Server Integration Services Projects 2022+ GA

Chunhua — Thu, 02 Apr 2026 02:11:27 GMT

We're pleased to announce the General Availability release of SQL Server Integration Services (SSIS) Projects 2022+, bringing full support for SQL Server 2025 and Visual Studio 2026.

With this release, SSIS Projects now supports target server versions from SQL Server 2017 through SQL Server 2025 and works with both Visual Studio 2022 and Visual Studio 2026.

Download

SQL Server Integration Services Projects 2022+ on Visual Studio Marketplace

Version: 2.2
Build Version: 17.0.1010.6
Release Date: April 1, 2026

For detailed release notes, see the Marketplace release notes. For troubleshooting and offline installation, visit the SSIS in VS2022+ troubleshooting guide.

Share your feedback

Visual Studio Marketplace Q&A

Invoke SSIS Package Activity in Microsoft Fabric (Preview)

Chunhua — Fri, 27 Mar 2026 08:22:23 GMT

SQL Server Integration Services (SSIS) has been a cornerstone of enterprise data integration for decades, powering mission-critical ETL workloads across thousands of organizations worldwide. Now Invoke SSIS Package activity (Preview) in Data Factory in Microsoft Fabric, brings the power of your existing SSIS investments directly into Fabric's unified SaaS analytics platform.

Many enterprises have significant investments in SSIS packages that orchestrate complex ETL workflows across on-premises databases, file systems, and cloud services. Until now, running these packages required either an on-premises SQL Server or the Azure-SSIS Integration Runtime in Azure Data Factory. Both options meant managing additional infrastructure and staying outside the Fabric ecosystem.

But the Invoke SSIS Package pipeline activity in Microsoft Fabric Data Factory changes this. It allows you to execute your existing SSIS packages directly from a Fabric pipeline, enabling true lift-and-shift of legacy ETL workloads into Fabric — no package rewrite required. No integration runtime management, no stop/starting IRs— just add it to your pipeline!

Key benefits include:

Zero rewrite migration: Run your existing SSIS packages as-is inside Fabric pipelines. Protect years of ETL investment while moving to a modern platform.
Unified orchestration: Combine SSIS package execution with Fabric-native activities — Copy activity, Dataflow Gen2, Notebook, Stored Procedure, and more — in a single pipeline.
OneLake integration: SSIS packages running in Fabric can leverage OneLake as package store and write package execution logs into OneLake.
Simplified management: No need to provision or manage separate integration runtimes. Fabric handles the compute for executing your SSIS packages.

Getting started

Getting started with the Invoke SSIS Package activity takes just a few steps:

Upload packages to OneLake — Move your .dtsx (and optional .dtsConfig) files into a Lakehouse via OneLake file explorer or the Fabric portal.
Add the activity to a pipeline — Create or open a new pipeline, then add the Invoke SSIS Package activity from the Activities pane.

Figure: Add an Invoke SSIS Package activity.

Configure package settings — Browse to select your package and configuration files from OneLake and optionally enable logging to capture execution logs in OneLake.

Figure: Invoke SSIS package activity configuration.

Set runtime values — Override connection manager properties or package properties (such as connection strings and credentials) using the Connection Managers and Property Overrides tabs. You can use expressions, pipeline parameters, or system variables for dynamic values.
Run or schedule — Save the pipeline and run it immediately or set up a recurring schedule.
Monitor execution — Track progress in the pipeline Output tab or the workspace Monitor hub. When logging is enabled, detailed execution logs are written to OneLake.

For full configuration details, refer to the Invoke SSIS Package activity documentation.

Get started

The Invoke SSIS Package activity (Preview) for all Fabric workspaces.

Open Data Factory in Microsoft Fabric.
Create a new Data Pipeline in your workspace.
Add the Invoke SSIS Package activity and configure it to point to your packages in OneLake.
Refer to the Invoke SSIS Package activity documentation for detailed configuration guidance.
Refer to Tutorial: Integrate SSIS with SQL database in Microsoft Fabric for detailed configuration to connect with Fabric SQL database in SSIS package.

We want to hear from you! Share your experience, report issues, and request features.

What's next

This preview represents the first step in a broader set of capabilities we’re building. Today’s experience focuses on core scenarios, with additional investments already underway — including expanded package sources, on-premises and private network connectivity, and custom or third-party component support. We’ll continue to evolve the platform as we incorporate feedback and deliver these capabilities over time.

These upcoming features will roll out through a series of private previews. If you'd like early access to help shape the future of SSIS in Fabric, sign up to join our upcoming private previews. Your feedback is invaluable in driving this experience toward general availability.

Learn more

SQL Server Integration Services 2025 General Availability

Chunhua — Mon, 24 Nov 2025 00:22:22 GMT

Alongside with the general availability of SQL Server 2025, SQL Server Integration Services is now generally available.

Enterprise security

Microsoft SqlClient Data Provider Support

Microsoft.Data.SqlClient offers a modern, secure, and future-ready data access solution for SQL Server and Azure SQL. It supports advanced security protocols like TLS 1.3 for encrypted connections, integrates seamlessly with Microsoft Entra ID for robust authentication, and is optimized for high performance and cross-platform compatibility on .NET Framework, .NET Core, and .NET 8+.

Learn more about What's New in Integration Services in SQL Server 2025.

Security remains a priority in enterprise strategy. SQL Server’s Strict Encryption, introduced with TDS 8.0 in 2022 and improved in 2025, now enforces default encryption for all network traffic. Integration Services support will be included in the upcoming CU.

Integration Services features in SQL Server Management Studio (SSMS IS)

SQL Server Management Studio (SSMS) 22 is generally available, supporting SQL Server Integration Services 2025.

SQL Server Integration Services Projects

SQL Server Integration Services Projects 2022+ v2.0 (Preview) is available, supporting SQL Server Integration Services 2025 and Visual Studio 2026. A new version addressing known issues is coming soon.

Learn More

SQL Server 2025 is Now Generally Available | Microsoft Community Hub

What's New in Integration Services in SQL Server 2025 - SQL Server Integration Services (SSIS) | Microsoft Learn

SQL Server Management Studio 22 (GA) supporting SQL Server Integration Services (SSIS) | Microsoft Community Hub

SQL Server Integration Services Projects 2022+

SQL Server Management Studio 22 (GA) supporting SQL Server Integration Services (SSIS)

Chunhua — Tue, 18 Nov 2025 23:28:17 GMT

SQL Server Management Studio 22 generally availability has been released. SSMS 22 GA version is recommended to use with Integration Services, as Integration Services 2025 is better supported and known issues from previous versions have been resolved.

You can install SQL Server Integration Services by selecting Modify in the Visual Studio Installer, adding the Business Intelligence workload, or from the Individual components tab if you only want SSIS. If you are updating a previously installed version of SSMS 22, after the update has finished installing you will need to go back to the Visual Studio Installer and add the SSIS component from the Modify workflow. For further details, refer to Install SQL Server Management Studio.

Known limitation

Starting with SQL Server Management Studio 21 and SQL Server 2025, the Import and Export Wizard only support a 64-bit environment. For further details, refer to Import from Excel or Export to Excel with SSIS.
SSIS Integration Runtime creation wizard is not supported. As the workaround, use Azure Data Factory portal to Create an Azure-SSIS integration runtime.

Learn More

SQL Server Management Studio (SSMS) 22 is now Generally Available (GA) | Microsoft Community Hub

Release Notes for SQL Server Management Studio (SSMS) | Microsoft Learn

Known Issues in SQL Server Management Studio | Microsoft Learn

SQL Server Integration Services Projects 2022+

Chunhua — Thu, 16 Oct 2025 01:00:31 GMT

SQL Server Integration Services Projects 2022+ preview is now available, supporting SQL Server 2025 Preview and Visual Studio 2026 Preview. You can get the latest download from SQL Server Integration Services Projects 2022+ - Visual Studio Marketplace.

Highlights of the release

Support SQL Server 2025 Preview
Support Visual Studio 2026 Preview
Support Microsoft SqlClient Data Provider in ADO.NET connection manager for SQL Server 2025, which provides more Microsoft Entra ID-based authentication options. Learn more about using Microsoft SqlClient Data Providerwith ADO.NET connection manager.
Installation troubleshooting is updated and VSInstallationTroubleShooting.ps1 is released to debug installation issues might occur. Visit documentation page for comprehensive troubleshooting guidance.

If you experience any issue or would like to share your feedback, please visit SSIS extension in marketplace Q&A tab.

Learn More

SQL Server Integration Services Projects 2022+ - Visual Studio Marketplace

SSIS Projects Extension for Visual Studio 2022+ Troubleshooting Guide

SQL Server 2025 Integration Services Public Preview | Microsoft Community Hub

SQL Server Management Studio 22 Preview now supports SQL Server Integration Services (SSIS)

Chunhua — Thu, 16 Oct 2025 00:19:39 GMT

Alongside the Release of SQL Server Management Studio 22 Preview 3, SQL Server Integration Services (SSIS) is now available for installation. Integration Services 2025 Preview is better supported release and Legacy Integration Services Service is supported by SSMS 22 release.

Known Issues

Starting with SQL Server Management Studio 21 and SQL Server 2025, the Import and Export Wizard only support a 64-bit environment. For further details, refer to Import from Excel or Export to Excel with SSIS.
Using the Execute Package Utility (dtexecui) to run Integration Packages with Script Task fails. Manually download and install Microsoft Visual Studio Tools for Applications 2022 first before running package execution to work around this issue.
Maintenance Tasks (Including Back Up Database Task, Check Database Integrity Task, Execute SQL Server Agent Job Task, Execute T-SQL Statement Task, History Cleanup Task, and Maintenance Cleanup Task) are not supported by Execute Package Utility (dtexecui). This issue will be fixed in the coming release.
SSIS Integration Runtime creation wizard is not supported. As the workaround, use Azure Data Factory portal to Create an Azure-SSIS integration runtime.

Learn More

Announcing the Release of SQL Server Management Studio 22 Preview 3 | Microsoft Community Hub

Release Notes for SQL Server Management Studio (SSMS) 22 Preview

Known Issues in SQL Server Management Studio (SSMS) 22 Preview

SQL Server Management Studio 21 now supports SQL Server Integration Services (SSIS)

Chunhua — Thu, 23 Oct 2025 23:47:49 GMT

With the release of SQL Server Management Studio (SSMS) 21.2.5, SQL Server Integration Services (SSIS) is now available in Preview. You can install it by selecting Modify in the Visual Studio Installer, adding the Business Intelligence workload, or from the Individual components tab if you only want SSIS. If you are updating a previously installed version of SSMS 21, after the update has finished installing you will need to go back to the Visual Studio Installer and add the SSIS component from the Modify workflow.

Known Issues

SSMS 21.2.5 cannot function alongside SQL Server Integration Services Projects 2022 version 1.5 and below. Installing SSMS 21.2.5 or SSMS 21.3.6 will cause SQL Server Integration Services Projects 2022 version 1.5 and below features to break. The issue is fixed in SSMS 21.4.7 or later.

If SQL Server Integration Services Projects 2022 is broken after SSMS is installed, please upgrade SQL Server Integration Services Projects 2022 to Version 1.6.2.
SQL Server Import and Export Wizard, when selecting a Data source other than Microsoft OLEDB Provider for SQL Server, please apply the following workaround. Add dependentAssembly inside <assemblyBinding> section of "C:\Program Files\Microsoft SQL Server Management Studio 21\Release\Common7\IDE\CommonExtensions\Microsoft\SSIS\170\Binn\default.applocal.config":

This issue will be fixed in the next version.

Execute Package Utility (dtexecui) is not supported yet. This will be supported in the next version.
Legacy Integration Services Service is not supported yet.

Learn More

Announcing the release of SQL Server Management Studio (SSMS) 21.2.5 | Microsoft Community Hub

Release Notes for SQL Server Management Studio (SSMS) | Microsoft Learn

Known Issues in SQL Server Management Studio | Microsoft Learn

SQL Server 2025 Integration Services Public Preview

Chunhua — Tue, 20 May 2025 08:37:14 GMT

We are excited to announce that alongside the release of SQL Server 2025, Integration Services has been updated to ensure seamless and efficient data integration processes.

Support for the Microsoft SqlClient Data Provider with the ADO.NET connection manager

One of the standout enhancements is support for the Microsoft SqlClient Data Provider within the ADO.NET connection manager. This addition brings improved connectivity, especially the new Microsoft Entra authentication modes. Learn more about using Microsoft SqlClient Data Provider with ADO.NET connection manager.

Deprecated features in SQL Server 2025 Integration Services

Legacy Integration Services Service

This legacy Integration Services Service available in SQL Server Management Studio is deprecated in SQL Server 2025 (17.x) Preview Integration Services.

Integration Services 32-bit mode

Integration Services 32-bit mode is deprecated in SQL Server 2025 (17.x) Preview Integration Services engine. Tools including SQL Server Management Studio 21 and SQL Server Integration Services Projects 2022 support 64-bit only in current and onward.

SqlClient Data Provider (SDS) connection type

When editing SSIS package in SSIS Visual Studio extension, in Maintenance Tasks (Including Back Up Database Task, Check Database Integrity Task, Execute SQL Server Agent Job Task, Execute T-SQL Statement Task, History Cleanup Task, and Maintenance Cleanup Task) and For Each loop container enumerators (including ADO.NET Schema Rowset Enumerator and Foreach SMO Enumerator), you can explicitly choose “SqlClient Data Provider” when “New” a connection. The “SqlClient Data Provider” for above is deprecated.

Features moved out of SQL Server 2025 Integration Services

The following section describes removed features in SQL Server 2025 (17.x) Preview Integration Services.

Change Data Capture (CDC) components by Attunity and Change Data Capture (CDC) service for Oracle by Attunity

Change Data Capture (CDC) components by Attunity and Change Data Capture (CDC) service for Oracle by Attunity were deprecated and are no longer available from SQL Server 2025 (17.x) Preview Integration Services and onward. Details refer to the announcement.

Microsoft Connector for Oracle

Microsoft Connector for Oracle was deprecated and are no longer available from SQL Server 2025 (17.x) Preview Integration Services and onward. Details refer to the announcement.

Hadoop related components

Hadoop Hive Task, Hadoop Pig Task, and Hadoop File System Task components are moved out of SQL Server 2025 (17.x) Preview Integration Services and onward.

Breaking changes

.NET API Microsoft.SqlServer.Dts.Runtime Namespace

When upgrading to SSIS 2025, if you use .NET API Microsoft.SqlServer.Dts.Runtime Namespace, your projects need to update the references and rebuild when SSIS Package includes Execute SQL Task or SSIS package has certain SSIS tasks that rely on SQL Server Management Objects (SMO). For more information, see ADO.NET connection manager.

We will improve this in later CU, so that you do not need this manual change.

Learn more about What's New in Integration Services in SQL Server 2025

What's Next

Azure Feature Pack for Integration Services (SSIS Azure Feature Pack)

Azure Feature Pack for SSIS 2025 is not available yet but will be released soon.

Microsoft Connector for Teradata (SSIS) for SSIS 2025 is not available yet.

Integration Services features in SQL Server Management Studio (SSMS IS)

SQL Server Management Studio supports connection to Integration Services 2025 is not available yet but will be released soon.

SQL Server Integration Services Projects 2022

SQL Server Integration Services Projects 2022 for SSIS 2025 is not available yet but will be released soon.

Learn More

Announcing SQL Server 2025 (preview): The AI-ready enterprise database from ground to cloud - Microsoft SQL Server Blog

SQL Server 2025 - AI ready enterprise database from ground to cloud | Microsoft Community Hub

What's New in Integration Services in SQL Server 2025 - SQL Server Integration Services (SSIS) | Microsoft Learn

Update to Microsoft Connector for Teradata

Chunhua — Tue, 20 Aug 2024 00:36:09 GMT

SQL Server Integration Services (SSIS) has released an update to Microsoft Connector for Teradata, enhancing its compatibility with the latest version of Teradata Tools and Utilities (TTU) 20.00.

The Connector now fully supports Teradata Tools and Utilities (TTU) 20.00, ensuring users can leverage the latest enhancements and performance improvements offered by the new TTU version.

For more details, check the Microsoft documentation.

Integration services deployment wizard now supports Microsoft Entra interactive authentication

Chunhua — Tue, 21 Nov 2023 05:34:22 GMT

Integration services deployment wizard now supports interactive authentication (MFA) option for Microsoft Entra accounts.

If you deploy SSIS projects with SQL Server Management Studio (SSMS), use latest SSMS 19.2.

If you deploy SSIS projects with Visual Studio SSIS extension, use latest SQL Server Integration Services Projects 2022 version 1.3.

General availability of SSIS Projects for VS 2022

Chunhua — Tue, 20 Jun 2023 07:04:43 GMT

We’re happy to announce the popular and long-awaited SQL Server Integration Services Projects extension is now generally available for Visual Studio 2022!

You can get the latest download from SQL Server Integration Services Projects 2022 - Visual Studio Marketplace and visit documentation page for known issues, installation and troubleshooting guidance.

If you experience any issue or would like to share your feedback, please visit SSIS extension in marketplace Q&A tab.

General availability of Azure-SSIS Integration Runtime in Azure Synapse Analytics

Chunhua — Tue, 14 Feb 2023 04:54:28 GMT

Azure-SSIS Integration Runtime is now generally available in Azure Synapse Analytics. You can use Azure-SSIS Integration Runtime (IR) and Execute SSIS package activity in Azure Synapse Analytics.

An Azure-SSIS IR in Azure Synapse Analytics supports

Running packages deployed into SSIS catalog (SSISDB) hosted by Azure SQL Database server/Managed Instance (Project Deployment Model)
Running packages deployed into Azure Files (Package Deployment Model)

Create an Azure-SSIS integration runtime

In the Synapse workspace portal, switch to the Manage tab and then switch to the Integration runtimes to view existing integration. Select New to create a new Azure-SSIS IR and open the Integration runtime setup pane

In the Integration runtime setup pane, select the Lift-and-shift existing SSIS packages to execute in Azure tile, and then select Continue.

For the remaining steps to set up an Azure-SSIS IR, see the Provision an Azure SSIS integration runtime process.

Execute SSIS Package activity in Synapse pipelines

You can deploy your packages into Azure Files (Package Deployment Model).

If you use SSISDB (Project Deployment Model), you can deploy your packages into SSISDB hosted in Azure SQL Managed Instance or Azure SQL Database with the Deployment Wizard. In the Select Deployment Target step, choosing SSIS in Azure Synapse Analytics. Then enter the destination server name (Azure SQL Database or Azure SQL Managed Instance) where the project will be located in the Integration Services Catalog.

In both cases, you can also run your deployed packages on Azure-SSIS IR by using the Execute SSIS Package activity in Synapse pipelines. For more information, see Invoke SSIS package execution as a first-class activity.

Limitations and known issues

Below features are not available in Azure Synapse Analytics:

General Availability of Express Virtual Network injection for SSIS in Azure Data Factory

Chunhua — Mon, 19 Dec 2022 06:30:53 GMT

If you want to lift & shift/migrate your existing SQL Server Integration Services (SSIS) packages to the cloud, so they can run on SSIS integration runtime (IR) in Azure Data Factory (ADF), you’ll need to inject/join your SSIS IR to a virtual network (VNet) in the following scenarios:

You want to access on-premises data stores/resources from SSIS packages running on Microsoft-managed SSIS IR without configuring and managing a self-hosted IR as proxy by yourself. To learn, see the pros and cons of using VNet or no VNet.

You want to use Azure SQL Database server/managed instance that’s configured with a private endpoint/IP firewall rule/virtual network service endpoint or managed instance that joins a VNet to store your packages in SSIS catalog database (SSISDB)/SQL Server database (MSDB).

You want to use Azure Storage that’s configured with a private endpoint/IP firewall rule/virtual network service endpoint to store your SSIS packages or custom setup files.

You want to access other Azure data stores/resources that are configured with a private endpoint/IP firewall rule/virtual network service endpoint.

You want to access other cloud data stores/resources that are configured with an IP firewall rule.

There are two methods for you to inject your SSIS IR into a VNet: standard and express that are both in General Availability (GA) now.

With Express method, your SSIS IR provisions/starts faster, and inbound traffic is not needed anymore to meet Enterprise security compliance requirements.

Here’s a table highlighting the differences between standard and express virtual network injection methods:

Comparison	Standard virtual network injection	Express virtual network injection
Azure-SSIS IR starting duration	Around 30 minutes.	Around 5 minutes.
Azure subscription & resource group settings	Microsoft.Batch must be registered as a resource provider in the virtual network subscription. Creation of a public IP address, load balancer, and network security group (NSG) must be allowed in the virtual network resource group.	Microsoft.Batch must be registered as a resource provider in the virtual network subscription.
Virtual network subnet	Subnet mustn’t be dedicated to other Azure services.	Subnet mustn’t be dedicated to other Azure services. Subnet must be delegated to Microsoft.Batch/batchAccounts.
Virtual network permission	User creating Azure-SSIS IR must have Microsoft.Network/virtualNetworks//join* permission.	User creating Azure-SSIS IR must have Microsoft.Network/virtualNetworks/subnets/join/action permission.
Static public IP addresses	(Optional) Bring your own static public IP addresses (BYOIP) for Azure-SSIS IR.	(Optional) Configure virtual network network address translation (NAT) to set up a static public IP address for Azure-SSIS IR.
Custom DNS server	Recommended to forward unresolved DNS requests to Azure recursive resolvers.	Recommended to forward unresolved DNS requests to Azure recursive resolvers. Requires a standard custom setup for Azure-SSIS IR.
Inbound traffic	Port 29876, 29877 must be open for TCP traffic with BatchNodeManagement service tag as source.	Not required.
Outbound traffic	Port 443 must be open for TCP traffic with AzureCloud service tag as destination.	Port 443 must be open for TCP traffic with DataFactoryManagement service tag as destination.
Resource lock	Not allowed in the resource group.	Not allowed in the virtual network.
Azure-SSIS IRs per virtual network	Unlimited.	Only one.

For more information on VNet injection, see overview of VNet injection.

For more information on express method, see express VNet injection method.

Public preview of SSIS Projects for VS 2022

Chunhua — Mon, 12 Dec 2022 07:42:56 GMT

We’re happy to announce the popular and long-awaited SQL Server Integration Services Projects extension is now available for Visual Studio 2022!

You can get latest download from SQL Server Integration Services Projects 2022 - Visual Studio Marketplace, and visit documentation page for installation and troubleshooting guidance.

If you experience any issue or would like to share your feedback, please visit SSIS extension in marketplace Q&A tab.

Public preview of SQL Server 2022 Integration Services

Chunhua — Thu, 02 Jun 2022 00:38:41 GMT

SQL Server 2022 announced public preview, Integration Services for SQL 2022 is also available.

Visual Studio SQL Server Integration Serivces extension 4.0 (Preview) is available, new features and improvements included are:

Support SQL Server 2022 as target server
Fixed ODBC incorrect binding when output column sequence is different from external column sequence

Details refer to SQL Server Integration Services Projects - Visual Studio Marketplace

SQL Servier Management Studio (SSMS) 19 (Preview) is also available, details refer to

Azure-SSIS Integration Runtime now available in Azure Synapse Analytics (Public Preview)

Chunhua — Mon, 07 Mar 2022 06:44:30 GMT

You can now use Azure-SSIS Integration Runtime (IR) and Execute SSIS package activity in Azure Synapse Analytics.

An Azure-SSIS IR in Azure Synapse Analytics supports

Running packages deployed into SSIS catalog (SSISDB) hosted by Azure SQL Database server/Managed Instance (Project Deployment Model)
Running packages deployed into Azure Files (Package Deployment Model)

Create an Azure-SSIS integration runtime

In the Synapse workspace portal, switch to the Manage tab and then switch to the Integration runtimes to view existing integration. Select New to create a new Azure-SSIS IR and open the Integration runtime setup pane

In the Integration runtime setup pane, select the Lift-and-shift existing SSIS packages to execute in Azure tile, and then select Continue.

For the remaining steps to set up an Azure-SSIS IR, see the Provision an Azure SSIS integration runtime process.

Execute SSIS Package activity in Synapse pipelines

You can deploy your packages into Azure Files (Package Deployment Model).

If you use SSISDB (Project Deployment Model), you can deploy your packages into SSISDB hosted in Azure SQL Managed Instance or Azure SQL Database with the Deployment Wizard. In the Select Deployment Target step, choosing SSIS in Azure Data Factory. Then enter the destination server name (Azure SQL Database or Azure SQL Managed Instance) where the project will be located in the Integration Services Catalog.

Limitations and known issues

Deploy a project with the Deployment Wizard

Deployment target as SSIS in Azure Synapse Analytics is not available currently.

To deploy SSIS project to SSIS IR in Azure Synapse Analytics, in the Select Deployment Target step, choose SSIS in Azure Data Factory instead. Then enter the destination server name (Azure SQL Database or Azure SQL Managed Instance) where the project will be in the Integration Services Catalog.

Below features are not available for SSIS IR in Azure Synapse:

Run Any Process Anywhere in 3 Easy Steps with SSIS in Azure Data Factory

Sandy Winarko — Sun, 14 Nov 2021 06:20:06 GMT

Following our first blog in the “3 Easy Steps” series, see https://techcommunity.microsoft.com/t5/sql-server-integration-services/run-any-sql-anywhere-in-3-easy-steps-with-ssis-in-azure-data/ba-p/2457244, we’d like to show you more ways to quickly extend ADF capabilities by leveraging SSIS features, all without designing/deploying your own package.

Instead, we’ll provide a sample package with parameterized properties that you can simply download, drag & drop into an Execute SSIS Package activity in ADF pipeline and run it on your self-hosted/SSIS integration runtimes (SHIR/SSIS IR) with run-time parameter values assigned for your specific scenario.

This article illustrates 3 easy steps to run any process, which can be any executable, such as application/program/utility/batch file, anywhere using SSIS in ADF: (1) Prepare your SHIR/SSIS IR; (2) Prepare an Execute SSIS Package activity in ADF pipeline; and (3) Run the Execute SSIS Package activity on your SHIR/SSIS IR.

1) Prepare your SHIR/SSIS IR

Our sample package will allow you to run any executable on premises and in the cloud. To run it on premises, your SSIS IR will need to join a virtual network (VNet) connected to your on-premises network or use an SHIR as a proxy. For this purpose, we’ll use the proxy method that’s relatively easier/quicker to configure than the VNet method.

First, to prepare an SHIR as a proxy for your SSIS IR, see https://docs.microsoft.com/azure/data-factory/self-hosted-integration-runtime-proxy-ssis.

Next, to quickly prepare your SSIS IR for this purpose, you can unselect all checkboxes on the Deployment settings page of Integration runtime setup pane and only select the checkboxes to install Azure PowerShell as an express custom setup and configure SHIR as a proxy on the Advanced settings page, see https://docs.microsoft.com/azure/data-factory/tutorial-deploy-ssis-packages-azure.

Your SSIS IR should be running within 5 minutes.

2) Prepare an Execute SSIS Package activity in ADF pipeline

Once your SHIR and SSIS IR are running, you can go to the Integration runtimes page in ADF Manage hub and select the Execute SSIS package button on the right side of your SSIS IR name.

This will auto-create an ADF pipeline with Execute SSIS Package activity already in it.

We’ve created a sample package (ExecuteProcess.dtsx) on SQL Server Data Tools (SSDT) containing two components named after their actual type/function: Flexible File Task to optionally download an input file for your executable onto the working directory of your SSIS IR and Execute Process Task to run your executable on SHIR/SSIS IR. We’ve also parameterized the following package properties for you to assign your run-time values:

-- Arguments: Enter the command-line arguments for your executable.

-- Executable: Enter the name of your executable to run.

-- ExecuteOnProxy: Enter True or False to run Execute Process Task on your SHIR or SSIS IR, respectively.

-- SourceFileName: (OPTIONAL) Enter the name of input file for your executable, so Flexible File Task can download it onto the working directory of your SSIS IR – Use only when you want to run your executable on SSIS IR and it can’t accept UNC path as an argument, otherwise omit it.

-- SourceFolderPath: (OPTIONAL) Enter the path of folder containing an input file for your executable, so Flexible File Task can download it onto the working directory of your SSIS IR – Use only when you want to run your executable on SSIS IR and it can’t accept UNC path as an argument, otherwise omit it.

-- WorkingDirectory: Enter the path of folder containing your executable – If omitted, your executable must be located in one of the directories included in Windows %PATH% environment variable.

You can download our sample package from https://ssisazurefileshare.blob.core.windows.net/blogs/ExecuteProcess.dtsx and then drag & drop it as an embedded package on the Settings tab of your Execute SSIS Package activity in ADF pipeline.

3) Run the Execute SSIS Package activity on your SHIR/SSIS IR

Once you embed our sample package in your Execute SSIS Package activity in ADF pipeline, you can run it on your SHIR/SSIS IR with run-time parameter values assigned for your specific scenario. Here are some examples:

Example 1) Run any command-line utility on your machine on premises

In this example, we’ll run the bulk copy program (bcp) utility to export data from your SQL Server on premises into a CSV file. To do so, complete the following steps:

a) Configure the Windows authentication feature on the Settings tab of your Execute SSIS Package activity to connect to your SQL Server on premises with Azure Key Vault (AKV) to store your sensitive data.

b) Add the following parameters on the SSIS parameters tab to assign their run-time values:

-- Arguments: YourDBName.dbo.YourTableName out C:\YourFolderName\YourFileName.csv -c -t, -T

-- Executable: bcp

-- ExecuteOnProxy: True

Example 2) Run any batch file on your machine on premises

In this example, we’ll run a main.cmd batch file that will in turn run the bcp utility to export data from your SQL Server on premises into a CSV file and a PowerShell script to upload the CSV file into a new blob container in your Azure Storage. To do so, complete the following steps:

a) Configure the Windows authentication feature on the Settings tab of your Execute SSIS Package activity to connect to your SQL Server on premises with AKV to store your sensitive data – See Example 1 above.

b) Add the following parameters on the SSIS parameters tab to assign their run-time values:

-- Executable: main.cmd

The batch file contains the following commands:

bcp YourDBName.dbo.YourTableName out C:\YourFolderName\YourFileName.csv -c -t, -T

powershell -File C:\YourFolderName\YourScriptName.ps1

The PowerShell script contains the following cmdlets:

$ctx = New-AzStorageContext YourAzureStorageAccountName YourAzureStorageAccountKey

New-AzStorageContainer YourContainerName -Context $ctx -Permission blob

Set-AzStorageBlobContent -File C:\YourFolderName\YourFileName.csv -Container YourContainerName -Blob YourFileName.csv -Context $ctx

-- ExecuteOnProxy: True

-- WorkingDirectory: C:\YourFolderName\

Example 3) Run any package stored in file system on your machine on premises

In this example, we’ll run a package stored in file system that connects to your SQL Server on premises. To do so, complete the following steps:

b) Add the following parameters on the SSIS parameters tab to assign their run-time values:

-- Arguments: /File C:\YourFolderName\YourPackageName.dtsx

-- Executable: dtexec

-- ExecuteOnProxy: True

Example 4) Run any Azure PowerShell cmdlets on your SSIS IR

In this example, we’ll run Azure PowerShell cmdlets to create a new blob container in your Azure Storage. To do so, complete the following steps:

a) Add the following parameters on the SSIS parameters tab of your Execute SSIS Package activity to assign their run-time values:

-- Arguments: $ctx = New-AzStorageContext YourAzureStorageAccountName YourAzureStorageAccountKey; New-AzStorageContainer YourContainerName -Context $ctx -Permission blob

-- Executable: powershell

-- ExecuteOnProxy: False

Example 5) Run any Azure PowerShell script file on your SSIS IR

In this example, we’ll run an Azure PowerShell script file stored in Azure Files. To do so, complete the following steps:

a) Configure the Windows authentication feature on the Settings tab of your Execute SSIS Package activity to connect to Azure Files with AKV to store your sensitive data.

b) Add the following parameters on the SSIS parameters tab of your Execute SSIS Package activity to assign their run-time values:

-- Arguments: -F YourScriptName.ps1

-- Executable: powershell

-- ExecuteOnProxy: False

-- SourceFileName: YourScriptName.ps1

-- SourceFolderPath: //YourAzureStorageAccountName.file.core.windows.net/YourFileshareName/

If you have other scenarios that require you to assign run-time values to different properties that aren’t parameterized in our sample package, you can still do so on the Connection managers/Property overrides tabs of your Execute SSIS Package activity by referencing the component names (Flexible File Task/Execute SQL Task).

I hope you’ll find this article useful to run any process/executable using SSIS in ADF. Please don’t hesitate to contact us if you have any feedbacks, questions, or issues, and we’ll follow up ASAP. Thank you as always for your support.

Express Virtual Network Injection for SSIS in Azure Data Factory

Sandy Winarko — Thu, 11 Nov 2021 23:25:01 GMT

You want to access on-premises data stores/resources from SSIS packages running on Microsoft-managed SSIS IR without configuring and managing a self-hosted IR as proxy by yourself. To learn about the pros and cons of using VNet or no VNet, see https://techcommunity.microsoft.com/t5/sql-server-integration-services/vnet-or-no-vnet-secure-data-access-from-ssis-in-azure-data/ba-p/1062056.

You want to use Azure SQL Database server/managed instance that’s configured with a private endpoint/IP firewall rule/virtual network service endpoint or managed instance that joins a VNet to store your packages in SSIS catalog database (SSISDB)/SQL Server database (MSDB).

You want to use Azure Storage that’s configured with a private endpoint/IP firewall rule/virtual network service endpoint to store your SSIS packages or custom setup files.

You want to access other Azure data stores/resources that are configured with a private endpoint/IP firewall rule/virtual network service endpoint.

You want to access other cloud data stores/resources that are configured with an IP firewall rule.

There are two methods for you to inject your SSIS IR into a VNet: standard and express that are in General Availability (GA) and Public Preview, respectively.

To see their different networking requirements, compare the diagram for standard VNet injection method on the left-hand-side (LHS) and that for express VNet injection method on the right-hand-side (RHS):

The standard method injects both SSIS IR virtual machine (VM) and container into your VNet, while the express one uses a new networking technology that injects SSIS IR VM into Microsoft-managed VNet, but SSIS IR container into your VNet.

This technology allows the express method to provision/start your SSIS IR faster, since it can now leverage our pools of standby VMs that have been created and prepared in advance, see https://techcommunity.microsoft.com/t5/sql-server-integration-services/express-provisioning-of-azure-ssis-integration-runtime/ba-p/719058.

The express method is also enterprise-friendlier than the standard one, since it needs not create additional network resources, such as public IP address, load balancer, and network security group (NSG) at network interface card (NIC) level, inside your VNet (see the purple boxes in LHS diagram).

Consequently, the express method has no inbound traffic and minimal outbound traffic requirements compared to the standard one (see the incoming and outgoing arrows, into and from your VNet in LHS and RHS diagrams).

That said, there are still some limitations with the express method that can be worked around for now, e.g. using your own DNS server requires a standard custom setup on SSIS IR, using multiple SSIS IRs requires multiple VNets that can be peered/connected together, etc.

For an overview of VNet injection, see https://docs.microsoft.com/azure/data-factory/join-azure-ssis-integration-runtime-virtual-network#configure-a-virtual-network.

For a detailed comparison of the standard and express VNet injection methods, see https://docs.microsoft.com/azure/data-factory/azure-ssis-integration-runtime-virtual-network-configuration.

For more information on the express VNet injection method, see https://docs.microsoft.com/azure/data-factory/azure-ssis-integration-runtime-express-virtual-network-injection.

For more information on the standard VNet injection method, see https://docs.microsoft.com/azure/data-factory/azure-ssis-integration-runtime-standard-virtual-network-injection.

I hope you’ll find this article useful for you to inject your SSIS IR into a VNet. Please don’t hesitate to contact us if you have any feedbacks, questions, or issues, and we’ll follow up ASAP. Thank you as always for your support.

Run Any SQL Anywhere in 3 Easy Steps with SSIS in Azure Data Factory

Sandy Winarko — Sun, 14 Nov 2021 06:19:31 GMT

After releasing many features that unblock and accelerate the migration of SQL Server Integration Services (SSIS) packages into Azure Data Factory (ADF), see https://techcommunity.microsoft.com/t5/sql-server-integration-services/blast-to-the-future-accelerating-legacy-ssis-migrations-into/ba-p/1792821, we’d like to show you how to quickly extend ADF capabilities by leveraging these SSIS features, all without designing/deploying your own package.

This article illustrates 3 easy steps to run any SQL statements/script anywhere using SSIS in ADF: (1) Prepare your SHIR/SSIS IR; (2) Prepare an Execute SSIS Package activity in ADF pipeline; and (3) Run the Execute SSIS Package activity on your SHIR/SSIS IR.

1) Prepare your SHIR/SSIS IR

Our sample package will allow you to run any SQL statements/script on premises and in the cloud. To access any SQL Server or other SQL-based relational database management system (RDBMS) on premises, your SSIS IR will need to join a virtual network (VNet) connected to your on-premises network or use an SHIR as a proxy. For this purpose, we’ll use the proxy method that’s relatively easier/quicker to configure than the VNet method.

First, to prepare an SHIR as a proxy for your SSIS IR, see https://docs.microsoft.com/azure/data-factory/self-hosted-integration-runtime-proxy-ssis.

Next, to quickly prepare your SSIS IR for this purpose, you can unselect all checkboxes on the Deployment settings page of Integration runtime setup pane and only select the checkbox to configure an SHIR as a proxy on the Advanced settings page, see https://docs.microsoft.com/azure/data-factory/tutorial-deploy-ssis-packages-azure.

Since your SSIS IR isn’t joining a VNet, it should be running within 5 minutes.

2) Prepare an Execute SSIS Package activity in ADF pipeline

Once your SHIR and SSIS IR are running, you can go to the Integration runtimes page in ADF Manage hub and select the Execute SSIS package button on the right side of your SSIS IR name.

This will auto-create an ADF pipeline with Execute SSIS Package activity already in it.

We’ve created a sample package (ExecuteSQL.dtsx) on SQL Server Data Tools (SSDT) containing 3 components named after their actual type/function: Execute SQL Task to run any SQL statements/script, OLEDB Connection Manager to access any SQL Server or other SQL-based RDBMS, and File Connection Manager to access any SQL script stored in file system. We’ve also parameterized the following package properties for you to assign your run-time values:

-- ConnectionString: Enter the usual connectivity info for OLEDB Connection Manager, e.g.

-- To use Windows Authentication:

-- Data Source=[.|YourSQLServerName];Initial Catalog=YourDBName;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;

-- To use SQL Authentication:

-- Data Source=[.|YourSQLServerName|YourAzureSQLServerEndpoint];User ID=YourUserID;Password=YourPassword;Initial Catalog=YourDBName;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;

-- To use Azure Active Directory (AAD) Authentication with ADF managed identity:

-- Data Source=YourAzureSQLServerEndpoint;User ID=;Initial Catalog=YourDBName;Provider=MSOLEDBSQL;Integrated Security=SSPI;Auto Translate=False;

-- ConnectUsingManagedIdentity: Enter True or False to enable or disable AAD Authentication with ADF managed identity on your OLEDB Connection Manager, respectively.

-- ExecuteOnProxy: Enter True or False to run Execute SQL Task on your SHIR or SSIS IR, respectively.

-- FilePath: Enter the local/UNC path of your SQL script stored in file system.

-- SQLStatementSource: Enter your SQL statements or File Connection Manager depending on what you assigned to the SQLStatementSourceType parameter.

-- SQLStatementSourceType: Enter 1 or 2 to run your SQL statements assigned to the SQLStatementSource parameter or your SQL script file referenced by the FilePath parameter, respectively.

You can download our sample package from https://ssisazurefileshare.blob.core.windows.net/blogs/ExecuteSQL.dtsx and then drag & drop it as an embedded package on the Settings tab of your Execute SSIS Package activity in ADF pipeline.

3) Run the Execute SSIS Package activity on your SHIR/SSIS IR

Example 1) Run any SQL statements on your SQL Server on premises

To run any SQL statements on your SQL Server on premises, complete the following steps:

b) Add the following parameters on the SSIS parameters tab to assign their run-time values:

-- ConnectionString: Use Windows authentication, e.g.

Data Source=.;Initial Catalog=YourDBName;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;

-- ConnectUsingManagedIdentity: False

-- ExecuteOnProxy: True

-- SQLStatementSource: Enter any SQL statements, e.g.

CREATE TABLE [Customer] (

[Firstname] nvarchar(50),

[Lastname] nvarchar(50),

[Street] nvarchar(50),

[Postalcode] nvarchar(50),

[City] nvarchar(50),

[Country] nvarchar(50))

-- SQLStatementSourceType: 1

Example 2) Run any SQL script file on your SQL Server on premises

To run any SQL script file on your SQL Server on premises, complete the following steps:

a) If you store your SQL script file on the same machine as your SQL Server/SHIR:

1) Configure the Windows authentication feature on the Settings tab of your Execute SSIS Package activity to connect to your SQL Server on premises with AKV to store your sensitive data – See Example 1 above.

2) Add the following parameters on the SSIS parameters tab to assign their run-time values:

-- ConnectionString: Use Windows authentication, e.g.

Data Source=.;Initial Catalog=YourDBName;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;

-- ConnectUsingManagedIdentity: False

-- ExecuteOnProxy: True

-- FilePath: Enter the local path of your SQL script file, e.g.

C:\Users\YourUsername\Downloads\SQLScript.sql.

-- SQLStatementSource: File Connection Manager

-- SQLStatementSourceType: 2

b) If you store your SQL script file in Azure Files:

1) Configure the Windows authentication feature on the Settings tab of your Execute SSIS Package activity to connect to Azure Files with AKV to store your sensitive data.

2) Add the following parameters on the SSIS parameters tab to assign their run-time values:

-- ConnectionString: Use SQL authentication, e.g.

Data Source=.;User ID=YourUserID;Password=YourPassword;Initial Catalog=YourDBName;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;

You can also configure AKV to store your sensitive data.

-- ConnectUsingManagedIdentity: False

-- ExecuteOnProxy: True

-- FilePath: Enter the UNC path of your SQL script file, e.g.

//YourAzureStorageAccountName.file.core.windows.net/YourFileShareName/SQLScript.sql

-- SQLStatementSource: File Connection Manager

-- SQLStatementSourceType: 2

Example 3) Run any package stored in SSISDB hosted by your SQL Server on premises

To run any package stored in SSISDB hosted by your SQL Server on premises, complete the following steps:

b) Add the following parameters on the SSIS parameters tab to assign their run-time values:

-- ConnectionString: Use Windows authentication, e.g.

Data Source=.;Initial Catalog=SSISDB;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;

-- ConnectUsingManagedIdentity: False

-- ExecuteOnProxy: True

-- SQLStatementSource:

DECLARE @return_value INT, @exe_id BIGINT, @err_msg NVARCHAR(150)

EXEC @return_value=[SSISDB].[catalog].[create_execution] @folder_name=N'YourFolderName', @project_name=N'YourProjectName', @package_name=N'YourPackageName.dtsx', @use32bitruntime=0, @execution_id=@exe_id OUTPUT

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @exe_id, @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=1

EXEC [SSISDB].[catalog].[start_execution] @execution_id=@exe_id, @retry_count=0

IF(SELECT [status] FROM [SSISDB].[catalog].[executions] WHERE execution_id=@exe_id)<>7

BEGIN

SET @err_msg=N'Your package execution did not succeed for execution ID: ' + CAST(@exe_id AS NVARCHAR(20))

RAISERROR(@err_msg,15,1)

END

-- SQLStatementSourceType: 1

Example 4) Clean up package execution logs outside the configured retention window for SSISDB hosted by your Azure SQL Database server

To clean up package execution logs outside the configured retention window for SSISDB hosted by your Azure SQL Database server, complete the following steps:

a) Add the following parameters on the SSIS parameters tab of your Execute SSIS Package activity to assign their run-time values:

-- ConnectionString: Use AAD authentication with ADF managed identity, e.g.

Data Source=YourAzureSQLDatabaseServerName.database.windows.net;User ID=;Initial Catalog=SSISDB;Provider=MSOLEDBSQL;Integrated Security=SSPI;Auto Translate=False;

-- ConnectUsingManagedIdentity: True

Make sure that you configure your Azure SQL Database server to allow access using AAD authentication with ADF managed identity, see https://docs.microsoft.com/sql/integration-services/connection-manager/ole-db-connection-manager?view=sql-server-ver15#managed-identities-for-azure-resources-authentication.

-- ExecuteOnProxy: False

-- SQLStatementSource:

EXEC internal.cleanup_server_retention_window_exclusive

-- SQLStatementSourceType: 1

I hope you’ll find this article useful to run any SQL statements/script anywhere using SSIS in ADF. Please don’t hesitate to contact us if you have any feedbacks, questions, or issues, and we’ll follow up ASAP. Thank you as always for your support.

SSIS DevOps Tools new version tasks in preview

Chunhua — Mon, 26 Apr 2021 02:53:22 GMT

SSIS DevOps Tools released new version of SSIS Build task and SSIS Deploy task. Major enhancements include:

SSIS Build task version 1.* (Preview)
- Remove the dependency on Visual Studio and SSIS designer. Build task can run on Microsoft-hosted agent or self-hosted agent with Windows OS and .NET framework 4.6.2 or higher.
- No need of installing out-of-box components.
- Support protection level EncryptionWithPassword and EncryptionAllWithPassword.
SSIS Deploy task version 1.* (Preview)
- Support protection level EncryptionWithPassword and EncryptionAllWithPassword.

Use new version of SSIS Build task:

Microsoft-hosted agent or self-hosted agent with Windows OS and .NET framework 4.6.2 or higher.
Add SSIS Build task and select Task version as 1.* (Preview). Default version is 0.*.
1.* only properties:
- Project Password
  Password of the SSIS project and its packages. This argument is only valid when the protection level of the SSIS project and packages is EncryptSensitiveWithPassword or EncryptAllWithPassword. For package deployment model, all packages must share the same password specified by this argument.
- Strip Sensitive Data
  Convert the protection level of the SSIS project to DontSaveSensitve if this value is true. When protection level is EncryptSensitiveWithPassword or EncryptAllWithPassword, the argument Project Password must be correctly set. This option is only valid for project deployment model.

Use new version of SSIS Deploy task:

Add SSIS Deploy task and select Task version as 1.* (Preview). Default version is 0.*
1.* only properties:
- Project Password
  Password to decrypt the ISPAC or DTSX files. This argument is only valid when the protection level is EncryptSensitiveWithPassword or EncryptAllWithPassword.

Please refer to this page for more detail usage. If you have questions, visit Q&A.