Site icon TheWindowsUpdate.com

Modernize and extend your ETL/ELT workflows with SSIS activities in ADF pipelines

This post has been republished via RSS; it originally appeared at: SQL Server Integration Services (SSIS) articles.

First published on MSDN on May 23, 2018
As we continue our march towards General Availability (GA) of SQL Server Integration Services (SSIS) in Azure Data Factory (ADF), we are excited to announce the release of Execute SSIS Package activity as the most direct and code-free method to invoke SSIS package executions within ADF pipelines.



In the past, our customers resorted to using Stored Procedure activity to do the same, but they were required to create a linked service to connect to Azure SQL Database (DB) server hosting their catalog of SSIS projects (SSISDB) and T-SQL script to invoke stored procedures in SSISDB that create, configure, and start SSIS package executions (see https://docs.microsoft.com/en-us/azure/data-factory/how-to-invoke-ssis-package-stored-procedure-activity ).



Now they can simply drag-n-drop the first-class Execute SSIS Package activity into their ADF pipeline, select Azure-SSIS Integration Runtime (IR) to run their package, and specify the path to their deployed package in SSIDB:





For more info, see https://docs.microsoft.com/en-us/azure/data-factory/how-to-invoke-ssis-package-ssis-activity .



Execute SSIS Package activity lets you do the following:









If you use SSIS project/package parameters in your packages, you can assign values to them when running Execute SSIS Package activities within ADF pipelines in the following manners:









In the near future, we will provide more direct and code-free methods on ADF UI/app to assign values to SSIS project/package parameters, connection managers, and property overrides just like using SSMS.



Speaking of SSMS, once you run your pipelines containing Execute SSIS Package activities, you can monitor the pipeline runs on ADF UI/app and obtain the relevant SSISDB operation/execution IDs from the output of your activity runs to check more comprehensive execution logs/error messages on SSMS:





One more thing, we have also released a new scheduling feature on SSMS that will fast-track the introduction of ADF capabilities to SSIS users.  This feature lets you schedule SSIS package executions on Azure-SSIS IR by automatically generating ADF pipelines, Execute SSIS Package activities within those pipelines, and ADF triggers that schedule the pipeline runs, all through a familiar UI just like using SQL Server Agent on premises:





The auto-generated ADF pipelines/activities/triggers can then be monitored/edited/extended on ADF UI/app.



For more info, see https://docs.microsoft.com/en-us/sql/integration-services/lift-shift/ssis-azure-schedule-packages-ssms?view=sql-server-2017 .



We hope you will find these new features useful to modernize and extend your ETL/ELT workflows, so please do not hesitate to contact us if you have any feedbacks/questions/issues and we will follow up ASAP.  Thank you as always for your support.

Exit mobile version