December 18, 2014 at 2:13 am
Hello
I need to some help trying to connect the an Amazon S3 FTP site. We have a third party providing us data via amazon S3 FTP site. the default SSIS FTP task can't connect to it and I have looked online and there are connectors that you can get; however, I cant use them due to the cost of the connector. Does anyone know of a way to connect to an S3 FTP site via SSIS or a free connector that will allow me to ?
Any help would be much appreciated
🙂
January 5, 2015 at 6:44 am
There is a few ways of doing this, three methods I have used is:
1. Download and install the AWS SDK for powershell and then use the Powershell commands for interacting with S3 (http://aws.amazon.com/documentation/powershell/). Use the exec system task in SSIS.
2. Download and install python and the python SDK for AWS called boto, and use the boto methods to get the files from S3 (http://aws.amazon.com/sdk-for-python/) Use the exec system task in SSIS.
3. Use the AWS Command Line Interface to create batch file to get the files and call it using a exec system task (http://docs.aws.amazon.com/cli/latest/reference/s3/index.html)
All the methods require you to have the access and secret AWS keys for your user account.
MCITP SQL 2005, MCSA SQL 2012
January 5, 2015 at 6:47 am
Here is an example python module I have created for uploading \ deleting and downloading files from S3:
import boto
import os
from boto.s3.connection import S3Connection
from boto.s3.key import Key
#############################################################################
#### Create a function to upload to S3
#############################################################################
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)
#############################################################################
#### Create a function to delete all files from S3 path
#############################################################################
def RemoveS3Path(access_key, secret_key, s3_bucket, s3_path):
#Load the keys into memory within the OS
os.environ['AWS_ACCESS_KEY_ID'] = access_key
os.environ['AWS_SECRET_ACCESS_KEY'] = secret_key
# connect to the bucket
conn = S3Connection()
bucket = conn.get_bucket(s3_bucket.lower())
#For every file in the check if its in the s3_path, and if so delete it
for key in bucket.list():
if key.name.startswith(s3_path):
keyfile = str(key.name)
key.delete()
return 1
#############################################################################
#### Create a function to download all files from a S3 Path
#############################################################################
def DownloadS3BucketPath(access_key, secret_key, s3_bucket, s3_path, localpath):
#Load the keys into memory within the OS
os.environ['AWS_ACCESS_KEY_ID'] = access_key
os.environ['AWS_SECRET_ACCESS_KEY'] = secret_key
# connect to the bucket
conn = S3Connection()
bucket = conn.get_bucket(s3_bucket.lower())
#For every file in the bucket check if its in the supplied path, and if so download it locally
for key in bucket.list():
if key.name.startswith(s3_path):
keyfile = str(key.name)
head, tail = ntpath.split(keyfile)
keyfile = tail
if len(keyfile) > 1:
key.get_contents_to_filename(localpath + "\\" + keyfile)
return 1
MCITP SQL 2005, MCSA SQL 2012
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply