Share the love

To migrate SSIS packages to Azure Data Factory (ADF) , you can use the following general steps. We will use Azure KeyVault to store connection strings.

  1. Set up an Azure Data Factory: Create an Azure Data Factory in the Azure portal, and configure the integration runtime (IR) to allow connectivity to your on-premises environment.
  2. Create an Azure Key Vault: Create an Azure Key Vault in the Azure portal, and configure it to store the secrets that your SSIS packages need to access.
  3. Export SSIS Packages from SQL Server: Use the SQL Server Integration Services (SSIS) catalog to export your existing SSIS packages as .ispac files.
  4. Create an SSIS project in ADF: Create an SSIS project in ADF, and import the exported SSIS packages by uploading the .ispac files.
  5. Configure SSIS package connections: Update the package connections to use the appropriate Azure-based data sources and update the connection strings to reference the secrets stored in Azure Key Vault.
  6. Deploy SSIS packages: Deploy the SSIS packages to the SSIS IR in ADF, and configure the package execution schedule.
  7. Test and Monitor: Test and Monitor the SSIS packages execution in ADF, and troubleshoot any issues that you may encounter.

Fetch secrets from KeyVault

Using SPN

  1. Create an Azure AD service principal: Create an Azure Active Directory (AAD) service principal and grant it the necessary permissions to access your Key Vault.
  2. Add Key Vault references to the SSIS project: In the SSIS project in Azure Data Factory, add references to the Key Vault and the AAD service principal by using the “Project Parameters” and “Project Connection Managers” options.
  3. Update connection strings in SSIS packages: In the SSIS packages, update the connection strings to reference the secrets stored in Azure Key Vault. You can use expressions to reference the Key Vault secrets, for example, instead of hardcoding the connection string in the package, you can use the following format: ” @{Microsoft.KeyVault(SecretUri=https://yourKeyvault.vault.azure.net/secrets/yoursecret)} “
  4. Deploy and test the packages: Deploy the SSIS packages to the SSIS Integration Runtime (IR) in Azure Data Factory and test the package execution to ensure that the connections are established correctly and the secrets are retrieved from Key Vault.

Using managed identity

It is possible to use a managed identity as an alternative to an AAD service principal for authenticating to Azure Key Vault when configuring SSIS package connections. A managed identity is an identity that is managed by Azure, and can be used to authenticate to Azure services without the need to manage a separate service principal.

To use a managed identity for SSIS package connections, you will first need to create a managed identity for your Azure Data Factory and then grant it the necessary permissions to access your Key Vault. Once you have set up the managed identity, you can then use it to authenticate to the Key Vault and retrieve the secrets, instead of using an AAD service principal.

You can use the following steps to configure the SSIS package connections to use a managed identity:

  1. Create a managed identity for your Azure Data Factory: In the Azure portal, navigate to the Azure Data Factory and create a managed identity.
  2. Grant permissions to the managed identity: In the Azure portal, navigate to the Key Vault and grant the managed identity the necessary permissions to access the secrets.
  3. Configure SSIS package connections: In the SSIS packages, update the connection strings to reference the secrets stored in Azure Key Vault. Use the managed identity to authenticate to the Key Vault and retrieve the secrets.
  4. Deploy and test the packages: Deploy the SSIS packages to the SSIS Integration Runtime (IR) in Azure Data Factory and test the package execution to ensure that the connections are established correctly and the secrets are retrieved from Key Vault.

Using a managed identity to authenticate to Azure Key Vault eliminates the need to manage a separate service principal, simplifying the authentication process. However, you will have to ensure that the Managed Identity is properly set up and has the necessary permissions to access the Keyvault.