azurerm_data_factory_linked_service_sql_server SSIS Integration Runtime

[jungopro]

Community Note

• Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request
• Please do not leave "+1" or "me too" comments, they generate extra noise for issue followers and do not help prioritize the request
• If you are interested in working on this issue or have submitted a pull request, please leave a comment

Terraform (and AzureRM Provider) Version

Terraform v0.11.13

• provider.azurerm v1.25.0

Affected Resource(s)

• azurerm_data_factory_linked_service_sql_server

References

In the documentation for the azurerm_data_factory_linked_service_sql_server resource there's a reference to the integration runtime. However, I don't see any place mentioning how to create / query / config the integration runtime.
With the new data_factory set of resources, I expect there will be a standard way to provision and configure SSIS integration runtime inside data factory with SQL connection

Am I missing something in the documentation or does this feature not yet implemented?

Thanks

Omer

[mbfrahry]

Hey @jungopro, unfortunately we haven't written the integration runtime resource yet. In the meantime, you can create one through the data factory portal and use the name to attach it to the linked service through Terraform.

[r0bnet]

@jungopro found this issue as i'm looking for something similar or even the same. But i'm currently not sure if you mean this resource: https://docs.microsoft.com/de-de/azure/templates/microsoft.datafactory/2018-06-01/factories/integrationruntimes which is a subresource of the data factory.
Found out that the documentation isn't correct in the link but there are working ARM templates for it: https://docs.microsoft.com/de-de/azure/templates/microsoft.datafactory/2018-06-01/factories/integrationruntimes

[jungopro]

@jungopro found this issue as i'm looking for something similar or even the same. But i'm currently not sure if you mean this resource: https://docs.microsoft.com/de-de/azure/templates/microsoft.datafactory/2018-06-01/factories/integrationruntimes which is a subresource of the data factory.
Found out that the documentation isn't correct in the link but there are working ARM templates for it: https://docs.microsoft.com/de-de/azure/templates/microsoft.datafactory/2018-06-01/factories/integrationruntimes

I do mean this resource, yes

[r0bnet]

Okay, then i'm also looking for this. Probably i'll have to develop it on my own.
Currently i guess it's possible to create the runtime via ARM templates. Do you know if it's possible to create the integration runtime WITHOUT creating the parent data factory via the same ARM template? It's always defined as subresource so i'm not sure tbh.

[jungopro]

Haven't tried that, sorry

What I currently have is an arm template and TF module to deploy ARM template. This might be a bit of an overkill but it can help you get started quickly

ARM

{
  "contentVersion": "1.0.0.0",
  "$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
  "parameters": {
      "name": {
          "type": "string",
          "metadata": {
              "description": "Name of the data factory. Must be globally unique."
          }
      },
      "location": {
          "type": "string",
          "defaultValue": "East US",
          "metadata": {
              "description": "Location of the data factory. Currently, only East US, East US 2, and West Europe are supported. "
          }
      },
      "nodeSize": {
        "type": "string",
        "allowedValues": [
          "Standard_A4_v2",
          "Standard_A8_v2",
          "Standard_D1_v2",
          "Standard_D2_v2",
          "Standard_D3_v2",
          "Standard_D4_v2"
        ],
        "metadata": {
          "description": "Location of the data factory."
        }
      },
      "nodeNumber": {
        "type": "string",
        "metadata": {
          "description": "Number of nodes in the cluster."
        }
      },
      "maximumParallelExecutionsPerNode": {
        "type": "string",
        "metadata": {
          "description": "Maximim number of parallel executions per node in the cluster."
        }
      },
      "azureSqlServerName": {
        "type": "string",
        "metadata": {
          "description": "Name of the Azure SQL server that hosts the SSISDB database (SSIS Catalog). Example: servername.database.windows.net"
        }
      },
      "databaseAdminUsername": {
        "type": "string",
        "metadata": {
          "description": "Name of the Azure SQL database user."
        }
      },
      "databaseAdminPassword": {
        "type": "securestring",
        "metadata": {
          "description": "Password for the database user."
        }
      },
      "catalogPricingTier": {
        "type": "string",
        "metadata": {
          "description": "Pricing tier of the SSIS Catalog (SSISDB datbase)"
        }
      },
      "computeType": {
        "type": "string",
        "metadata": {
          "description": "Compute Type"
        }
      },
      "coreCount": {
        "type": "string",
        "metadata": {
          "description": "Number of cores"
        }
      },
      "ttlMinutes": {
        "type": "string",
        "metadata": {
          "description": "TTL in minutes"
        }
      }
  },
  "resources": [{
      "name": "[parameters('name')]",
      "apiVersion": "2018-06-01",
      "type": "Microsoft.DataFactory/factories",
      "location": "[parameters('location')]",
      "identity": {
          "type": "SystemAssigned"
      },
      "resources": [
        {
          "name": "[concat(parameters('name'), '/SPAzureSsisIR')]",
          "type": "Microsoft.DataFactory/factories/integrationRuntimes",
          "dependsOn": [
            "[parameters('name')]"
          ],
          "apiVersion": "2018-06-01",
          "properties": {
            "type": "Managed",
            "typeProperties": {
              "computeProperties": {
                "location": "[parameters('location')]",
                "nodeSize": "[parameters('nodeSize')]",
                "numberOfNodes": "[int(parameters('nodeNumber'))]",
                "maxParallelExecutionsPerNode": "[int(parameters('maximumParallelExecutionsPerNode'))]",
                "dataFlowRuntime": {
                  "computeType": "[parameters('computeType')]",
                  "coreCount": "[int(parameters('coreCount'))]",
                  "timeToLiveInMinutes": "[int(parameters('ttlMinutes'))]"
                }
              },
              "ssisProperties": {
                "catalogInfo": {
                  "catalogServerEndpoint": "[parameters('azureSqlServerName')]",
                  "catalogAdminUserName": "[parameters('databaseAdminUserName')]",
                  "catalogAdminPassword": {
                    "type": "SecureString",
                    "value": "[parameters('databaseAdminPassword')]"
                  },
                  "catalogPricingTier": "[parameters('catalogPricingTier')]"
                },
                "edition": "Standard",
                "licenseType": "LicenseIncluded"
              }
            }
          }
        }
      ]
    }
  ],
  "outputs": {
    "name": {
      "type": "string",
      "value": "[parameters('name')]"
    }
  }
}

