Rajendra Gupta
package build status

Working with SSIS packages on AWS RDS SQL Server

June 3, 2020 by

In the first part of the article, Explore SQL Server Integration Services (SSIS) on AWS RDS SQL Server, we understood that AWS RDS SQL Server supports SSIS functionality starting from May 19th, 2020.

In this article, we will create, deploy, and schedule SSIS packages using SQL Server Agent jobs. You should configure your environment with prerequisites and steps mentioned in the first article specified above.

Create and Deploy an SSIS project

We use Visual Studio 2019 SQL Server data tools to create an integration service project. Once we installed visual studio 2019 using Download SQL Server Data Tools (SSDT) for Visual Studio, we need to enable the integration service extension.

You should install it in the Amazon EC2 Windows instance. I have the following EC2 instance, and it is a member of the [SqlshackDemo] domain. It is the same domain that we used in the RDS instance.

Install SQL Server Integration Service Projects extension

Open any project in Visual Studio and go to Extensions > Manage Extension from the menu bar. It opens the following market place to download extensions. Search for the SQL Server Integration Service Projects and download it. Integration Service Projects extension

Launch the setup and select the language of the installer. By default, it chooses system language. You can change it if required.

Select the language

Click Ok, and it starts the integration service project installation in the Visual Studio.

integration service projects installation

We have already installed Visual Studio 2019, so it integrates the integration service package with it. In case you have multiple versions, you can choose the required version.

Integrate SSIS with Visual Studio

As we can see below, SQL Server Integration Services Projects version 3.7 in the Visual Studio 2019 on Amazon EC2 instance.

Setup completed

Create an SSIS package using Visual Studio 2019 for AWS RDS SQL Server

Launch Visual Studio in the EC2 instance, create a new project and select the category – Integration Service Project.

Integration Service Project.

For configuration, specify the project name and location. I give the project name – SSISforRDS.

Configure project

It opens the following SSIS project. You can see the SSIS toolbox in the left and SSIS packages along with its parts like control flow in the solution explorer ( right-side).

Package overview

For this article, my primary intension is to guide you for creating an SSIS project that uses AWS RDS SQL Server integration service. We will create a basic SSIS package here.

My RDS SQL instance holds [AdventureWorksDW2014] database. You can download this sample database from Microsoft docs. You can follow the article Recover Data in AWS RDS SQL Server to restore it on the RDS instance using the S3 bucket.

Create the following stored procedure to select the records from a SQL view.

Create the following stored procedure to select the records from a SQL view.

Now, drag an Execute SQL Task from the SSIS toolbox.

Add an Execute SQL Task

Double-click on the execute SQL task to open its configuration editor.

SQL task editor Click on the Connection-> New Connection. In the connection window, specify the RDS endpoint as the server name if you do not have an endpoint in handy, login to AWS console, and get it from the AWS RDS SQL Server dashboard.

In the authentication, always use Windows authentication. We cannot use SQL authentication for SSIS in the RDS environment.

RDS endpoint

Click Ok and Specify the SQL statement to execute the stored procedure, we created earlier.

Specify SQL statement

Click Ok. Now, right-click on the SSIS solution and go to its properties. Here, you can see the project creation date, the EC2 instance name, the Windows authenticated user, and the project name.

SSIS solution properties for AWS RDS SQL Server

In security, it sets the default protection level as EncryptSensitiveWithUserKey. Change this Protection level to DonotSaveSensitive.

SSIS package protection level

You get a warning message if you change the package protection level. Click Ok to accept the warning.

Warning message

We also need to set the same protection level for the SSIS package. Right-click on the control flow area and go to properties. Change the protection level to DonotSaveSensitive, as shown in the below image.

Change the protection level

Save the package and click on Build-> Build Solution.

Build Solution

It builds the package for you, and you get the status in the output tab.

package build status

Download project ISPAC file from S3 bucket to AWS RDS SQL Server

Browse the SSIS project directory in the EC2 instance. In the Bin-> Development folder, you can find a project file with .ispac Extension similar to the below file.

project Development folder

Login to AWS web console and upload this project.ispac file in the S3 bucket that is already integrated with AWS RDS SQL Server.

Upload .ISPAC file to S3 bucket

To download this file from the S3 bucket to the RDS directory ( D:\S3), we use the RDS stored procedure msdb.dbo.rds_download_from_s3 with the following arguments.

  • @s3_arn_of_file: It is the Amazon resource name(ARN) of the S3 bucket .ispac file. Its format is arn:aws:s3:::<bucketname>/FilenameWithExtension
  • @rds_file_path: It is the directory for the RDS instance. It is always D:\S3 for RDS SQL Server

You should run this procedure in SSMS connected using Windows authenticated AWS RDS SQL Server.

It starts a task to execute the stored procedure. Note-down the task id and pass it in the msdb.dbo.rds_fn_task_status to check the status.

Stored procedure msdb.dbo.rds_fn_task_status

It shows success status that means RDS downloaded the ISPAC file in the RDS instance directory.

