Wednesday, May 9, 2018

How to Execute an SSIS Package on Azure from On-premise or IaaS Server

So you have SSIS packages deployed to Azure using ADFv2 and an Azure-SSIS Integration Runtime but you still have on-premise or IaaS SQL Server(s) and want to initiate the execution of your Azure hosted packages from your on-premise or IaaS server(s)...

As I like to say, don't fret! This is pretty easy to do using straightforward, tried & true on-premise SQL Server technologies:

  1. Linked Server - A Linked Server is used to execute Azure SQL Database queries using four-part names (server.database.schema.object).
  2. Stored Procedures - The SSISDB create_execution and start_execution stored procedures are used to initiate package execution.
  3. SQL Agent Job - A SQL Agent Job is used to schedule the execution of the SSISDB create_executionstart_execution, etc. stored procedures.
There you have it. A rather painless way to execute Azure-based SSIS packages from your traditional SQL Server instance. This can be useful when migrating from on-premise and/or IaaS to PaaS.

No comments:

Post a Comment