How to Import data from S3 directly into my sqlserver database on amazon EC2

Question

How to Import data from S3 directly into my sqlserver database on amazon EC2

psingla

Hall of Fame

Points: 3840
More actions
October 1, 2013 at 8:34 am

#278621

How can i take text files, or csv files from s3 and directly upload/insert them into a table in my sql server?
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

EdVassie SSC Guru Points: 60478 More actions · Answer 1

Have you looked at S3Browser. There are both GUI and command-line versions available.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

psingla Hall of Fame Points: 3840 More actions · Answer 2

Thanks,

Actually I am new to it and haven't used this tool.

I will test it.

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/

SQLAli Ten Centuries Points: 1109 More actions · Answer 3

I did not get any method to upload data directly from S3. Could any one provide me steps if it is possible?

Thanks,
I’m nobody but still I’m somebody to someone………….

tripleAxe SSCertifiable Points: 5706 More actions · Answer 4

I'm not aware of any method to do this directly, however I have automated a process to do this using SSIS and the S3 command line utility from CodePlex (http://s3.codeplex.com/)

RTaylor2208 SSChampion Points: 13298 More actions · Answer 5

The easiest way to programmatically get files in and out of S3 is using the AWS SDK for your chosen language such as powershell or python.

Here is an example of a function to upload a file to S3 using python:

import boto

import os

from boto.s3.connection import S3Connection

from boto.s3.key import Key

def UploadFileToS3(access_key, secret_key, s3_bucket, s3_path, file):

#Load the keys into memory within the OS

os.environ['AWS_ACCESS_KEY_ID'] = access_key

os.environ['AWS_SECRET_ACCESS_KEY'] = secret_key

#Create file name for S3 storage

file_name_to_use_in_s3 = "%s/%s"%(s3_path.lower(), os.path.basename(file))

#Create a connection to S3

conn = S3Connection()

bucket = conn.get_bucket(s3_bucket.lower())

#Create a new key object for the file to be written

k = Key(bucket)

k.key = os.path.basename(file)

k.name = file_name_to_use_in_s3

#Write the contents of the key in S3

k.set_contents_from_filename(file)

#Return the name and location of the file in S3

return s3_bucket + "/" + str(k.name)

MCITP SQL 2005, MCSA SQL 2012

RTaylor2208 SSChampion Points: 13298 More actions · Answer 6

However to load a file to a db direct from S3 you would need to use powershell to retrieve the file and pipe the file into a cmdlet to load the data into a table. I don't have an example that I can share due to some of the contents of the script being sensitive but it is possible.

MCITP SQL 2005, MCSA SQL 2012