AWS Database Blog

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.

Solution overview

We implement the solution to deploy a SSIS package with the following high-level steps:

  1. Configure SSIS on Amazon RDS for SQL Server using the AWS CLI.
  2. Configure the Amazon S3 integration with Amazon RDS using the AWS CLI.
  3. Build and upload the SSIS package using PowerShell.

We can use the same procedure to deploy multiple packages by repeating step 3.

Prerequisites

To configure SSIS on Amazon RDS for SQL Server, you must meet the following requirements:

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.

  1. Create the parameter group (ssis-parameter-group) for SQL Server engine sqlserver-ee-15.0 in the us-east-2 Region: 
    aws rds create-db-parameter-group \
--db-parameter-group-name ssis-parameter-group \
--db-parameter-group-family sqlserver-ee-15.0 \
--description "my ssis parameter group" \
--region us-east-2
  2. Enable CLR for the parameter group (ssis-parameter-group) and apply it immediately to put it into effect: 
    aws rds modify-db-parameter-group \
 --db-parameter-group-name ssis-parameter-group \
 --region us-east-2 \
 --parameters "ParameterName='clr enabled',ParameterValue=1,ApplyMethod=immediate"
  3. Create the option group ssis-option-group for the same SQL Server engine to enable SSIS for Amazon RDS for SQL Server: 
    aws rds create-option-group \
 --option-group-name ssis-option-group \
 --engine-name sqlserver-ee \
 --major-engine-version 15.00 \
 --option-group-description test \
 --region us-east-2
  4. Add the SSIS option to the option group (ssis-option-group): 
    aws rds add-option-to-option-group \
 --option-group-name ssis-option-group \
 --options OptionName=SSIS \
 --region us-east-2 \
 --apply-immediately
  5. Apply the parameter group ssis-parameter-group to the RDS for SQL Server DB instance and apply it immediately to bring the change into effect: 
    aws rds modify-db-instance \
 --db-instance-identifier sqlssis \
 --db-parameter-group-name ssis-parameter-group \
 --option-group-name ssis-option-group \
 --apply-immediately \
 --region us-east-2

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:

  1. Upload the ISPAC files to an already existing S3 bucket or create a new one.
  2. 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. 
     aws iam create-role \
 --role-name rds-ssis-assume \
 --assume-role-policy-document file://c:/scripts/rdstrust.txt

    The file rdstrust.txt contains the following content, which includes the trust policy we use to grant Amazon RDS assume role access:

    {
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "rds.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}
  3. Create the IAM policy rds-ssis-policy with the new policy name: 
      aws iam create-policy \
 --policy-name rds-ssis-policy \
 --policy-document file://c:/scripts/iam.txt

    The following is the content of the file iam.txt, which creates the access policy for the S3 bucket ssis-testing to be attached to the role we created:

    {
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:AbortMultipartUpload",
                "s3:ListBucket",
                "s3:GetBucketAcl",
                "s3:GetBucketLocation",
                "s3:ListMultipartUploadParts"
            ],
            "Resource": [
		  "<arn of the s3 bucket>/*",
		  "<arn of the s3 bucket>         
            ]
        },
        {
            "Effect": "Allow",
            "Action": "s3:ListAllMyBuckets",
            "Resource": "*"
        }
    ]
}
  4. Attach the policy you created to the new role by providing the ARN of the policy: 
    aws iam attach-role-policy \
--policy-arn <ARN> \
--role-name rds-ssis-assume

    Refer to Finding Amazon Resource Names (ARNs) for more details.

  5. Apply the IAM role to the RDS for SQL Server instance, and use the ARN of the role you created: 
    aws rds add-role-to-db-instance \
 --db-instance-identifier sqlssis  \
 --feature-name S3_INTEGRATION \
 --region us-east-2 \
 --role-arn <ARN>

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_admin and 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:

$RdsDbCred = Get-Credential #Use the sql account created while creating the RDS instance for credetial.
$mydomain = 'example'
$user_name = 'admin'
$Db_instance = "<dbInstanceEndpoint>,1433"
$SqlCommand = @"
USE [SSISDB]
GO
CREATE USER [$mydomain\$user_name] FOR LOGIN [$mydomain\$user_name]
ALTER ROLE [ssis_admin] ADD MEMBER [$mydomain\$user_name]
ALTER ROLE [ssis_logreader] ADD MEMBER [$mydomain\$user_name]
GO

