Download 3rd Party S3 Files

  • 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!

  • 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

  • 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
        }
    }

  • 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

  • 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.

  • 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)

  • 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

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

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