Using Microsoft SQL Server Integration Services on Amazon RDS for SQL Server

This blog post was last reviewed or updated March, 2022.

You can now configure Microsoft SQL Server Integration Services (SSIS) on Amazon Relational Database Service (RDS) for SQL Server. SSIS works on Single-AZ and Multi-AZ DB instances for both Standard and Enterprise editions using either the 2016 or 2017 SQL Server major versions.

Previously, you could use RDS for SQL Server as a target source for SSIS, but you couldn’t use SSIS on the same server as the RDS for SQL Server database itself. Although it’s still possible to use SSIS on Amazon Elastic Compute Cloud (Amazon EC2), and you may have to for some use cases, direct integration with RDS for SQL Server provides a cost-effective and better experience by having it on the same RDS for SQL Server instance. If you’re currently running SSIS on Amazon EC2, you can now save costs by running SSIS directly on the same RDS DB instance as your SQL Server database.

This post describes how to configure and use SSIS on RDS for SQL Server DB instances. It explains the process of deploying an SSIS project and scheduling its execution using SQL Server Agent.

Prerequisites for SSIS

To configure SSIS on Amazon RDS for SQL Server, you must meet the following requirements:

• The RDS for SQL Server instance must be SQL Server 2016 Standard or Enterprise editions (13.00.5426.0.v1 or above) or SQL Server 2017 Standard or Enterprise editions (14.00.3223.3.v1 or above).
• The instance must be joined to an AWS Managed Microsoft Active Directory to enable Windows Authentication. For instructions, see Setting Up Windows Authentication for SQL Server DB Instances.
• You must enable CLR on the RDS instance by modifying the instance parameter group. For instructions, see Modifying Parameters in a DB Parameter Group and Modifying an Amazon RDS DB Instance.
• You must enable Amazon Simple Storage Service (Amazon S3) integration for transferring SSIS packages and output files between the instance and S3 bucket. For instructions, see Integrating an Amazon RDS for SQL Server DB Instance with Amazon S3.

For more information about supported SSIS features, see Limitations and Recommendations for SSIS.

Configuring SSIS

SSIS is activated on an RDS instance through an option group. For more information, see Working with Option Groups. You can create or use an existing option group for SQL Server based on the edition of your RDS DB instance.

1. On the Amazon RDS console, choose Option groups.
2. Choose Add option.
   
3. For Option name, choose SSIS.
   

Setting up SSIS permissions for the Windows Authenticated user

After you add the SSIS option, a database with the name SSISDB is created. To set up your SSIS permissions, complete the following steps:

1. Launch SQL Server Management Studio (SSMS).
2. Connect to the SQL Server database engine as a master user using SQL Server Authentication.

You see the SSISDB catalog, as shown in the following screenshot.

The master user also has permission to grant ssis_admin and ssis_logreader roles to any other user. Because SSIS needs a Windows authenticated user to work with the SSIS catalog, prepare the Windows authenticated user by completing the steps in the following sections.

Granting ssis_admin and ssis_logreader role to the domain user

To grant the role to the domain user, complete the following steps:

1. Choose Security from the object explorer in SSMS.
2. Choose Logins.
3. Choose the [<domain>\<user>] login from the list.
4. Choose User Mappings.
5. For Map, select SSIDB.
6. For Database role membership, select ssis_admin and ssis_logreader.
   
7. Choose OK.

Granting permissions required for running SSIS packages

To run the SSIS package using the SQL Server Agent, the Windows authenticated user must have certain permissions. To grant the required permissions to the Windows authenticated user, use SSMS to run the following query:

 USE [msdb]
