AWS Database Blog

Migrate Microsoft SQL Server SSIS Packages to Amazon RDS Custom for SQL Server

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:

  1. Create an RDS Custom for SQL Server instance.
  2. Configure SSIS on Amazon RDS Custom for SQL Server.
  3. Upload the .ispac file to Amazon S3.
  4. Import the .ispac file into an RDS Custom for SQL Server EC2 instance.
  5. 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 Serviceto 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.
  • EC2 Instance will be plugged out to AD after scale compute or host replacement, we can automate domain joining using SSM. Refer automating domain join for more details.

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.

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:

  1. Open SSMS.
  2. Connect to the localhost using a Windows account that was created in Directory Service.
  3. Expand the folder SSISDB under Integration Service Catalogs.
  4. Create a Sample folder where your projects are stored.
  5. Choose (right-click) the Projects folder and choose Deploy Project.
  6. When the integration service deployment appears, choose Next.
  7. Enter the local path where you downloaded the. ispac file from the S3 bucket.
  8. Select SSIS in SQL Server and choose Next.
  9. Enter the destination server name and choose Connect.
  10. Provide the path where the project will be located.
  11. Review the values you entered, then choose Deploy.
  12. Wait for the deployment to complete.
  13. You can review the deployed package in SSMS.
  14. Make sure to configure the package to validate the parameters are appropriate.
  15. Ensure the connection string and other parameter values are defined appropriately. Edit the values to provide the right path to the input files.
  16. To run the package, choose (right-click) it and choose Execute.

You can now view a report of your package.

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.

  1. Select the RDS Custom for SQL Server DB instance that you created for this tutorial and want to delete.
  2. On the Actions menu, choose Delete.
  3. To take a final snapshot, choose Create final snapshot and provide a name for the snapshot.
  4. To retain automated backups, choose Retain automated backups.
  5. Enter delete me in the box.
  6. Choose Delete.
  7. 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.