SSIS Amazon SQS Source

Help > Source Adapters > Amazon SQS Source >

SSIS-Amazon-SQS-Source-Adpater
Amazon SQS Source can be used to read messages from Amazon SQS (Simple Queue Storage) using SSIS.

Content

Video Tutorial
Step-By-Step
Component Properties
Error Handling
Settings UI
See also

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.

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
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
From the SSIS toolbox drag and drop Data Flow Task on the controlflow designer surface.
Double click on the DataFlow task to see DataFlow designer surface.
From the SSIS toolbox drag and drop ZS Amazon SQS Source on the dataflow designer surface.
Double click Amazon SQS Source to configure it.
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.
Click OK to save connection information.
Select queue from the dropdown and click preview.
Click OK to save settings.
From the SSIS toolbox drag and drop Trash Destination on the data flow designer surface.
Now single click on the Amazon SQS Source once you see blue arrow from source ... connect it to Trash Destination.
Double click on ZS Trash Destination to Configure it.
Click on OK button to save Trash Destination configure setting UI.
Right click on the path and Add Data Viewer.
Execute the package and verify source data in data viewer.
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

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

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 […]