Is there a way to run an SSIS package on scheduled manner from Azure SQL cloud?

Question

I have an Azure SQL instance as SaaS for my project, in which I have a database created. I have developed an SSIS package in my local based on this database. Now I want to schedule the execution of this SSIS package. Is there a way to do this from Azure cloud, by placing the .dtsx file there?

Answer 1

Yes you can run SSIS packages by using Azure SQL Managed Instance Agent :

https://learn.microsoft.com/en-us/azure/data-factory/how-to-invoke-ssis-package-managed-instance-agent

Prerequisites :

To use this feature, download and install latest SQL Server Management Studio (SSMS). Version support details as below:

• To run packages in SSISDB or file system, install SSMS version 18.5 or above.
• To run packages in package store, install SSMS version 18.6 or above.

You also need to provision an Azure-SSIS integration runtime in Azure Data Factory. It uses a SQL Managed Instance as an endpoint server.

Run an SSIS package in SSISDB:

In this procedure, you use SQL Managed Instance Agent to invoke an SSIS package that's stored in SSISDB.

In the latest version of SSMS, connect to a SQL Managed Instance.

Create a new agent job and a new job step. Under SQL Server Agent, right-click the Jobs folder, and then select New Job.

On the New Job Step page, select SQL Server Integration Services Package as the type.

On the Package tab, select SSIS Catalog as the package location.

Because SSISDB is in a SQL Managed Instance, you don't need to specify authentication.

Specify an SSIS package from SSISDB.

On the Configuration tab, you can:

• Specify parameter values under Parameters.
  • Override values under Connection Managers.
    • Override the property and choose the logging level under Advanced.
    Select OK to save the agent job configuration. Start the agent job to run the SSIS package.

Run an SSIS package in the file system:

In this procedure, you use SQL Managed Instance Agent to run an SSIS package that's stored in the file system.

In the latest version of SSMS, connect to a SQL Managed Instance.

Create a new agent job and a new job step. Under SQL Server Agent, right-click the Jobs folder, and then select New Job.

On the New Job Step page, select SQL Server Integration Services Package as the type.

On the Package tab:

