Migrate SSIS packages from Amazon S3 to Amazon RDS for SQL Server using the AWS CLI and PowerShell scripts
In this post, we explain how you can use the AWS Command Line Interface (AWS CLI) and PowerShell scripts to migrate SSIS project (.ispac) files in an Amazon Simple Storage Service (Amazon S3) bucket to an Amazon Relational Database Service (Amazon RDS) for SQL Server database.
Microsoft SQL Server Integration Services (SSIS) is a component that you can use to perform a broad range of data migration tasks. With SSIS, you can extract and transform data from a wide variety of sources, such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations.
Amazon RDS for SQL Server supports running SSIS directly on an RDS DB instance. You can enable SSIS on an existing or new DB instance. SSIS is installed on the same DB instance as your database engine. SSIS works on Single-AZ and Multi-AZ DB instances for both Standard and Enterprise editions using either the 2016, 2017, or 2019 SQL Server major versions. Direct integration of SSIS with Amazon RDS for SQL Server provides a cost-effective and better experience. To implement this integration, certain configuration is required on Amazon RDS for SQL Server. Using the command line, we can further simplify the efforts involved to deploy SSIS packages on Amazon RDS for SQL Server.
We implement the solution to deploy a SSIS package with the following high-level steps:
- Configure SSIS on Amazon RDS for SQL Server using the AWS CLI.
- Configure the Amazon S3 integration with Amazon RDS using the AWS CLI.
- Build and upload the SSIS package using PowerShell.
We can use the same procedure to deploy multiple packages by repeating step 3.
To configure SSIS on Amazon RDS for SQL Server, you must meet the following requirements:
- Amazon Elastic Compute Cloud (Amazon EC2) with Microsoft Windows Server 2019 Base AMI and the following tools installed for connecting to Amazon RDS for SQL Server instance:
- The AWS CLI installed on Windows. For instructions, refer to Installing or updating the latest version of the AWS CLI. Use the following command:
- AWSPowerShell.NetCore installed on Windows. For instructions, refer to Installing the AWS Tools for PowerShell on Windows. Use the following command:
- The RDS for SQL Server instance must be SQL Server 2016 Standard or Enterprise editions (13.00.5426.0.v1 or above), SQL Server 2017 Standard or Enterprise editions (14.00.3223.3.v1), or SQL Server 2019 Standard or Enterprise (220.127.116.1143.16.v1).
- The instance must be joined to AWS Directory Service for Microsoft Managed Active Directory to enable Windows Authentication. For instructions, see Setting up Windows Authentication for SQL Server DB instances.
- The SSIS project (.ispac) files must be copied to an S3 bucket that is accessible to Amazon RDS for SQL Server. For instructions to generate .ispac file from the project, Refer to Deploy Integration Services (SSIS) Projects and Packages to learn how to build ISPAC files from project.
For more information, refer to Using Microsoft SQL Server Integration Services on Amazon RDS for SQL Server and review the limitations and recommendations for SSIS.
Configure SSIS on Amazon RDS for SQL Server using the AWS CLI
First we create parameter and option groups and apply them to the RDS instance. This section provides details on how to use the AWS CLI to perform these steps. 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.
The following example demonstrates how to create a new database parameter group and enable CLR to configure SSIS on Amazon RDS for SQL Server. The default parameter group has predefined values set, which are specific to database engine. We must explicitly enable CLR to configure SSIS for Amazon RDS for SQL Server, we need a new parameter group for that. After you create a DB parameter group, you must associate it with your Amazon RDS for SQL Server DB instance using the
ModifyDBInstance API. This requires a reboot of your DB instance for the new DB parameter group and associated settings to take effect.
- Create the parameter group (
ssis-parameter-group) for SQL Server engine
- Enable CLR for the parameter group (
ssis-parameter-group) and apply it immediately to put it into effect:
- Create the option group
ssis-option-groupfor the same SQL Server engine to enable SSIS for Amazon RDS for SQL Server:
- Add the SSIS option to the option group (
- Apply the parameter group
ssis-parameter-groupto the RDS for SQL Server DB instance and apply it immediately to bring the change into effect:
Configure Amazon S3 integration with Amazon RDS for SQL Server using the AWS CLI
In this section we enable Amazon S3 integration with Amazon RDS for SQL Server. This integration allows us to transfer SSIS packages and output files between the S3 and a directory (
D:\S3\) inside the RDS instance server. All files are stored in
D:\S3\, which is understandable by the DB instance. For instructions, see Integrating an Amazon RDS for SQL Server DB instance with Amazon S3. To complete the integration using the AWS CLI, complete the following steps:
- Upload the ISPAC files to an already existing S3 bucket or create a new one.
- Create the AWS Identity and Access Management (IAM) role (
rds-ssis-assume), which grants access to the S3 bucket. Take note of the ARN, we use it in a later step.
rdstrust.txtcontains the following content, which includes the trust policy we use to grant Amazon RDS assume role access:
- Create the IAM policy
rds-ssis-policywith the new policy name:
The following is the content of the file
iam.txt, which creates the access policy for the S3 bucket
ssis-testingto be attached to the role we created:
- Attach the policy you created to the new role by providing the ARN of the policy:
Refer to Finding Amazon Resource Names (ARNs) for more details.
- Apply the IAM role to the RDS for SQL Server instance, and use the ARN of the role you created:
Build and upload the SSIS package
After you build the package using SSDT, you create the integration services package. This file is a deployed package that we use to migrate to Amazon RDS for SQL Server.
The file must be uploaded to the S3 bucket that you created earlier. You can upload the file via the Amazon S3 console.
Create SSIS permission and roles
Use the following PowerShell script to create the SSIS permission and roles for the domain user for SSIS package deployment. This script takes care of adding the
ssis_logreader roles to the domain user
example\admin. When prompted for credentials, use the RDS for SQL Server primary account.
The following script provides the permissions to the domain user
example\admin in the MSDB and SSISDB database:
In the following steps, make sure to connect to your RDS for SQL Server instance as the Windows authenticated user
example\admin, which you created in the previous step.
Validate and download the SSIS package
To deploy the SSIS package to Amazon RDS for SQL Server, we need to first move the files from the S3 bucket to the
D:\S3 directory, inside the RDS for SQL Server DB instance. You can do this by running the SQL stored procedure to download the package from Amazon S3 to Amazon RDS for SQL Server:
Create a catalog folder in the DB instance
We need to create a catalog to deploy the SSIS package and create a project structure in Amazon RDS for SQL Server. Use the following code to create your own catalog folder, for example,
Deploy the SSIS package
Deploy the sample SSIS package
SamplePkg1.ispac with the following code:
In this post, we demonstrated how you can migrate an SSIS package from Amazon S3 to Amazon RDS for SQL Server. The direct integration of SSIS on Amazon RDS for SQL Server provides a cost-effective and better experience, without the need to host separate instances of SSIS on Amazon EC2 or on-premises. For more information, refer to Using Microsoft SQL Server Integration Services on Amazon RDS for SQL Server. Try out the PowerShell scripts to deploy SSIS on Amazon RDS for SQL Server and provide your feedback and thoughts in the comments section.
About the authors
InduTeja Aligeti is a Senior Database Consultant at AWS Professional Services team. She has vast experience working with Microsoft Technologies with a specialization in SQL Server. She focuses on helping customers to design and build high-available, cost-effective database solutions and migrate their large scale SQL Server databases to AWS.
Alamelu Palaniappan is a Database Consultant at AWS. She has 15+ years of experience working with SQL server database. She helps build technical solutions that assist customers to migrate and optimize their on-premises database workload to the AWS Cloud.
Soumyajyoti Biswas is a System Development Engineer with Finance Automation team. He has worked at Amazon for more than six years, and his primary area of interest is using the AWS cloud to assist clients in scaling their workloads. When not assisting clients, he enjoys spending time with loved ones and on road trips.