SSIS Amazon SQS Destination
PreviousNext

Amazon SQS Destination can be used to write/delete data from Amazon SQS (Simple Queue Storage) using SSIS.

Download SSIS PowerPack

Content

Video Tutorial

Coming soon...

Step-By-Step

In this section you will learn how to write messages to Amazon SQS Queue. For this demo we are assuming that you already have SQS Queue created.
  1. In order to connect to Amazon SQS from SSIS you will need Access Key and Secret Key (Login to AWS Portal and Create AccessKey/Secret key with SQS Service Read access). If you are not sure about this 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. Once you have 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
  3. From the SSIS toolbox drag and drop Data Flow Task on the controlflow designer surface.
    SSIS Data Flow Task - Drag and Drop
  4. Double click on the Data Flow task to see Data Flow designer surface.
  5. Right Click in Connection Manager Panel and select [New Connection...] menu item. Pick ZA-AWS-STORAGE connection type. On New Connection Dialogbox Select ServiceType=SQS from Top Dropdown, Enter AccessKey, SecrentKey and Region. Click Test connection to verify credentials.
    Create SSIS Amazon SQS Connection - Choose Type
    Create SSIS Amazon SQS Connection - Choose Type
    Configure SSIS Amazon SQS Destination Connection
  6. Click OK to save connection information.
  7. From the SSIS toolbox drag and drop ZS DummyData Source on the data flow designer surface.
    SSIS DummyData Source - Drag and Drop
  8. Double click Dummy Data Source to configure it.
  9. From Template pick JsonDocuments and enter row count=100 to generate 100 json documents.
    Generate fake JSON documents
  10. From the SSIS toolbox drag and drop Amazon SQS Destination on the dataflow designer surface.
    SSIS Amazon SQS Queue Destination - Drag and Drop
  11. Now single click on the Dummy Data Source, once you see blue arrow from source ... connect it to SQS Destination.
  12. Now double click Amazon SQS Destination to configure it.
  13. On [Connection Manager] tab select SQS connection manager.
    Configure SSIS Amazon SQS Destination Adapter - Connection Tab
  14. Click on [Component Properties] tab, Set Operation to Insert and select Target Queue from dropdown list.
    Configure SSIS Amazon SQS Destination Adapter - Component Tab
  15. Click on [Mappings] tab and verify mapping. Map Document column from upstream to MessageText target column.
    Configure SSIS Amazon SQS 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 SQS Service section.
    Execute SSIS Amazon SQS Destination Adapter - Load Messages
    SSIS Load Test - View SQS Data in AWS Console - Record Count Check

How to Delete Message from Queue.

  1. From the SSIS toolbox drag and drop Amazon SQS Destination on the Data flow designer surface and connect it to Amazon SQS Destination.
  2. Now double click on Amazon SQS Destination to configure it.
  3. On [Connection Manager] tab select SQS connection manager.
    Configure SSIS Amazon SQS Destination Adapter - Connection Tab
  4. Click on [Component Properties] tab, Set Operation to Delete and Select Target Queue from dropdown list.
    Configure SSIS Amazon SQS Destination Adapter - Component Tab
  5. Click on [Column Mappings] tab and verify mapping. Map document column from upstream to the MessageText target column.
    Configure SSIS Amazon SQS Destination Adapter - Connection Tab
  6. In the last, click on the OK button to save configure setting UI.
  7. After all, Execute the Package and verify target data by going to your AWS console under SQS service section.

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.
QueueName Queue name for this action
VisibilityTimeout This is message visibility timeout in seconds. Set -1 to use default timeout (default queue level setting). During visibility timeout period message becomes unavailable after you read it so adjust this timeout as per your need. Set timeout to 0 if you want to allow read operation for same message without any wait time. If you set this timout too small then same message might appear back in read operation and it will throw duplicate message error.
AttributeNames Comma separated list of attribute names (case-sensitive) you like to output in a separate column (one for each attribute) (e.g MyProp1=string,MyProp2=int,MyProp3=double). If you do not include datatype then string type will be assumed (Syntax PropName1[=DATATYPE],PropName2[=DATATYPE]...,PropNameN[=DATATYPE]). Supported datatypes are int, bool, long, date and string. In the output Attr_ will be prefixed automatically.
Operation Action you like to perform

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
UpdateVisibility [1] UpdateVisibility
Delete [2] Delete
Reload [3] Reload

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

Settings UI

SSIS Amazon SQS Destination Adapter - Setting UI
SSIS Amazon SQS Destination Adapter - Setting UI

See Also

Articles / Tutorials

Click here to see all articles for [SSIS Amazon SQS Destination] category
How to Read / Write / Delete Amazon SQS Queue data in SSIS

How to Read / Write / Delete Amazon SQS Queue data in SSIS

Introduction ZappySys provides high-performance drag and drop connectors for Amazon AWS Integration. In this post, we will see How to Read / Write / Delete Amazon SQS Queue data in SSIS using ZS Amazon SQS components. ZappySys developed many AWS related components but in this article, we will look at 2 components for Amazon SQS […]



Copyrights reserved. ZappySys LLC.