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.
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.
- On the Amazon RDS console, choose Option groups.
- Choose Add option.
- 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:
- Launch SQL Server Management Studio (SSMS).
- 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_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:
- Choose Security from the object explorer in SSMS.
- Choose Logins.
- Choose the
[<domain>\<user>]login from the list.
- Choose User Mappings.
- For Map, select SSIDB.
- For Database role membership, select ssis_admin and ssis_logreader.
- 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:
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:
- Create a new Integration Services project in SQL Server Data Tools (or open a project that you have created previously).
- On the Solution Explorer tab, choose the solution and choose Properties.
- Choose Common Properties.
- For Project Protection Level, choose Do not save sensitive data.
- Choose OK.
- Choose OK again.
- A pop-up window might appear with a message that you still need to change the
ProtectionLevelproperty of already existing packages in this project.
- Choose OK.
- 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.
- Repeat this for all existing packages present in this project.
- 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\DevelopmentFor deployment, you require the
- Upload the
<project name>.ispacfile to the S3 bucket on which Amazon S3 integration is already enabled.
- 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:\S3folder on the RDS instance:
- To track the status of the stored procedure, use the following code:
task_idvalue is the ID of the task. Entering
task_idshows all tasks. Wait for the task
lifecycleto show as
SUCCESSbefore advancing to the next step. The
task_infocolumn also gives additional information on the state of the task.
- Choose SSISDB Catalog (right-click) and create a folder; for example,
- Call the
SSIS_DEPLOY_PROJECTstored procedure to deploy the project into SSISDB. See the following code:
- After the
SSIS_DEPLOY_PROJECTtask 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 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:
- Log in as a Windows authenticated user.
- Create a SQLServer credential that you use to execute the SSIS package. See the following code:
'mydomain\user_name’with your domain user and
‘mysecret’with the password of your domain user.
- 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:
'mydomain\user_name’with your domain user.
- Add a SQL Server Agent job for package execution with the domain user as the owner of the job.
- 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_nameas the server. For example,
myssisinstanceis the DB instance name and
corp-ad.example.comis the FQDN.
- c. Specify the package you would like to execute in this step.
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.