For Package location, select File system.

  For **File source type**:
  
        If your package is uploaded to Azure Files, select **Azure file share**.
        
        ![Options for file source type](https://learn.microsoft.com/en-us/azure/data-factory/media/how-to-invoke-ssis-package-managed-instance-agent/package-source-file-system.png)
        
        The package path is **`\\<storage account name>.file.core.windows.net\<file share name>\<package name>.dtsx`**.
        
        Under **Package file access credential**, enter the Azure file account name and account key to access the Azure file. The domain is set as **Azure**.
        
              If your package is uploaded to a network share, select **Network share**.
              
              The package path is the UNC path of your package file with its .dtsx extension.
              
              Enter the corresponding domain, username, and password to access the network share package file.
              
                 If your package file is encrypted with a password, select **Encryption password** and enter the password.
                 
                 On the **Configurations** tab, enter the configuration file path if you need a configuration file to run the SSIS package. If you store your configuration in Azure Files, its configuration path will be **`\\<storage account name>.file.core.windows.net\<file share name>\<configuration name>.dtsConfig`**.
                 
                 On the **Execution options** tab, you can choose whether to use **Windows authentication** or **32-bit runtime** to run the SSIS package.
                 
                 On the **Logging** tab, you can choose the logging path and corresponding logging access credential to store the log files. By default, the logging path is the same as the package folder path, and the logging access credential is the same as the package access credential. If you store your logs in Azure Files, your logging path will be **`\\<storage account name>.file.core.windows.net\<file share name>\<log folder name>`**.
                 
                 On the **Set values** tab, you can enter the property path and value to override the package properties.
                 
                 For example, to override the value of your user variable, enter its path in the following format: **`\Package.Variables[User::<variable name>].Value`**.
                 
                 Select **OK** to save the agent job configuration.
                 
                 Start the agent job to run the SSIS package.
                 

Run an SSIS package in the package store:

Package store is not supported in Azure-SSIS IR in Azure Synapse.

In this procedure, you use SQL Managed Instance Agent to run an SSIS package that's stored in the Azure-SSIS IR package store.

1. In the latest version of SSMS, connect to a SQL Managed Instance.
2. Create a new agent job and a new job step. Under SQL Server Agent, right-click the Jobs folder, and then select New Job.
3. On the New Job Step page, select SQL Server Integration Services Package as the type.
4. On the Package tab:
   1. For Package location, select Package Store.
   2. For Package path: The package path is <package store name>\<folder name>\<package name>.
   3. If your package file is encrypted with a password, select Encryption password and enter the password.
5. On the Configurations tab, enter the configuration file path if you need a configuration file to run the SSIS package. If you store your configuration in Azure Files, its configuration path will be \\<storage account name>.file.core.windows.net\<file share name>\<configuration name>.dtsConfig.
6. On the Execution options tab, you can choose whether to use Windows authentication or 32-bit runtime to run the SSIS package.
7. On the Logging tab, you can choose the logging path and corresponding logging access credential to store the log files. By default, the logging path is the same as the package folder path, and the logging access credential is the same as the package access credential. If you store your logs in Azure Files, your logging path will be \\<storage account name>.file.core.windows.net\<file share name>\<log folder name>.
8. On the Set values tab, you can enter the property path and value to override the package properties. For example, to override the value of your user variable, enter its path in the following format: \Package.Variables[User::<variable name>].Value.
9. Select OK to save the agent job configuration.

Start the agent job to run the SSIS package.

Prerequisites

To use this feature, download and install latest SQL Server Management Studio (SSMS). Version support details as below:

• To run packages in SSISDB or file system, install SSMS version 18.5 or above.
• To run packages in package store, install SSMS version 18.6 or above.

You also need to provision an Azure-SSIS integration runtime in Azure Data Factory. It uses a SQL Managed Instance as an endpoint server.

Run an SSIS package in SSISDB

In this procedure, you use SQL Managed Instance Agent to invoke an SSIS package that's stored in SSISDB.

1. In the latest version of SSMS, connect to a SQL Managed Instance.
2. Create a new agent job and a new job step. Under SQL Server Agent, right-click the Jobs folder, and then select New Job.
3. On the New Job Step page, select SQL Server Integration Services Package as the type.
4. On the Package tab, select SSIS Catalog as the package location.
5. Because SSISDB is in a SQL Managed Instance, you don't need to specify authentication.
6. Specify an SSIS package from SSISDB.
7. On the Configuration tab, you can:
   • Specify parameter values under Parameters.
   • Override values under Connection Managers.
   • Override the property and choose the logging level under Advanced.
   
8. Select OK to save the agent job configuration.
9. Start the agent job to run the SSIS package.

Run an SSIS package in the file system

In this procedure, you use SQL Managed Instance Agent to run an SSIS package that's stored in the file system.

1. In the latest version of SSMS, connect to a SQL Managed Instance.
2. Create a new agent job and a new job step. Under SQL Server Agent, right-click the Jobs folder, and then select New Job.
3. On the New Job Step page, select SQL Server Integration Services Package as the type.
4. On the Package tab:
   1. For Package location, select File system.
   2. For File source type:
      • If your package is uploaded to Azure Files, select Azure file share. The package path is \\<storage account name>.file.core.windows.net\<file share name>\<package name>.dtsx. Under Package file access credential, enter the Azure file account name and account key to access the Azure file. The domain is set as Azure.
      • If your package is uploaded to a network share, select Network share. The package path is the UNC path of your package file with its .dtsx extension. Enter the corresponding domain, username, and password to access the network share package file.
   3. If your package file is encrypted with a password, select Encryption password and enter the password.
5. On the Configurations tab, enter the configuration file path if you need a configuration file to run the SSIS package. If you store your configuration in Azure Files, its configuration path will be \\<storage account name>.file.core.windows.net\<file share name>\<configuration name>.dtsConfig.
6. On the Execution options tab, you can choose whether to use Windows authentication or 32-bit runtime to run the SSIS package.
7. On the Logging tab, you can choose the logging path and corresponding logging access credential to store the log files. By default, the logging path is the same as the package folder path, and the logging access credential is the same as the package access credential. If you store your logs in Azure Files, your logging path will be \\<storage account name>.file.core.windows.net\<file share name>\<log folder name>.
8. On the Set values tab, you can enter the property path and value to override the package properties. For example, to override the value of your user variable, enter its path in the following format: \Package.Variables[User::<variable name>].Value.
9. Select OK to save the agent job configuration.
10. Start the agent job to run the SSIS package.

Run an SSIS package in the package store

Package store is not supported in Azure-SSIS IR in Azure Synapse.

In this procedure, you use SQL Managed Instance Agent to run an SSIS package that's stored in the Azure-SSIS IR package store.

1. In the latest version of SSMS, connect to a SQL Managed Instance.
2. Create a new agent job and a new job step. Under SQL Server Agent, right-click the Jobs folder, and then select New Job.
3. On the New Job Step page, select SQL Server Integration Services Package as the type.
4. On the Package tab:
   1. For Package location, select Package Store.
   2. For Package path: The package path is <package store name>\<folder name>\<package name>.
   3. If your package file is encrypted with a password, select Encryption password and enter the password.
5. On the Configurations tab, enter the configuration file path if you need a configuration file to run the SSIS package. If you store your configuration in Azure Files, its configuration path will be \\<storage account name>.file.core.windows.net\<file share name>\<configuration name>.dtsConfig.
6. On the Execution options tab, you can choose whether to use Windows authentication or 32-bit runtime to run the SSIS package.
7. On the Logging tab, you can choose the logging path and corresponding logging access credential to store the log files. By default, the logging path is the same as the package folder path, and the logging access credential is the same as the package access credential. If you store your logs in Azure Files, your logging path will be \\<storage account name>.file.core.windows.net\<file share name>\<log folder name>.
8. On the Set values tab, you can enter the property path and value to override the package properties. For example, to override the value of your user variable, enter its path in the following format: \Package.Variables[User::<variable name>].Value.
9. Select OK to save the agent job configuration.
10. Start the agent job to run the SSIS package.