SSIS Amazon SQS Source
PreviousNext

Amazon SQS Source can be used to read messages 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 read all messages from Amazon SQS Source. For this demo we are assuming that you already have SQS Queue created and it contains some messages.
  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 DataFlow task to see DataFlow designer surface.
  5. From the SSIS toolbox drag and drop ZS Amazon SQS Source on the dataflow designer surface.
    SSIS Amazon SQS Queue Source - Drag and Drop
  6. Double click Amazon SQS Source to configure it.
  7. Click on [New] button for Select Connection Manager. Select ServiceType = SQS from Top Dropdown, Enter AccessKey and SecrentKey and Click Test connection to verify credentials.
    Configure SSIS Amazon SQS Destination Connection
  8. Click OK to save connection information.
  9. Select queue from the dropdown and click preview.
    Configure SSIS Amazon SQS Source Connection
  10. Click OK to save settings.
  11. From the SSIS toolbox drag and drop Trash Destination on the data flow designer surface.
    SSIS Trash Destination - Drag and Drop
  12. Now single click on the Amazon SQS Source once you see blue arrow from source ... connect it to Trash Destination.
  13. Double click on ZS Trash Destination to Configure it.
    SSIS Trash Destination - Configure
  14. Click on OK button to save Trash Destination configure setting UI.
  15. Right click on the path and Add Data Viewer.
  16. Execute the package and verify source data in data viewer.
    Read Amazon SQS Messages using SSIS Amazon SQS Source
  17. After messages read from SQS Queue, same message cannot be read again until VisibilityTimeout. By default Queue visibility timeout is used (-1 means use default) but you can override it on the source UI

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
MaxRows Maximum number of rows to get from queue. Keep fetching data from queue until total message count >=MaxRows or response is empty (no message).
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.
MaxRetryOnNullMessageReceive Maximum retry count for NULL message receive before it stops calling read message from queue.
OutputDateAsLocalTime Output any UTC date as Local time.
FetchAttributes Fetch message attributes.
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.
OutputAllAttributes If this option is set to true then one more column All_Attributes will be generated. It contains JSON for key/value pairs of all attributes.

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

Setting UI

SSIS Amazon SQS Queue Source - Setting UI
SSIS Amazon SQS Queue Source - Setting UI
SSIS Amazon SQS Queue Source - Setting UI
SSIS Amazon SQS Queue Source - Setting UI

See Also

Articles / Tutorials

Click here to see all articles for [SSIS Amazon SQS Source] 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.