Download 3rd Party S3 Files

Question

Download 3rd Party S3 Files

Michelle-138172

Hall of Fame

Points: 3234
More actions
March 29, 2018 at 10:16 am

#328127

Trying to download from a Vendor's S3 bucket. I'd like to do it through SSIS, but from what I read, Powershell script might be my best bet. So I wrote a PS script, but I keep getting the error:
Get-S3Object : Invalid URI: The hostname could not be parsed.
Here's the line of command:
$objects = Get-S3Object -BucketName $bucket -KeyPrefix $keyPrefix -AccessKey $accessKey -SecretKey $secretKey -Region $region
I got a S3 path from the Vendor like s3://xxx/yyy. I can download through Cyberduck without any issue, so the access keys should be fine. I suspect the problem is with BucketName and KeyPrefix, and possibly Region. I don't know the Region for the bucket, and used "US Standard".
So what exactly should I specify for these params?
If there's a more direct or easier way to do this in SSIS, that'll be even better.
Thanks!

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

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

RTaylor2208 SSChampion Points: 13298 More actions · Answer 1

This is one way to do it:

$aws_profile = 'MyAWSProfile'
$awskey = 'XXXXXXXXXXXXX'
$awssecretkey = 'XXXXXXXXXXXXXX'
$bucket = "MyBucket"
$keyPrefix = "install/"
$localPath = "D:\install\"

Import-Module AWSPowerShell
Set-AWSCredentials -AccessKey $awskey -SecretKey $awssecretkey -StoreAs $aws_profile

Set-AWSCredentials -ProfileName $aws_profile
Set-DefaultAWSRegion -Region us-east-1

$bucket = "MyBucket"
$keyPrefix = "install/"
$localPath = "D:\install\"

$objects = Get-S3Object -BucketName $bucket -KeyPrefix $keyPrefix

foreach($object in $objects) {
    $localFileName = $object.Key -replace $keyPrefix, ''
    if ($localFileName -ne '') {
        $localFilePath = Join-Path $localPath $localFileName
        Copy-S3Object -BucketName $bucket -Key $object.Key -LocalFile $localFilePath -AccessKey $accessKey -SecretKey $secretKey -Region $region
    }
}

MCITP SQL 2005, MCSA SQL 2012

Michelle-138172 Hall of Fame Points: 3234 More actions · Answer 2

Thanks! That's basically the script I used, but I can't figure out what is the right Bucket name to use since it's the 3rd party data.

RTaylor2208 - Tuesday, April 3, 2018 8:32 AM
This is one way to do it:
$aws_profile = 'MyAWSProfile'
$awskey = 'XXXXXXXXXXXXX'
$awssecretkey = 'XXXXXXXXXXXXXX'
$bucket = "MyBucket"
$keyPrefix = "install/"
$localPath = "D:\install\"
Import-Module AWSPowerShell
Set-AWSCredentials -AccessKey $awskey -SecretKey $awssecretkey -StoreAs $aws_profile
Set-AWSCredentials -ProfileName $aws_profile
Set-DefaultAWSRegion -Region us-east-1
$bucket = "MyBucket"
$keyPrefix = "install/"
$localPath = "D:\install\"
$objects = Get-S3Object -BucketName $bucket -KeyPrefix $keyPrefix
foreach($object in $objects) {
    $localFileName = $object.Key -replace $keyPrefix, ''
    if ($localFileName -ne '') {
        $localFilePath = Join-Path $localPath $localFileName
        Copy-S3Object -BucketName $bucket -Key $object.Key -LocalFile $localFilePath -AccessKey $accessKey -SecretKey $secretKey -Region $region
    }
}

RTaylor2208 SSChampion Points: 13298 More actions · Answer 3

Sorry for the slow reply. I assume the URL they gave you is something like this:

https://s3.amazonaws.com/thislocation/install/en_sql_server_2017_developer_x64_dvd_11296168.iso

In that case the bucket name is "thislocation" m the bucket name is the first part after the https://s3.amazonaws.com/ part of the URL.

MCITP SQL 2005, MCSA SQL 2012

Michelle-138172 Hall of Fame Points: 3234 More actions · Answer 4

RTaylor2208 - Friday, April 6, 2018 9:03 AM
Sorry for the slow reply. I assume the URL they gave you is something like this:
https://s3.amazonaws.com/thislocation/install/en_sql_server_2017_developer_x64_dvd_11296168.iso
In that case the bucket name is "thislocation" m the bucket name is the first part after the https://s3.amazonaws.com/ part of the URL.

