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

Video Tutorial

Coming soon...

Step-By-Step

In this section you will learn how to use Amazon DynamoDB Destination Adapter to write data to Amazon DynamoDB Table(In this case its from DummyData Source).
  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.
    SSIS Data Flow Task - Drag and Drop
  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 ZS DummyData Source on the data flow designer surface.
    SSIS DummyData Source - Drag and Drop
  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. Now single click on the Dummy Data Source, once you see blue arrow from source ... connect it to DynamoDB Destination.
  12. Now double click Amazon DynamoDB Destination to configure it.
  13. On [Connection Manager] tab select DynamoDB connection manager.
    Configure SSIS Amazon DynamoDB Destination Adapter - Connection Tab
  14. 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
  15. 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
  16. Click OK to save settings.
  17. 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 (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Normal [0] Normal
Medium [1] Medium
Detailed [2] Detailed
Debugging [3] 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 (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Table [0] Table
Query [1] 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 (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Insert [0] Insert
Reload [1] Reload
Update [2] Update
UpdateOrInsert [3] UpdateOrInsert
Delete [4] Delete
Replace [5] 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.

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

Option Description
Auto [0] Auto
Strict [1] Strict - Exact length
Guess2x [2] Guess2x - 2 times bigger
Guess3x [3] Guess3x - 3 times bigger
Guess4x [4] Guess4x - 4 times bigger
TreatAsUnicodeString [5] Set all columns as string
Guess10x [6] Guess10x - 10 times bigger
TreatStringAsMaxLength [7] Set string columns with MAX Length - i.e. DT_WSTR(4000)
TreatStringAsBlob [8] Set string columns as BLOB - i.e. DT_NTEXT
MetaDataCustomLength Length for all string column. This option is only valid for MetaDataScanMode=Custom
MetaDataTreatStringAsAscii When this option is true, it detects all string values as DT_STR (Ascii) rather than DT_WSTR (Unicode)

Output Column Properties

Insert Operation (Bulk)

DynamoDB Destination supports Bulk Insert operation. You can do these two ways.

Update Operation (Row by Row)

Update operation requires use of Template Transform or you can use JSON Generator Transform to Produce Update Command for UpdateItem API call Check this article. Amazon DynamoDB doesn't support Bulk Update like Insert / Delete. So its Row-By-Row operation for now.

Delete Operation

Delete Operation requires you to map Key column by which you like to delete document. Refer this article

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

SSIS Amazon DynamoDB Destination - Setting UI
SSIS Amazon DynamoDB Destination - Setting UI

Amazon AWS DynamoDB Destination FAQs

See Also

Articles / Tutorials

 Click here to see all articles for [SSIS Amazon DynamoDB Destination] category
How to Read / Write Amazon DynamoDB in SSIS

How to Read / Write Amazon DynamoDB in SSIS

Introduction In this article we will look at how to Read / Write Amazon DynamoDB in SSIS. ZappySys developed many AWS related components but in this article we will look at 3 Tasks/Components for DynamoDB Integration Scenarios (Read, Write, Update, Bulk Insert, Create / Drop Table etc.). We will discuss how to use SSIS DynamoDB Source Connector […]

Copyrights reserved. ZappySys LLC.