And the TF module:

resource "random_string" "random_string" {
  keepers = {
    # Generate a new ID only when a new resource group is defined
    resource_group = "${var.resource_group}"
  }

  length  = 5
  upper   = false
  special = false
}

data "template_file" "arm_template" {
  template = "${file("${path.root}/arm_templates/${var.template_file}")}"
}

resource "azurerm_template_deployment" "arm_deployment" {
  name                = "${var.resource_group}-deployment-${random_string.random_string.result}"
  resource_group_name = "${var.resource_group}"
  template_body       = "${data.template_file.arm_template.rendered}"

  parameters = "${var.template_parametes}"

  deployment_mode = "${var.deployment_mode}"
}

[r0bnet]

After a bit of research and a look into the API specs i will try to implement it and i already started.

#fyi: i'll at first only implement the managed SSIS integration runtime, not the self-hosted one.

I'd propose a structure like this and will update when i come across some inevitable changes.

resource "azurerm_data_factory_integration_runtime_managed" "integration_runtime" {
  name                = "managed-ssis-integration-runtime"
  description         = "My managed integration runtime"
  data_factory_name   = "datafactory1"
  resource_group_name = "my-resourcegroup"
  location            = "northeurope"
  
  node_size                        = "Standard_D8_v3"
  number_of_nodes                  = 2
  max_parallel_executions_per_node = 16
  edition                          = "Standard"
  license_type                     = "LicenseIncluded" # LicenseIncluded or BasePrize (1st is used if you don't already own a valid license, didn't use BasePrice)

  vnet_integration {
    vnet_id     = "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/my-network-rg/providers/Microsoft.Network/virtualNetworks/my-vnet"
    subnet_name = "default"
  }

  custom_setup_script {
    blob_container_uri = "https://myssisinstallationstore.blob.core.windows.net/setup-files" # is used for installing custom vendor scripts, e.g. SAP Hana Client
    sas_token          = "?st=2019-08-31T11%3A52%3A00Z&se=2026-08-07T11%3A52%3A00Z&sp=rwl&sv=2018-03-28&sr=c&sig=vutouO1KNhy4npyO6PvRLFWWC67XKyDEm61mSJDF2N8%3D" # SAS token to get access to the container. Permissions needed: read, write, list
  }

  catalog_info {
    server_endpoint        = "my-ssis-catalog-server.database.windows.net" # Azure SQL Server endpoint for the catalog to be deployed: DB will be called SSISDB
    administrator_login    = "ssis_admin" # SQL Server admin login
    administrator_password = "MyH4rDPassw0rd!" # SQL Server admin password
    pricing_tier           = "Basic" # pricing tier of the SSISDB
  }
}

//edit: @jungopro can you tell me what that dataFlowRuntime object is all about? I didn't find that in the API specs.

[mbfrahry]

Hey @r0bnet. This implementation looks good so far but I have a few tweaks that I'd like you to consider. First what're your thoughts on renaming the resource to azurerm_data_factory_integration_runtime_managed, this keeps it consistent with the other data factory resource naming patterns and hopefully makes it easier for a user find the integration runtime resource they're looking for by going from data_factory -> integration_runtime -> managed. We went that route because there are soooo many datasets and linked services that the data factory collection of resources would get very cluttered and confusing if we didn't implement some form of naming pattern. Also, it looks like you have quite a bit of information in catalog_info that isn't relevant to a catalog. I don't know a lot about this resource other than what's in the sdk and my little bit of finagling in the UI but do you mind confirming that what all the attributes in catalog_info are supposed to be there. Lastly, additional_properties are littered throughout all of these structures. I wouldn't worry about adding them to this first draft but it is something we don't want to forget as we get closer to shipping this resource.

