SSIS Amazon SQS Destination

Help > Destination Adapters > Amazon SQS Destination >

SSIS-Amazon-SQS-Destination-Adpater
Amazon SQS Destination can be used to write/delete data 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 write messages to Amazon SQS Queue. For this demo we are assuming that you already have SQS Queue created.

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 Data Flow task to see Data Flow designer surface.
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.
Click OK to save connection information.
From the SSIS toolbox drag and drop ZS DummyData Source on the data flow designer surface.
Double click Dummy Data Source to configure it.
From Template pick JsonDocuments and enter row count=100 to generate 100 json documents.
From the SSIS toolbox drag and drop Amazon SQS Destination on the dataflow designer surface.
Now single click on the Dummy Data Source, once you see blue arrow from source ... connect it to SQS Destination.
Now double click Amazon SQS Destination to configure it.
On [Connection Manager] tab select SQS connection manager.
Click on [Component Properties] tab, Set Operation to Insert and select Target Queue from dropdown list.
Click on [Mappings] tab and verify mapping. Map Document column from upstream to MessageText target column.
Click OK to save settings.
Execute the package and verify target data by going to your AWS Console under SQS Service section.

How to Delete Message from Queue.

From the SSIS toolbox drag and drop Amazon SQS Destination on the Data flow designer surface and connect it to Amazon SQS Destination.
Now double click on Amazon SQS Destination to configure it.
On [Connection Manager] tab select SQS connection manager.
Click on [Component Properties] tab, Set Operation to Delete and Select Target Queue from dropdown list.
Click on [Column Mappings] tab and verify mapping. Map document column from upstream to the MessageText target column.
In the last, click on the OK button to save configure setting UI.
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

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

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