USE [msdb]
GO
CREATE USER [$mydomain\$user_name] FOR LOGIN [$mydomain\$user_name]
GRANT EXEC ON msdb.dbo.rds_msbi_task TO [$mydomain\$user_name] with grant option
GRANT SELECT ON msdb.dbo.rds_fn_task_status TO [$mydomain\$user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_task_status TO [$mydomain\$user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_cancel_task TO [$mydomain\$user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_download_from_s3 TO [$mydomain\$user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_upload_to_s3 TO [$mydomain\$user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_delete_from_filesystem TO [$mydomain\$user_name] with grant option
GRANT EXEC ON msdb.dbo.rds_gather_file_details TO [$mydomain\$user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_add_proxy TO [$mydomain\$user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_update_proxy TO [$mydomain\$user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_grant_login_to_proxy TO [$mydomain\$user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_revoke_login_from_proxy TO [$mydomain\$user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_delete_proxy TO [$mydomain\$user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_enum_login_for_proxy to [$mydomain\$user_name] with grant option
GRANT EXEC ON msdb.dbo.sp_enum_proxy_for_subsystem TO [$mydomain\$user_name]  with grant option
GRANT EXEC ON msdb.dbo.rds_sqlagent_proxy TO [$mydomain\$user_name] WITH GRANT OPTION
ALTER ROLE [SQLAgentUserRole] ADD MEMBER [$mydomain\$user_name]
GO

USE [master]
GO
GRANT ALTER ANY CREDENTIAL TO [$mydomain\$user_name]
GO
"@
Invoke-sqlcmd -Query $SqlCommand -ServerInstance $Db_instance -Credential $RdsDbCred

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:

$RdsDbCred = Get-Credential #Use the domain user credential.
$Db_instance = "<dbInstanceEndpoint>,1433"    
$Connstring = "Server=$Db_Instance; Database=ssisdb;User ID=$($RdsDbCred.Username); Password=$($RdsDbCred.GetNetworkCredential().Password);Integrated Security=true"
$SQL_Command_Download = @"

exec msdb.dbo.rds_download_from_s3 
@s3_arn_of_file='arn:aws:s3:::ssis-testing/SamplePkg.ispac', 
@rds_file_path='D:\S3\SamplePkg1.ispac', 
@overwrite_file=1
"@
$Task_Id = (Invoke-Sqlcmd -Query $SQL_Command_DownLoad -ConnectionString $Connstring).'task_id'
$Status_Query = "SELECT * FROM msdb.dbo.rds_fn_task_status(NULL,$Task_Id) ORDER BY task_id DESC"
while ((Invoke-Sqlcmd -Query $Status_Query -ConnectionString $Connstring).Lifecycle -notin @('SUCCESS','ERROR') -and $Count -ne 360){
    Write-Output "Waiting for task id $Task_Id to complete."
    Start-Sleep -seconds 5
    $Count++
}
If((Invoke-Sqlcmd -Query $Status_Query -ConnectionString $Connstring).Lifecycle -eq 'ERROR' -or $Count -eq 360){
    Write-Output "Kindly check your IAM permissions, and .ispac file are in order.$($Status_Query.'task_info')."    
}Else{
    Write-Output "Task id $Task_Id succeeded."    
}

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, testfolder1:

$RdsDbCred = Get-Credential #Use the domain user credential.
$Db_instance = "<dbInstanceEndpoint>,1433"
$FolderName = "testfolder1"
$SQL_Command = @"
DECLARE @FolderName SYSNAME = '$FolderName';
DECLARE @FolderDescription NVARCHAR(1024) = '$FolderName Description';
DECLARE @FolderID BIGINT = (SELECT folder_id FROM ssisdb.catalog.folders WHERE name = @FolderName);
IF @FolderID IS NULL
BEGIN
EXEC ssisdb.catalog.create_folder @folder_name = @FolderName, @folder_id = @FolderID OUTPUT;
EXEC ssisdb.catalog.set_folder_description @folder_name = @FolderName, @folder_description = @FolderDescription;
END
"@
$Connstring = "Server=$Db_Instance; Database=ssisdb;User ID=$($RdsDbCred.Username); Password=$($RdsDbCred.GetNetworkCredential().Password);Integrated Security=true"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $Connstring
$Cmd = New-object System.Data.SqlClient.SqlCommand($SQL_Command, $SqlConnection)
$SqlConnection.Open()
$cmd.ExecuteReader()
$SqlConnection.Close()

Deploy the SSIS package

Deploy the sample SSIS package SamplePkg1.ispac with the following code:

$RdsDbCred = Get-Credential #Use the domain user credential.
$Db_instance = "<dbInstanceEndpoint>,1433"
$Connstring = "Server=$Db_Instance; Database=ssisdb;User ID=$($RdsDbCred.Username); Password=$($RdsDbCred.GetNetworkCredential().Password);Integrated Security=true"
$SQL_Command_Execute = @"
exec msdb.dbo.rds_msbi_task 
@task_type='SSIS_DEPLOY_PROJECT', 
@file_path='d:\S3\SamplePkg1.ispac', 
@folder_name='testfolder1',
@project_name = 'RDS_SSIS_Project1'
"@
$Task_Id = (Invoke-Sqlcmd -Query $SQL_Command_Execute -ConnectionString $Connstring).'task_id'
write-output $SQL_Command_Execute
$Status_Query = "SELECT * FROM msdb.dbo.rds_fn_task_status(NULL,$Task_Id) ORDER BY task_id DESC"
while ((Invoke-Sqlcmd -Query $Status_Query -ConnectionString $Connstring).Lifecycle -notin @('SUCCESS','ERROR') -and $Count -ne 360){
    Write-Output "Waiting for task id $Task_Id to complete."
    Start-Sleep -seconds 5
    $Count++
}
If((Invoke-Sqlcmd -Query $Status_Query -ConnectionString $Connstring).Lifecycle -eq 'ERROR' -or $Count -eq 360){
    Write-Output "Check message $($Status_Query.'task_info')." 
}Else{
    Write-Output "Task id $Task_Id succeeded."
}

Conclusion

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.