[r0bnet]

Hi @mbfrahry thanks for your review. The renaming thing definitely makes sense and i'll update it accordingly.
In my opinion all the information in the catalog_info part is relevant because it's part of the SQL DB that is created where the SSIS packages will be stored / installed. I'll update the listing above and add comments that give information about the attributes' purpose.
This additional_properties stuff exists nearly everywhere but i didn't find a place where it is used. Neither in an ARM template nor in the API specs. So i omitted it to not confuse anyone. But i think it can easily be added later on as simple map or something.

[mbfrahry]

All that sounds good but I do want to confirm catalog_info. From looking at the sdk and the UI, catalog_info looks to only reference how to connect to the database. The other bits in your implementation edition/license/custom_setup_script are used to configure the properties of the ssis integration runtime and don't reference the catalog/database. Is that right or am I not understanding the full contest of catalog_info?

[r0bnet]

I reviewed it and you're totally right. I mixed it up because it wasn't clear from the ARM script i used. But yes they can be moved outside the catalog_info object. I updated the example above.
I'd have noticed it when i was implementing that stuff for sure but it's better to have it correct right from the beginning. ;)

[mbfrahry]

I totally get that. Thanks for taking the time to go over it early though. I'll be keeping any eye out for this so feel free to ask any questions if they come up

[jungopro]

//edit: @jungopro can you tell me what that dataFlowRuntime object is all about? I didn't find that in the API specs.

I believe this doc explains it. I can swear I had a better reference but I can't find it...
https://docs.microsoft.com/bs-latn-ba/azure/data-factory/control-flow-execute-data-flow-activity

[r0bnet]

PR: #4342

[muralidar44]

HI, I have deployed Azure SSIS IR in a VNET but the IR state is "stopped", I have then went to the portal and did VNET validation manually and updated, which made IR state "running". Could someone pl help me how to make IR running after the deployment or how to make the VNET validation using TF

[r0bnet]

Hey @muralidar44,
we're currently not starting the IR after the deployment (more or less) on purpose. You could start it with Powershell after deployment but I'd rather not start it after deployment.
Regarding the vnet validation: I think this is a feature of the portal and I didn't find an API call for that. Maybe it gets automatically validated when the IR gets deployed? Not sure tbh. Could you check deploying an IR with a invalid vnet config?

[muralidar44]

Thanks @r0bnet , i have added powershell task in my release pipeline to start IR with force and it succeeded. Even the VNET validation which is the option in UI also take care with PowerShell command. But when I use SQL Manage instance as my target Azure SQL to deploy SSIDB, i get following error. But I have commented Pricing Tier variable and function in my code. Please suggest..

Error 1:
Last operation 'Start' get the status 'Failed'.
Error code: CatalogPricingTierCannotBeSpecifiedForManagedInstance
Error message: 'catalogPricingTier' property cannot be specified when provisioning Integration Runtime with Azure SQL DB server that is Managed Instance.
Activity ID: 56318d86-a7ac-4182-8dad-bd14c865d736
For more details, please refer to https://go.microsoft.com/fwlink/?linkid=2099434
.

Code I am using to deploy Azure SSIS IR

`resource "azurerm_data_factory_integration_runtime_managed" "example" {
name = var.ir-name
data_factory_name = var.df-name
resource_group_name = var.resource-group-name
location = var.ir-location
node_size = var.nodesize
number_of_nodes = var.nodecount
max_parallel_executions_per_node = var.paraexecpernode
edition = var.edition
license_type = "LicenseIncluded" # LicenseIncluded or BasePrize (1st is used if you don't already own a valid license, didn't use BasePrice)

vnet_integration {
vnet_id = var.vnetid
subnet_name = var.subnet-name
}
catalog_info {
server_endpoint = var.sqlservername # Azure SQL Server endpoint for the catalog to be deployed: DB will be called SSISDB
administrator_login = var.sqluser # SQL Server admin login
administrator_password = var.sqlpassword # SQL Server admin password
#pricing_tier = var.pricing-tier # pricing tier of the SSISDB
}
}`

[vizel014]

Catalog Pricing Tier is not correct.

There are lots of types, for example, S1 Gen 5 vCore and more only Basic Works.

[favoretti]

@vizel014 Catalog Pricing Tier isn't related to the original issue, so I'm going to close this one. Would you mind opening a new one if the issue that you're facing is still relevant? Thank you.

[github-actions]

I'm going to lock this issue because it has been closed for 30 days ⏳. This helps our maintainers find and focus on the active issues.
If you have found a problem that seems similar to this, please open a new issue and complete the issue template so we can capture all the details necessary to investigate further.