Microsoft SQL Server Integration Service (SSIS) provides a platform for users to create, extract, transform, and load workflows by connecting to various data sources like relational database management services, flat files, XML files, and more. Before loading into the destination system, users can copy, cleanse, and process the data. SSIS allows developers to create extract, transform, and load (ETL) dataflows without writing complex codes.
If you’re still explicitly running SSIS on Amazon Elastic Compute Cloud (Amazon EC2) and paying for additional resources, you can now save costs by running SSIS directly on the same Amazon Relational Database Service (Amazon RDS) for SQL Server instance. Refer to Using Microsoft SQL Server Integration Services on Amazon RDS for SQL Server to learn more. If your use case demands better control and integration with the operating system, you can use Amazon RDS Custom for SQL Server, which gives you the flexibility to customize your database server host and operating system.
In this post, we show you how to configure and use SSIS on RDS Custom for SQL Server DB instances. We discuss migrating the package using Amazon Simple Storage Service (Amazon S3) and deploying an SSIS project in an RDS Custom for SQL Server database.
Solution overview
We implement the solution to deploy a SSIS package with the following high-level steps:
Create an RDS Custom for SQL Server instance.
Configure SSIS on Amazon RDS Custom for SQL Server.
Upload the .ispac file to Amazon S3.
Import the .ispac file into an RDS Custom for SQL Server EC2 instance.
Deploy the files to Amazon RDS Custom for SQL Server.
To deploy multiple packages, you can repeat Steps 3 and 4.
Prerequisites
To configure SSIS on Amazon RDS Custom for SQL Server, you must meet the following requirements:
The RDS Custom for SQL Server instance must be SQL Server 2019 Standard or Enterprise editions (15.00.4073.23.v1 or above).
The instance must be joined to AWS Directory Service to enable Windows Authentication. For instructions, see Setting Up Windows Authentication for SQL Server DB instances.
The SSIS project (.ispac) files must be uploaded to an S3 bucket that is accessible to Amazon RDS Custom for SQL Server. For instructions to generate an .ispac file from the project, refer to Deploy Integration Services (SSIS) Projects and Packages.
You must have the AWS Command Line Interface (AWS CLI) installed in order to copy the deployed SSIS packages to the EC2 instance’s Amazon Elastic Block Store (Amazon EBS) volume.
Because SSIS requires Windows Authentication, consider joining Amazon RDS Custom to either self-managed or AWS managed Active Directory. This way, in the event of host replacement, connecting to SSIS using Windows Authentication is seamless. Otherwise, if you add the local admin account as SQL login, it’s required every time a host replacement occurs.
Create an RDS Custom for SQL Server instance
To create an RDS Custom SQL Server instance on your account, refer to Setting up your environment for Amazon RDS Custom for SQL Server.
Configure SSIS on Amazon RDS Custom for SQL Server
It’s mandatory to enable Common Language Runtime (CLR) integration for SSIS. Check if CLR is already enabled for SQL Server. If it’s not enabled, refer to CLR Integration – Enabling.
Refer to Introduction to the SSIS Catalog database (SSISDB) for instructions on creating an SSIS catalog using SQL Server Management Studio (SSMS). The Integration Services Catalog is stored in the SSIS database that is created as part of this process. Provide a strong password, which is used to encrypt the primary key and save it in a secure location.
You need this password later to run the packages deployed from the secondary EC2 instance when Amazon RDS Custom for SQL Server in Multi-AZ is failed over (which we discuss in detail later in this post).
Upload the .ispac file to Amazon S3
After you build the package using SQL Server Data Tools (SSDT), it creates the integration services package (.ispac) file. Upload the .ispac files to an S3 bucket. Amazon RDS Custom for SQL Server must have access to this bucket. To learn how to create an S3 bucket and upload objects, visit Create your first S3 bucket and Uploading objects.
Import the .ispac file to the EC2 instance
Using the AWS CLI, copy the .ispac file to the EC2 instance from the S3 bucket. It’s recommended to use the D drive. For details on how to download the file, refer to Downloading an object. After the .ispac files are copied, we can proceed to the deployment stage.
Deploy the SSIS package to Amazon RDS Custom for SQL Server
Complete the following steps to deploy the SSIS package:
Open SSMS.
Connect to the localhost using a Windows account that was created in Directory Service.
Expand the folder SSISDB under Integration Service Catalogs.
Create a Sample folder where your projects are stored.
Choose (right-click) the Projects folder and choose Deploy Project.
When the integration service deployment appears, choose Next.
Enter the local path where you downloaded the. ispac file from the S3 bucket.
Select SSIS in SQL Server and choose Next.
Enter the destination server name and choose Connect.
Provide the path where the project will be located.
Review the values you entered, then choose Deploy.
Wait for the deployment to complete.
You can review the deployed package in SSMS.
Make sure to configure the package to validate the parameters are appropriate.
Ensure the connection string and other parameter values are defined appropriately. Edit the values to provide the right path to the input files.
To run the package, choose (right-click) it and choose Execute.
You can now view a report of your package.
Considerations
In this section, we discuss considerations around restoring the service primary key on a Multi-AZ RDS Custom for SQL Server instance. These guidelines help address a failover event or instance replacement. It is recommended that you have a checklist available to reconfigure the features when required.
Because the SSIS metadata information is encrypted using the database primary key, the following scenarios will encounter an error when you try to access this SSIS catalog:
Failover post-SSIS setup in Multi-AZ – After the initial failover event and after configuring SSIS, the primary database key is not available on the new primary.
Host replacement – When a host replacement event occurs on RDS Custom and the underlying EC2 instance gets recreated, you would need to run the firewall script again to make sure you are able to RDP to the RDS Custom underlying EC2 instance.
A primary key that is not encrypted by the service primary key must be opened by using the OPEN MASTER KEY (Transact-SQL) statement together with a password on each session that requires access to the primary key. To resolve the issue, enable automatic decryption of the primary key. To do this, run the following commands. Use the same password that you used on the primary to create the SSIS catalog:
To automatically manage this, you can also consider using sp_procoption to configure a startup procedure that runs at SQL Server startup and enables automatic decryption of the database primary key using the service primary key and is marked to automatically run every time the SQL Server service is started or restarted. For more information on the startup procedure, refer to sp_procoption.
Clean up
Complete the following steps to clean up the resources you created in this post:
On the Amazon RDS console, in the navigation pane, choose Databases.
Select the RDS Custom for SQL Server DB instance that you created for this tutorial and want to delete.
On the Actions menu, choose Delete.
To take a final snapshot, choose Create final snapshot and provide a name for the snapshot.
To retain automated backups, choose Retain automated backups.
Enter delete me in the box.
Choose Delete.
If you created a new EC2 instance and corresponding security groups for this tutorial, delete those resources as well.
Conclusion
In this post, you learned how you can migrate SQL Server Integration Service to Amazon RDS Custom for SQL Server. We used SQL Server Management Studio to install SSIS packages from an S3 bucket. This solution allows you to overcome the limitations of Amazon RDS for SQL Server, and you don’t need to host a separate instance of SSIS on an EC2 instance or on-premises server.
Let us know your thoughts and questions in the comments section.
About the authors
InduTeja Aligeti is a Senior Lead Database Consultant at AWS. She has over 18 years of experience working with Microsoft technologies with a specialization in SQL Server. She focuses on helping customers build highly available, cost-effective database solutions and migrate their large-scale SQL Server databases to AWS.
Kanwar Nain Singh is a Specialist Solutions Architect with Amazon Web Services. He has over 12 years of experience engineering and architecting migrations and modernization of database stacks including SQL Server.
Read MoreAWS Database Blog