Task successful

Create a folder in SSISDB integration Service catalog

Before we move further, right-click on the SSISDB catalog and create a folder. In this case, we are connected with the RDS admin user.

Create a folder in SSISDB catalog

You get the following error message. We get this error because the ADMIN user does not have permission to create folders in the SSIS catalog.

Permission error

Let’s try the same operation with Windows authenticated users. We create a folder [Sample SSIS project] in the SSISDB catalog.

Create a new folder using Windows authentication

It created the folder, as shown below. We use this folder to deploy the project created earlier.

View the folder

Deploy an SSIS project in the SSISDB catalog of AWS RDS SQL Server

We use the stored procedure msdb.dbo.rds_msdb_task to deploy an SSIS project.

In the above query, we use the following arguments.

  • @task_type: SSIS_DEPLOY_PROJECT
  • @file_path: It is the file path of the project ispac file in the AWS RDS SQL Server
  • @folder_name: It is the SSIS catalog folder in which we want to deploy the project. We already created the catalog folder as Sample SSIS Project
  • @project_name: Specify the project name that we have selected earlier while creating the SSIS project in the Visual Studio

SSIS deploy project

We have deployed a package successfully as it shows the SUCCESS status in the msdb.dbo.rds_fn_task_status.

Task status

Now, refresh the SSMS connection, and you see the deploy SSIS package and project in the integration service project.

View the deployed project

Execute an SSIS project from the SSISDB catalog manually

We can either manually execute the package from the SSISDB catalog or execute it using the SQL job. Right-click on the package and select Execute, as shown below.

Execute package manually

It opens the following execute package window. You can verify the RDS instance connection and package properties.

Verify connections

Click Ok, and it starts an operation ID for the package execution.

Operation ID for SSIS package

Click Ok, and it opens the package execution report. Oh! The package failed in execution. It is unable to open the connection to the RDS instance. It is surprising, right! We already connected to RDS using Windows authentication and it works fine, but in the package, it fails to acquire a connection.

Failed package

To resolve this error, right-click on the package and select Configure.

Configure project

In the SSIS package for AWS RDS SQL Server, we need to specify the server name in the following format: <RDS Instance Name>.<RDS Domain fully qualified domain name(FQDN)>

We have the following connection string in the package that uses the RDS endpoint.

Current Connection String:

Data Source=ssasinrds.cumznzii9fba.us-east-1.rds.amazonaws.com;Initial Catalog=AdventureWorksDW2014;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;

Replace this connection string that uses the server name in the format specified above.

  • RDS instance: ssasinrds
  • Domain: SQLshackdemo.com

New Connection String

New: Data Source=ssasinrds.sqlshackdemo.com;Initial Catalog=AdventureWorksDW2014;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;

Modify Connection string

Click Ok and again open the configuration wizard. Now, replace the server name in the new format.

Edit server name for AWS RDS SQL Server

Once both the changes complete, execute the SSIS package. You can validate the connection string using the new server format.

Verify changes

We can see the package executes successfully after making the change for the RDS instance connection in the new format.

Success package

Execute an SSIS package using the SQL Server Agent job

Usually, we set up a SQL Server Agent job to execute the package at a scheduled time. We can set up a SQL agent job for package execution in the AWS RDS SQL Server as well.

It requires setting up a credential and proxy account for job execution.

Create a credential for proxy setup

It uses the CREATE CREDENTIAL command. Specify the Windows user (Identity argument) and password (Secret argument) in the query below.

Create a credential for proxy setup

Create a proxy account that uses the credential created above

In this step, we create a proxy with the credential created in the above step. We also need to give permissions to run the integration service package using the msdb.dbo.rds_sqlagent_proxy procedure.

Create a proxy account

Set up a SQL agent job to run SSIS package in AWS RDS SQL Server

Expand SQL Server Agent, jobs, and create a new job. In this specify a job name, and you can specify a description as well.

Set up a SQL agent job

Click on the Steps and add a new job step using the following inputs.

  • Specify a job step name
  • Change the type from the drop-down as SQL Server Integration Service Package
  • Select the proxy account from the drop-down in the Run as section
  • Specify the server name in the new format – <RDS Instance Name>.<RDS Domain fully qualified domain name(FQDN)>

Add a new job step

Click on the eclipse in the package and select the package that we wish to execute.

Select the package from SSISDB catalog

You can see the job step configuration, as shown below.

View package

You can create a job schedule as per your requirement.

Create a job schedule

We can see the SQL agent job to run the package in the AWS RDS SQL Server.

View job

Right-click on the job to execute it or wait for its autorun in the scheduled time. We can see below job that holds the integration service package executed successfully

Successful job execution

Conclusion

In the two series article, we explored the configuration of integration services for the AWS RDS SQL Server. We deployed an SSIS project, executed it manually, and using the SQL Server agent job as well. It is a useful enhancement to RDS SQL services, and I would recommend you to explore it.

Rajendra Gupta
190 Views