SSIS Amazon DynamoDB Destination Adapter (Bulk Load,Write,Copy NoSQL Data)
PreviousNext

Amazon DynamoDB Destination can be used to bulk insert large amount of records to Amazon DynamoDB storage from any data source using SSIS. Amazon DynamoDB is a cloud based NoSQL data storage service from Amazon AWS

Download SSIS PowerPack

Content

Step-By-Step

In this section you will learn how to use Amazon DynamoDB Destination Adapter to write data to Amazon DynamoDB Table..
  1. In order to connect to Amazon DynamoDB from SSIS you will need Access Key and Secret Key. Ask your SysAdmin or responsible person to provide that to you. Your keys will look something like this: (this is just example key which may differ in your case)
    AccessKey: AKIAIOSFODNN7EXAMPLE
    SecretKey: wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY
                
    Click here to read more how to get your AccessKey and Secret Key

  2. For this sample exercise we will need new DynamoDB table (with CustomerID column as HASH key, see below screenshots). To create new dynamoDB table perform following steps
    • Click on create table under DynamoDB console
    • On Primary Key screen give table name (e.g. Customer), Pick Key Type=HASH, enter keyname=CustomerID and click next
      Create Amazon DynamoDB Table for SSIS Testing - Primary Key HASH or RANGE
    • On index screen no need to enter anything for this exercise so just click next
    • On Read/Write throughput just enter 1 in both fields and click next
      Create Amazon DynamoDB Table for SSIS Testing - Read WRITE Throughput
    • Review and finalize table creation by clicking Create. It may take few minutes to provision DynamoDB table (Check for AVAILABLE status)
      Create Amazon DynamoDB Table for SSIS Testing - Verify Status
  3. Once you have created DynamoDB table, got Account Key (its like UserID) and Secret Key (its like password) you may proceed to create new SSIS package. In BIDS/SSDT create new SSIS package
  4. From the SSIS toolbox drag and drop Data Flow Task on the controlflow designer surface.
  5. Double click on the DataFlow task to see DataFlow designer surface.
  6. Right Click in Connection Manager Panel and select [New Connection...] menu item. Pick ZA-AWS-STORAGE connection type. On New Connection Dialogbox Select ServiceType from Top Dropdown, Enter AccessKey and SecrentKey and Click Test connection to verify credentials.
    Create SSIS Amazon DynamoDB Connection - Choose Type
    Configure SSIS Amazon DynamoDB Destination Connection
  7. Click OK to save connection information.
  8. From the SSIS toolbox drag and drop Dummy Data Source on the dataflow designer surface.
  9. Double click Dummy Data Source to configure it.
  10. From Template pick Customer and enter row count=50 (For sample dataset has less than 100 unique values so lets pick small number).
    Configure SSIS Dummy Data Generator Source
  11. From the SSIS toolbox drag and drop Amazon DynamoDB Destination on the dataflow designer surface.
  12. Now single click on the Dummy Data Source, once you see blue arrow from source ... connect it to DynamoDB Destination.
  13. Now double click Amazon DynamoDB Destination to configure it.
  14. On [Connection Manager] tab select DynamoDB connection manager.
    Configure SSIS Amazon DynamoDB Destination Adapter - Connection Tab
  15. Click on [Component Properties] tab and select AccessMode=Table and Pick DynamoDB table from Tables dropdown list.
    Configure SSIS Amazon DynamoDB Destination Adapter - Component Tab
  16. Click on [Mappings] tab and verify mapping. If dynamoDB table is new table and its empty then all input columns will be automatically mapped. If DynamoDB table is existing table then you can manually pick mapping columns by dragging it from source list to target list.
    Configure SSIS Amazon DynamoDB Destination Adapter - Mapping Tab
  17. Click OK to save settings.
  18. Execute the package and verify target data by going to your AWS Console under DynamoDB section.
    Execute SSIS Amazon DynamoDB Destination Adapter - Load Data
    SSIS Load Test - View DynamoDB Data in AWS Console - Record Count Check

Component Properties

Property Name Description
LoggingMode LoggingMode determines how much information is logged during Package Execution. Set Logging mode to Debugging for maximum log.

Available Options

Option Description
Normal Normal
Medium Medium
Detailed Detailed
Debugging Debugging
PrefixTimestamp When you enable this property it will prefix timestamp before Log messages.
SwitchToRowByRowOnBatchFail When this option is true then each batch is processed in bulk mode in first attempt and when it fails that particular batch is processed in row by row mode to detect bad record. By default when error occurs all records from that batch is rejected but when this option is true you can process all good records and only reject records causing error.
TableName Table name from where you want to read data
BatchSize Indicates how many records are process in a single request. Setting this value to 0 will extract all records in single request.
AccessMode Indicates Access Mode of data (e.g. Table or Sql Query)

Available Options

Option Description
Table Table
Query Query
SqlCommand Specifies SQL query to access data. This setting is ignored when access mode is set to Table.
Operation Specifies operation for destination data load (e.g. Insert, Reload etc). Default operation is Insert (data always appended to target).

Available Options

Option Description
Insert Insert
Reload Reload
Update Update
UpdateOrInsert UpdateOrInsert
Delete Delete
Replace Replace
ParallelThreads
RequestDelay Delay time in milliseconds. This is time to wait before sending next request. This is useful to throttle requests/second. Sometimes server allows x requests/seconds and if you send more requests it may throw error. This setting will help you to slow down.
MetaDataScanMode Metadata scan mode controls how data type and length is determined. By default few records scanned to determine datatype/length. Changing ScanMode affects length/datatype accuracy.
MetaDataCustomLength Length for all string column. This option is only valid for MetaDataScanMode=Custom

Output Column Properties

Error handling

  • This component supports error output.
  • Any bad value will cause Error or Truncation (e.g. If your sample value is 5 character long but output column length is set to 3 chars). If you have error output attached with RedirectRow option on Error, Truncation then you can capture bad rows along with exact reason of error.
  • Use error handling tab to set option for error handling. By default component fails on any data error or truncation issue

Remarks

Settings UI

Amazon AWS DynamoDB Destination FAQs

References

See Also


Copyrights reserved. ZappySys LLC.