SQL Saturday Oslo is approaching and I’m giving a session where I compare SSIS, Databricks and Mapping Data Flows for data transformation in Data Factory. One of the problems I got when setting this up is that when running SSIS in Data Factory you must handle the Integration Runtime and start (and stop) this efficiently.

The Integration Runtime (IR) is the compute infrastructure used by Azure Data Factory to provide the following data integration capabilities across different network environments, and could be used for data flows, data movement (copy), SSIS or as gateways (self-hosted Integration runtime). When using SSIS this is where you set the cluster size, database and so on.

A disadvantage of using SSIS is that the Integration Runtime won’t automatically start if you invoke an activity that uses it. You probably won’t have it running all the time as it has an hourly cost. You also can’t have it automatically shut down, so you need to handle this as well.

I’ve solved this with an Azure Function with PowerShell, so the implementation is quite easy. For setting it up you need a Function with a managed identity, and then give this identity permissions (contributor works) for your data factory. Next you can add a function to start or stop like this:

$operation = $Request.Body.operation

#use the managed identify for authentication
Connect-AzAccount -Identity

$resourceGroupName = “datahelge-Rg”
$datafactoryName = “ADF-datahelge”
$integrationRuntimeName = “SQLSatOslo2019SSIS”

if ($operation -eq “start”) {
Start-AzDataFactoryV2IntegrationRuntime -resourceGroupName $resourceGroupName -datafactoryName $datafactoryName -name $integrationRuntimeName -Force
}
else {
Stop-AzDataFactoryV2IntegrationRuntime -resourceGroupName $resourceGroupName -datafactoryName $datafactoryName -name $integrationRuntimeName -Force
}

$response = [HttpResponseContext]@{
StatusCode = ‘200’ # OK
ContentType = ‘application/json’
Body = @{ ‘status’ = $body}
}

Push-OutputBinding -Name response -Value $response

PowerShell to start/stop SSIS IR

One of the problems you could face is that by calling this Function with ADF there is a hard 240 second timeout. So my advice is to add a second function that checks the status, and has this Powershell code:

connect-AzAccount -Identity

$resourceGroupName = “datahelge-Rg”
$datafactoryName = “ADF-datahelge”
$integrationRuntimeName = “SQLSatOslo2019SSIS”

$ir = Get-AzDataFactoryV2IntegrationRuntime -resourceGroupName $resourceGroupName -datafactoryName $datafactoryName -name $integrationRuntimeName
$state = $ir.state

$response = [HttpResponseContext]@{
StatusCode = ‘200’ # OK
ContentType = ‘application/json’
Body = @{ ‘status’ = $state}
}

Push-OutputBinding -Name response -Value $response

Check status for IR

Then you can have this simple pipeline:

You can add the same for stopping the integration runtime, and either schedule this with ADF, or add it as a part of your pipelines with SSIS. Naturally it would be easier if SSIS-IR could be started automatically, and I haven’t found a good solution to close it down in case of inactivity if you have multiple pipelines using SSIS Integration Runtime.