Explore SQL Server Integration Services (SSIS) on AWS RDS SQL Server

In the previous article, Deploy tabular databases in SSAS on AWS RDS SQL Server, we explored that AWS RDS SQL Server now supports SQL Server Analysis Service (SSAS). It enables you to implement tabular project models for RDS instance as well. In this article, we will explore the support of SQL Server integration service in the RDS environment.

The SSIS integration for AWS RDS SQL Server will be covered in two articles:

In this, the first article, we will cover the following topics:
- Overview of SSIS package on RDS instance
- Prerequisites
- Parameter group and Options group for SSIS feature
- SSIS DB permissions for master user and Windows authenticated user
In the second article, we will cover the following topics:
- Create an integration service project in Visual Studio 2019
- Deploy projects in SSIS RDS instance using S3 integration and stored procedures
- Create credentials, proxy accounts for SQL Server agent
- Create SQL Server agent jobs for SSIS package stored in the SSISDB catalog

Let’s get started with the SSIS feature in the AWS RDS SQL Server.

Introduction

Amazon gives you two options to implement SQL Server services:

Elastic Compute Cloud (EC2): We can deploy an EC2 instance and install SQL Server on it. An EC2 instance is similar to a virtual machine that we deploy in on-premise infrastructure. You have full control over the SQL Server, its services, version, edition, features in this
AWS RDS SQL Server: It is an AWS managed database platform for SQL Server. Here, the user does not worry about the infrastructure. They can select the supported SQL version and start using it. RDS instance comes with some feature restrictions

Previously, RDS supported only database service in SQL Server. Recently on May 19^th, 2020, Amazon announced that now RDS supports the SSIS package using SQL Server Integration Service in the AWS RDS SQL Server as well.

In this article, we will explore the process to enable SSIS, create an SSIS package, and deploy it in the RDS instance.

Overview of SSIS on AWS RDS SQL Server

SQL Server Integration Service (SSIS) is an integral and useful component of SQL Server. It helps us to design various data flows and transformations. It provides many useful transformations for cleaning, aggregating, merging data, FTP, and database maintenance tasks. We can also take data from various sources such as a flat-file, relational, non-relational databases, and design work flow, logics to extract or merge them.

Let’s explore SSIS feature requirements for an AWS RDS SQL instance.

SSIS feature availability

SSIS feature is available on both Single-AZ and Multi-AZ SQL Server configurations.

Note: You can refer article Multi-AZ Configuration for AWS RDS SQL Server to understand Single and Multi-AZ RDS SQL Server.

We will use a single AZ RDS instance in this article. You can follow the above article and configure it to use multi-AZ RDS with SSIS functionality.

Supported SQL editions and versions

It supports SQL Server 2016 and SQL Server 2017 standard or Enterprise edition.

SQL Server 2016: Minimum version supported is 13.00.5426.0.v1
SQL Server 2017: Minimum version supported is14.00.3223.3.v1

In this article, I use the AWS RDS SQL Server instance SQL Server 2017 version 14.00.3281.6.v1 standard edition, as shown below.

AWS Managed Microsoft active directory

The RDS instance should be a member of the AWS managed active directory. It allows you to connect to SQL Server with Windows authentication.

We already covered this required in the following two series of articles:

In the following screenshot, we see SQLShackdemo.com active directory in joined status for my RDS instance:

Amazon S3 bucket Integration for AWS RDS SQL Server

We need to integrate an Amazon S3 bucket with the RDS instance with an appropriate IAM role. RDS uses an S3 bucket to download and upload project-related files for the SSIS package.

You should refer to Integrating an AWS RDS SQL Server with Amazon S3 bucket to learn the integration process.

In the below screenshot, I have sqlshackrole in my RDS instance that is integrated with [sqlshackdemo] S3 bucket:

CLR integration on AWS RDS SQL Server

Enables Common Language Runtime(CLR) integration on the corresponding RDS instance. It allows RDS instance to use stored procedures, function, triggers written in any .Net language such as VB.net

To enable CLR integration on RDS SQL Server, first view the existing parameter group for RDS. The RDS instance should be in an available status to modify it. Click on modify and view its parameter group.

In my case, it is using the default parameter group i.e. default.sqlserver-se-14.0

