Bulk Insert with SQL Server on Amazon RDS

Import and Bulk Insert Amazon S3 files directly to your Amazon RDS for SQL Server database.

Bobby Neelon
Sep 16, 2020 · 5 min read

TL;DR

Background

After a quick call with the software provider, it turned out that they are in the process of developing a RESTful API, but it wouldn’t be in production anytime soon.

Instead, they were able to provide an FTP folder from which we could retrieve an initial cumulative XML file to replicate the database, daily XML files that contain updates, and a handful of SQL Server helper scripts to create and update the database from the aforementioned XML files. Since we are running a SQL Server database on Amazon RDS for my client, that seemed like a reasonable solution.

First Steps

The Hiccup

Cannot bulk load because the file "C:\my_local_file_path\XXXX_20010101-20200810\XXXX_20010101-20200810.xml" could not be opened. Operating system error code 3(The system cannot find the path specified.)

I’d experienced this before trying to BULK INSERT .csv files and remembered that the referenced file needed to live on a drive accessible to the database, and not on my local machine.

The issue with that is that Amazon RDS is a managed database service. Typically, I view this as a positive because I’m not a DBA or infrastructure expert, and RDS gets me up and running with a fully functioning, durable relational database in minutes. In this instance, though, it was a negative because RDS didn’t allow access to the underlying operating and file systems…or does it?!

New SQL Server for RDS Feature

You can transfer files between a DB instance running Amazon RDS for SQL Server and an Amazon S3 bucket. By doing this, you can use Amazon S3 with SQL Server features such as BULK INSERT. For example, you can download .csv, .xml, .txt, and other files from Amazon S3 to the DB instance host and import the data from D:\S3\ into the database. All files are stored in D:\S3\ on the DB instance.

Thanks, AWS! How did you know I needed this?!

Enabling this Feature

1. Create a New S3 Bucket

2. Create an IAM policy for access to Amazon S3

{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "VisualEditor0",
"Effect": "Allow",
"Action": [
"s3:PutObject",
"s3:GetObject",
"s3:AbortMultipartUpload",
"s3:ListBucket",
"s3:GetBucketAcl",
"s3:GetBucketLocation",
"s3:ListMultipartUploadParts"
],
"Resource": [
"arn:aws:s3:::<bucket-name>",
"arn:aws:s3:::*/*"
]
},
{
"Sid": "VisualEditor1",
"Effect": "Allow",
"Action": "s3:ListAllMyBuckets",
"Resource": "*"
}
]
}

3. Create a New IAM Role and Attach the IAM Policy from above

4. Associate IAM Role with RDS Database

Make sure you are in the Connectivity & Security Tab and scroll all the way down until you hit Manage IAM roles.

Find and select the IAM role you just created in the first drop-down and choose S3_INTEGRATION from the second drop-down. Click “Add role.”

5. Transfer/Download Files between RDS and S3

You can use Amazon RDS stored procedures to download and upload files between S3 and your RDS DB instance. You can also use Amazon RDS stored procedures to list and delete files on the RDS instance.

Well that’s nice!

Stored procedures, like the one below, can be run right from SSMS:

exec msdb.dbo.rds_download_from_s3
@s3_arn_of_file='arn:aws:s3:::bucket-name/XXXX_20200818.xml',
@rds_file_path='D:\S3\bucket-name\XXXX_20200818.xml',
@overwrite_file=1;

After running that stored procedure, our XML file has been copied from S3 to the D drive on the RDS instance.

6. Re-Run the 3rd Helper Script

Next Steps

Coach Neelon

Data Analytics Professional, Cloud Computing Enthusiast, Sports Nerd

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store