Great! That worked! Thanks a lot.

I took out the --Region param. So I guess it's optional.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

RTaylor2208 - Tuesday, April 3, 2018 8:32 AM
This is one way to do it:
$aws_profile = 'MyAWSProfile'
$awskey = 'XXXXXXXXXXXXX'
$awssecretkey = 'XXXXXXXXXXXXXX'
$bucket = "MyBucket"
$keyPrefix = "install/"
$localPath = "D:\install\"
Import-Module AWSPowerShell
Set-AWSCredentials -AccessKey $awskey -SecretKey $awssecretkey -StoreAs $aws_profile
Set-AWSCredentials -ProfileName $aws_profile
Set-DefaultAWSRegion -Region us-east-1
$bucket = "MyBucket"
$keyPrefix = "install/"
$localPath = "D:\install\"
$objects = Get-S3Object -BucketName $bucket -KeyPrefix $keyPrefix
foreach($object in $objects) {
    $localFileName = $object.Key -replace $keyPrefix, ''
    if ($localFileName -ne '') {
        $localFilePath = Join-Path $localPath $localFileName
        Copy-S3Object -BucketName $bucket -Key $object.Key -LocalFile $localFilePath -AccessKey $accessKey -SecretKey $secretKey -Region $region
    }
}

Yowch! I'm certainly not a PoSH expert but is there no way around storing login credentials in clear text this?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RTaylor2208 SSChampion Points: 13298 More actions · Answer 6

Jeff Moden - Friday, April 6, 2018 2:05 PM

RTaylor2208 - Tuesday, April 3, 2018 8:32 AM
...

Yowch! I'm certainly not a PoSH expert but is there no way around storing login credentials in clear text this?

Jeff, I agree storing credentials in plain text in a script is not how you would implement this securely the purpose was really to illustrate a full solution. There are a few options on how to do this securely.

One such option is to setup a aws profile first once.
1. Setup a profile once using this code:

$aws_profile = 'MyAWSProfile'
$awskey = 'XXXXXXXXXXXXX'
$awssecretkey = 'XXXXXXXXXXXXXX'
$bucket = "MyBucket"
$keyPrefix = "install/"
$localPath = "D:\install\"

Import-Module AWSPowerShell
Set-AWSCredentials -AccessKey $awskey -SecretKey $awssecretkey -StoreAs $aws_profile

From then on all scripts can use the profile:

$aws_profile = 'MyAWSProfile'
Set-AWSCredentials -ProfileName $aws_profile
Set-DefaultAWSRegion -Region us-east-1

Alternatively the solution we use is to store the AWS Keys in a secure encypted SQL database only accessible by specific accounts via windows authentication.

Each script executed then retrieves the access and secret keys for the specific IAM user, creates a profile and then uses that profile to execute AWS API calls. This is an abbreviated version of the code I use for our automated scripts:

$aws_profile = $Args[0]
$SQLTargetServer = $Args[1]
$EstateServer = $Args[2]
$EstateDB = $Args[3]

#Get the keys, to be used for connecting to the AWS API
$query = "SET NOCOUNT ON

OPEN SYMMETRIC KEY MyKey
DECRYPTION BY CERTIFICATE MyCert;

select CAST(CONVERT(nvarchar(100), DecryptByKey([API_KEY])) AS VARCHAR(100)) AS AKEY,
CAST(CONVERT(nvarchar(100), DecryptByKey([API_SECRET_KEY])) AS VARCHAR(100)) AS SKEY
FROM dbo.aws_env_keys
WHERE EnvName = '" + $aws_profile + "';

CLOSE SYMMETRIC KEY MyKey; "

$SecurityDetails = Invoke-Sqlcmd -ServerInstance $EstateServer -Database $EstateDB -Query $query
$awskey = $SecurityDetails.AKEY
$awssecretkey = $SecurityDetails.SKEY

# Import the AWS Powershell modules
if (-not (Get-Module -Name "AWSPowerShell")) {
Import-Module AWSPowerShell
}

#Create a local profile to store the AWS Connection credentials
Set-AWSCredential -AccessKey $awskey -SecretKey $awssecretkey -StoreAs $aws_profile

Set-AWSCredential -ProfileName $aws_profile
Set-DefaultAWSRegion -Region us-east-1
.............

MCITP SQL 2005, MCSA SQL 2012