Amazon Storage Connection Manager
PreviousNext

Amazon Storage Connection Manager can be used to connect to AWS Storage Services such as S3, DynamoDB, Redshift Cluster. You can supply your Accesskey and Secret Key (Or use Temporary credentials with Session Key) to connect to various services.

Download SSIS PowerPack

Content

Video Tutorial

Step-By-Step

In this tutorial we will learn how to create Amazon Storage Connection. You can create multiple connection.
  1. In order to connect to Amazon Redshift Storage from SSIS you will need Credentials. You can click here to get credentials.
  2. Now, You need to Download and Install SSIS ZappySys PowerPack.
  3. Once you finished above steps, Open Visual Studio and Create New SSIS Package Project.
  4. Right click in the Connection Manager panel and click on New Connection.
    SSIS Create New Connection
  5. Select ZS-AWS-STORAGE Connection Manager from the Connection Managers list and Click on Add Button.
    AWS-Storage Connection - ADD
  6. In the connection manager UI, Set Storage Services as per your need(S3, DynamoDB, Redshift and SQS) and Credential Type to Key-based access control. In the General Tab enter your credentials.
    Amazon Storage Connection Manager UI
  7. Now, click on Proxy tab to setting it.
    Amazon Storage Connection Manager UI - Proxy
  8. In the Encryption tab, do configure like below image.
    Amazon Storage Connection Encryption Options for S3
  9. Click on Test Connection to check configure correct or not.
  10. Click on OK button to save Amazon Storage Connection Manager configure setting UI.
  11. Once you finished, you will see Amazon Storage Connection in Connection Manager panel.
    Amazon Storage Connection - Success

Amazon Storage Temporary Credentials for AWS (Session Token and temp AccessKey, SecretKey)

  1. In the connection manager UI, set Credential Type to Temporary token credentials. Enter credentials like below image.
    Amazon Storage Temporary Credentials for AWS
  2. Click on Test Connection to check configure correct or not.
  3. Click on OK button to save Amazon Storage Connection Manager configure setting UI.

Properties

Property Name Description
AccessKey
UseDualStackEndPoint
UseAcceleratedEndPoint
MaxUploadSpeedKbps Maximum upload speed in KB/second (0=Unlimited). When you enable this option only single thread will be used for larger filer uploads so only set this option if you really need to slow down uploads for good reason.
DisableProxyAutoDetect Check this option to turn off proxy detection. This may speed up web requests in some cases.
ServiceUrl
UseRegionSpecificEndpoint Set this option to true if you connecting to specific region rather than US Standard. By default all requests made to US Standard region first and then it obtains correct region information for your request but in some rare case AWS server doesn't return correct region and it throws Bad Request error. Setting this option to true can help you to fix such errors. Leave this option to false if you are not sure about your region.
Encrypt If you want to enable encryption for content uploaded or downloaded to and from Amazon S3 then check this option. This is not valid for ServiceType=DynamoDB
EncryptionAlgorithm Algorithm for encryption. Supported algorithms are AES256, RSA1024 and KMS. If S3 connection is used for Redshift data transfer then only AES256 is supported.

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Notset [0] Notset
ASE256 [1] AES 256 Bit
RSA1024 [2] RSA 1024 Bit
KMS [3] KMS (Key Management Service)
EncryptionKeyFile Path of secret encryption key file when EncryptionAlgorithm=RSA1024 or AES256. If EncryptionAlgorithm=KMS then this must be Key ID (Obtained from KMS Console). Key file content is in base64 format for AES and for RSA encryption key file content is XML format. You can open it in notepad and validate if key is in right form.
ServiceType

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
S3 [0] S3
DynamoDB [1] DynamoDB
Redshift [2] Redshift
SQS [3] SQS
Notset [-1] Notset
Region
UseHttp
SecretKey
CredentialType

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
KeyBasedAccess [0] Key-based access control
TemporaryToken [1] Temporary token credentials
InstanceDefault [2] Default credentials (i.e. IAM Role or Anonymous)
SessionToken
ProxyPassword
ProxyHost Proxy Server or IP (e.g. MyProxy01 --or-- 192.168.0.1 --or-- proxy1.mydomain.com)
ProxyUser UserName for proxy authentication
ProxyPort Proxy server port number
ConnectionString
ProgressUpdateInterval Update interval in KB. System will report upload/download progress based on this setting. By default progress is reported every 1000 KB.
Timeout
MaxRetryOnError
RetryInterval
MaxParallelThreads

Setting UI

Amazon Storage Connection - Setting UI

