This post has been republished via RSS; it originally appeared at: MSDN Blogs.
Expanding our efforts to integrate SQL Server Integration Services (SSIS) deeper into Azure Data Factory (ADF), we have recently improved the configurability of Execute SSIS Package activities in ADF pipelines.
With the enhanced “Settings” tab and additional three new tabs (“SSIS Parameters”/”Connection Managers”/”Property Overrides”), you can now configure Execute SSIS Package activities via ADF User Interface (UI) as easily/intuitively as you can configure SSIS package executions via SQL Server Management Studio (SSMS) UI.
For comparisons, here is the existing SSMS UI for configuring SSIS package executions:
And here is the new ADF UI for configuring Execute SSIS Package activities:
When configuring Execute SSIS Package activities, after you designate the Azure-SSIS Integration Runtime (IR) to run your packages, the existing folders/projects/packages/environments from your associated SSIS catalog (SSISDB) will now be displayed for you to browse and select.
After you select your project/package, the relevant SSIS parameters and connection managers will then be displayed for you to assign values to them. Furthermore, you will also be able to override the values of existing properties in your selected package. All values can have dynamic content that includes expressions, functions, ADF system variables, and ADF pipeline parameters/variables.
All this can now be done without editing JSON code anymore. For more info, see https://docs.microsoft.com/azure/data-factory/how-to-invoke-ssis-package-ssis-activity.
On top of improving the configurability of Execute SSIS Package activities, we have also simplified the just-in-time/on-demand provisioning pattern for SSIS IR using Execute SSIS Package and Web activities.
In the past, we introduced an additional Azure Automation resource into this pattern and used Web activities to call webhooks of PowerShell runbooks that start and stop your SSIS IR just in time before and after running Execute SSIS Package activity. Since webhook calls are asynchronous, we had to introduce a delay/multiple retries for Execute SSIS Package activity to wait until your SSIS IR is ready.
With the new pattern, you can use Web activities to directly and synchronously call ADF REST API methods that start and stop your SSIS IR just in time before and after running Execute SSIS Package activity, so no more Azure Automation and no more delay/retries. For more info, see https://docs.microsoft.com/azure/data-factory/how-to-schedule-azure-ssis-integration-runtime.
To give you a little head start, we have also published this pattern as the first SSIS-specific template in ADF Template Gallery:
In the near future, we will publish more SSIS-specific templates and integrate more ADF features into Execute SSIS Package activity, e.g. to leverage Azure Key Vault (AKV) linked service and the managed identity for ADF in package executions, so stay tuned!
One more thing, driven by popular demand, we have finally released a preview of Power Query (PQ) Source. This new connector functions as both data source and transformation as it allows you to execute/operationalize PQ script generated from Excel/Power BI Desktop that connects and transforms data from various sources. At present, to facilitate quick/frequent feedback-gathering and improvement cycles, it can only be used with SQL Server Data Tools (SSDT) and SSIS IR. For more info, see https://docs.microsoft.com/sql/integration-services/data-flow/power-query-source?view=sql-server-2017.
As a simple example, I have used Excel to generate PQ script that connects and transforms Wikipedia page (https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population) into a table, and then created SSIS data flow with PQ Source that executes the script and ADO.NET Destination that loads the table into my Azure SQL Database. I have also optionally used Single Query from Variable mode that holds the script in a user variable, so I can use SSIS parameter or property override to assign value to it:
I 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.