We cannot modify a default parameter group. We create a new parameter group with additional configuration. In the RDS dashboard, click on the Parameter groups taband create a new parameter group:

In the Create parameter group window, select the SQL Server version, specify a group name and description. You should choose the correct DB instance version similar to your RDS instance version else it might work correctly with associated AWS RDS server.

Once you create it, select the new parameter group, open it, and click on edit parameters. Search for CLR enabled, change the value to 1, and save changes:

Now, go back to RDS instance and modify the option group from a default default.sqlserver-se-14.0 to new parameter group newparametergroup:

Verify the changes and apply them immediately. In case you do not want to apply it immediately, select to apply the changes during the next scheduled maintenance window. It also shows you the current maintenance window:

Option group in AWS RDS SQL Server to enable SSIS feature

Enable SSIS using the options group. In my RDS instance, it uses ssasforrds option group:

Navigate to the Option groups tab in the RDS dashboard, select the option group ssasforrds and click on the Add option button:

Select the SSIS option from the drop-down list and apply the changes immediately:

It will add SSIS to the existing option group:

We do not need to change the option group in the RDS instance because we modified the existing option group. In case you create a new option group, modify the RDS instance to utilize the new option group having an SSIS feature.

Now, connect to AWS RDS SQL Server using endpoint in SSMS. You can see a new database SSISDB is available in the Databases node:

You can expand the Integration Service Catalog node, and it will show the SSISDB catalog after we enabled the SSIS feature in the option group:

SSIS DB permissions for master and Windows authenticated user

As you know, RDS creates a master user once we create an AWS RDS instance. We cannot use the master user to execute the SSIS package in the RDS environment. The master user has the following permissions in the SSISDB:

alter on ssis_admin role
alter on ssis_logreader role
alter any user

This master user can provide the SSIS_ADMIN, SSIS_LOGREADER permissions to other users. We need permissions for Windows users so that Windows authentication can be used.

Expand the Security node in the Object Explorer panel, then the Logins node, and open the properties of the login for a Windows user. Click on the User Mapping tab, select the SSISDB database, and give SSIS_ADMIN, SSIS_LOGREADER permissions for the user:

We also need few additional permissions for the Windows user to run the SSIS package with the SQL Server agent job. In the below query, we provided these permissions to the user sqlshackdemo\rajendra.gupta. You can replace it with your windows user and execute the script. This script should run from the admin user security context in SSMS:

USE [msdb];

CREATE USER [sqlshackdemo\rajendra.gupta] FOR LOGIN [sqlshackdemo\rajendra.gupta];

GRANT EXEC ON msdb.dbo.rds_msbi_task TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;

GRANT SELECT ON msdb.dbo.rds_fn_task_status TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;

GRANT EXEC ON msdb.dbo.rds_cancel_task TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;

GRANT EXEC ON msdb.dbo.rds_download_from_s3 TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;

GRANT EXEC ON msdb.dbo.rds_upload_to_s3 TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;

GRANT EXEC ON msdb.dbo.rds_delete_from_filesystem TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;

GRANT EXEC ON msdb.dbo.rds_gather_file_details TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;

GRANT EXEC ON msdb.dbo.sp_add_proxy TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;

GRANT EXEC ON msdb.dbo.sp_update_proxy TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;

GRANT EXEC ON msdb.dbo.sp_grant_login_to_proxy TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;

GRANT EXEC ON msdb.dbo.sp_revoke_login_from_proxy TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;

GRANT EXEC ON msdb.dbo.sp_delete_proxy TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;

GRANT EXEC ON msdb.dbo.sp_enum_login_for_proxy TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;

GRANT EXEC ON msdb.dbo.sp_enum_proxy_for_subsystem TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;

GRANT EXEC ON msdb.dbo.rds_sqlagent_proxy TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;

ALTER ROLE [SQLAgentUserRole] ADD MEMBER [sqlshackdemo\rajendra.gupta];

USE [master];

GRANT ALTER ANY CREDENTIAL TO [sqlshackdemo\rajendra.gupta];

Conclusion

In the first article on the SSIS feature in AWS RDS SQL Server, we enabled SSIS services and provided permissions to the Windows user to connect using Windows authentication. In the second article, we will create an SSIS package, schedule it to run through SQL Server agent jobs.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023