GO
CREATE USER [mydomain\user_name] FOR LOGIN [mydomain\user_name]
GRANT EXEC ON msdb.dbo.rds_msbi_task TO [mydomain\user_name] with grant option
GRANT SELECT ON msdb.dbo.rds_fn_task_status TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_cancel_task TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_download_from_s3 TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_upload_to_s3 TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_delete_from_filesystem TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_gather_file_details TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_add_proxy TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_update_proxy TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_grant_login_to_proxy TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_revoke_login_from_proxy TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_delete_proxy TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_enum_login_for_proxy to [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_enum_proxy_for_subsystem TO [mydomain\user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_sqlagent_proxy TO [mydomain\user_name] WITH GRANT OPTION
ALTER ROLE [SQLAgentUserRole] ADD MEMBER [mydomain\user_name]
GO

USE [master]
GO
GRANT ALTER ANY CREDENTIAL TO [mydomain\user_name]
GO

Deploying an SSIS project

To deploy the SSIS package, you must move your ISPAC files to the DB instance and run the SSAS stored procedure. Deployment of the project directly into an RDS DB instance is not supported. Complete the following steps:

1. Create a new Integration Services project in SQL Server Data Tools (or open a project that you have created previously).
   
2. On the Solution Explorer tab, choose the solution and choose Properties.
3. Choose Common Properties.
4. For Project Protection Level, choose Do not save sensitive data.
5. Choose OK.
   
6. Choose OK again.
7. A pop-up window might appear with a message that you still need to change the ProtectionLevel property of already existing packages in this project.
8. Choose OK.
   
9. To change the protection level of your already existing package, complete the following steps:
   • a. Choose the package in the Solution Explorer.
   • b. In the Control Flow section, choose any empty position (right-click).
   • c. Choose Properties.
   • d. For ProtectionLevel, choose DontSaveSensitive.
     

10. Repeat this for all existing packages present in this project.
11. On the Solution Explorer tab, build your project.
    When you build the Integration Services project, SQL Server Data Tools generates the file in the project’s \Bin\Development For deployment, you require the <project name>.ispac file.
12. Upload the <project name>.ispac file to the S3 bucket on which Amazon S3 integration is already enabled.
13. Open SSMS and connect to your RDS for SQL Server instance as a Windows authenticated user and run the following stored procedure to download the project file from the S3 bucket to the local D:\S3 folder on the RDS instance:

    exec msdb.dbo.rds_download_from_s3 
    @s3_arn_of_file='arn:aws:s3:::sample-s3-bucket/testProject.ispac' , 
    @rds_file_path='D:\S3\testProject.ispac' , 
    @overwrite_file=1

14. To track the status of the stored procedure, use the following code:

    SELECT * FROM msdb.dbo.rds_fn_task_status(NULL,task_id)

    The task_id value is the ID of the task. Entering 0 for the task_id shows all tasks. Wait for the task lifecycle to show as SUCCESS before advancing to the next step. The task_info column also gives additional information on the state of the task.

15. Choose SSISDB Catalog (right-click) and create a folder; for example, sample-ssis-folder.
16. Call the SSIS_DEPLOY_PROJECT stored procedure to deploy the project into SSISDB. See the following code:

    exec msdb.dbo.rds_msbi_task 
    @task_type='SSIS_DEPLOY_PROJECT', 
    @file_path='d:\S3\testProject.ispac', 
    @folder_name='sample-ssis-folder',
    @project_name='testProject';

17. After the SSIS_DEPLOY_PROJECT task status changes to SUCCESS, you can see the project deployed under the SSIS catalog in SSMS.

Executing an SSIS package

After deploying the SSIS project into the SSIS catalog, you can run packages directly from SSMS (one-time executions) or schedule them by using SQL Server Agent. You must use a Windows authenticated login to execute SSIS packages.

Setting up database connection managers for SSIS projects

For local database connections, you can use SQL authentication. If you want to use Windows Authentication, use DB_instance_name.fully_qualified_domain_name as the server name of the connection string. For example, myssisinstance.corp-ad.example.com, where myssisinstance is the DB instance name and corp-ad.example.com is the FQDN.

For remote connections, always use SQL authentication.

Setting up an agent job for running the SSIS package

To schedule an SSIS package execution in SQL Server Agent, complete the following steps:

1. Log in as a Windows authenticated user.
2. Create a SQLServer credential that you use to execute the SSIS package. See the following code:

   USE [master]
   GO
   CREATE CREDENTIAL [SSIS_Credential] WITH IDENTITY = N'mydomain\user_name’, SECRET = N‘mysecret’
   GO

   Replace 'mydomain\user_name’ with your domain user and ‘mysecret’ with the password of your domain user.

3. Create the SSIS proxy, grant the SSIS subsystem access to it, and grant permission on the proxy to your domain user. See the following code:

   USE [msdb]
   GO
   EXEC msdb.dbo.sp_add_proxy @proxy_name=N'SSIS_Proxy',@credential_name=N'SSIS_Credential',@description=N''
   EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'SSIS_Proxy',@login_name=N'mydomain\user_name’
   EXEC msdb.dbo.rds_sqlagent_proxy @task_type='GRANT_SUBSYSTEM_ACCESS',@proxy_name='SSIS_Proxy',@proxy_subsystem='SSIS'
   GO

   Replace 'mydomain\user_name’ with your domain user.

4. Add a SQL Server Agent job for package execution with the domain user as the owner of the job.
   
5. Add a step that executes an SSIS package, making sure of the following:
   • a. Modify the step to Run as the proxy created earlier.
   • b. Use DB_instance_name.fully_qualified_domain_name as the server. For example, myssisinstance.corp-ad.example.com, where myssisinstance is the DB instance name and corp-ad.example.com is the FQDN.
   • c. Specify the package you would like to execute in this step.
     

Summary

This post showed how to configure and use SSIS on existing and new SQL Server 2016 and 2017 DB instances running on Amazon RDS. You also learned about the supportability, dependencies, and constraints of running SSIS for RDS SQL Server. With the availability of SSIS on RDS, you can run SSIS on existing or new RDS for SQL Server without having to host a separate instance of SSIS on Amazon EC2 or on-premises. Try out SSIS on RDS for SQL Server today and share your thoughts and questions in the comments.

About the Authors

Sumit Ahluwalia is a Software Development Engineer with Amazon Web Services. He works with the Amazon RDS team, focusing on commercial database engines and SQL Server. He enjoys working on technical challenges in Amazon RDS and is passionate about learning from and sharing knowledge with his teammates and AWS customers.

Chinni Bolapati is a Senior Database Engineer with Amazon Web Services. He works on the Amazon RDS team, focusing on commercial database engines, SQL Server and Oracle.

Garry Singh is a Solutions Architect with Amazon Web Services. He works with AWS customers to provide guidance and technical assistance to help them achieve the best outcome for Microsoft workloads on AWS.