See Also

Articles / Tutorials

Click here to see all articles for [SSIS Amazon Storage Task] category
Download the Latest File from Amazon S3 Storage using SSIS

Download the Latest File from Amazon S3 Storage using SSIS

Introduction In our previous article, we saw SSIS Amazon S3 Storage Task examples. Now let’s look at how to Download the Latest File from Amazon S3 Storage using SSIS. Microsoft SSIS includes the Amazon S3 Storage Task that allows us to upload files to Amazon S3 Storage, download files, creating local and remote directories an more. In […]


SSIS Amazon S3 Storage Task examples (Download, Upload, Delete Files / Folders)

SSIS Amazon S3 Storage Task examples (Download, Upload, Delete Files / Folders)

Introduction In this article, we will show SSIS Amazon S3 Storage task examples. Uploading files using Amazon S3 Storage Task or downloading files are common requirements in SSIS. In order to work with Amazon S3 Storage, Microsoft SSIS includes the Amazon S3 Storage Task that allows to upload files to Amazon S3 Storage, download files, creating […]


Setting Metadata key / value in SSIS Amazon S3 / Azure Storage Task

Setting Metadata key / value in SSIS Amazon S3 / Azure Storage Task

Introduction In this post we will see how to set Metadata key/value in Azure Blob Storage Task and Amazon Storage Task (S3 Task). We will also see how to set them dynamically at runtime (Use SSIS Expression). Set Amazon S3 Task Metadata (Key / Value Pairs) Here is how you can set Metadata in Amazon […]


Copy Amazon S3 Files in SSIS to different AWS Account

Copy Amazon S3 Files in SSIS to different AWS Account

Introduction In our previous blog we saw how to upload data to Amazon S3 now let’s look at how to Copy Amazon Files from one AWS account to another AWS account (Server Side Copy) using SSIS Amazon Storage Task. (Must be version v2.7.9 or higher) If you are doing file copy within same account then there […]


How to delete Amazon S3 File / Folder / Bucket in SSIS

How to delete Amazon S3 File / Folder / Bucket in SSIS

Introduction In our previous posts we looked at how to use SSIS Amazon Storage Task (Amazon S3 Task) to automate some Amazon S3 related tasks in SSIS. In this blog post will continue discovering more use cases. Let’s learn how to delete Amazon S3 File, delete Amazon S3 Folder and delete Amazon S3 Bucket. Deleting S3 Files […]


Using Regular Expressions in SSIS

Using Regular Expressions in SSIS

Introduction In this short article you will learn how to write Regular expressions in SSIS (i.e. Regex) and what tool to use to test them. You will also find helpful resources on how to write more sophisticated expressions and learn more about them. For demo purpose we will use FREE SSIS Regex Parser Task to parse and […]


Import / Export data from Amazon Athena using SSIS

Import / Export data from Amazon Athena using SSIS

Introduction In our previous post we explored unlimited possibilities to call Amazon AWS API using SSIS. Now in this post we will learn how to import / export data from Amazon Athena using SSIS. Amazon Athena is very similar service like Google BigQuery which we documented already.  To read data from Amazon Athena we will use […]


Filter by file size, name, date for Amazon / Azure Storage SSIS Task

Filter by file size, name, date for Amazon / Azure Storage SSIS Task

Introduction Sometimes you may need to make an advanced filtering on file path, file date, size etc in SSIS Amazon S3 Task or SSIS Azure Blob Storage Task . For example, you have a directory “Customers“, which contains client directories. Each of them contains a directory named “Invoices“, which is partitioned by year. E.g. “Customers/Microsoft/Invoices/2015”. So […]


SSIS – Copy Amazon S3 files from AWS to Azure

SSIS – Copy Amazon S3 files from AWS to Azure

Introduction Azure and AWS both are most popular Cloud Platforms. In this blog post we will learn how to copy or move Amazon S3 files to Azure Blob Storage without any coding or scripting  (AWS to Azure File Copy / Migration Scenario). To achieve this objective we will use following Drag and Drop SSIS  Tasks (i.e. […]


Loading data from SQL Server to Amazon S3 in SSIS (Split Files, GZip)

Loading data from SQL Server to Amazon S3 in SSIS (Split Files, GZip)

Introduction In this blog post you will see how easy it is to load large amount of data from SQL Server to Amazon S3 Storage. For demo purpose we will use SQL Server as relational source but you can use same steps for any database engine such as Oracle, MySQL, DB2. In this post we […]



Copyrights reserved. ZappySys LLC.