|
SSIS Azure Table Storage Source Adapter (Bulk Extract,Read,Copy NoSQL Data)
|
 
|
|
Azure Table Storage Source can be used to extract large amount of data from Azure Table Storage. This component supports SQL like query language to query Azure Table Storage data without worrying about complex API. Azure Table Storage is a cloud based NoSQL data storage service from Microsoft Azure
|
Account Name: mystorageaccount Access Key: Eby8vdM02xNOcqFlqUwJPLlmEtlCDXJ1OUzFT50uSRZ6IFsuFq2UVErCz4I6tq/K1SZFPTOtr/KBHBeksoGMGw==
SELECT * FROM Customers
WHERE Country = '{{User::varCountry}}'
| Property Name | Description |
|---|---|
| AccessMode | Indicates Access Mode of data (e.g. Table or Sql Query). |
| TableName | Table name from where you want to read data. |
| SqlCommand | Specifies SQL query to access data. This setting is ignored when access mode is set to Table. |
| BatchSize | Indicates how many records are process in a single request. Setting this value to 0 will extract all records in single request. |
SELECT TOP 2 * FROM Customer WHERE CustomerID='ALFKI' OR CustomerID='BOLID'
<Select Statement> ::= SELECT [{TOP Clause}] {Column List} {From Clause} [{Where Clause}]
<TOP Clause> ::= TOP IntegerLiteral
<Column List> ::= * | IDENTIFIER [ [,IDENTIFIER] [,IDENTIFIER] ... ]
<From Clause> ::= FROM IDENTIFIER
<Where Clause> ::= WHERE <Expression List>
! =============================================================================
! Expressions
! =============================================================================
! Mixing AND, OR not allowed. Just one type of Logical Operator can be used across multiple conditions in your filter
<Expression List> ::=
| <Expression> [[AND <Expression>] [AND <Expression>] ...]
| <Expression> [[OR <Expression>] [OR <Expression>] ...]
<Expression> ::= IDENTIFIER = <Value>
<Expression> ::= IDENTIFIER != <Value>
<Expression> ::= IDENTIFIER <> <Value>
<Expression> ::= IDENTIFIER > <Value>
<Expression> ::= IDENTIFIER >= <Value>
<Expression> ::= IDENTIFIER < <Value>
<Expression> ::= IDENTIFIER <= <Value>
<Value> ::= IntegerLiteral | StringLiteral | DateTimeLiteral | GuidLiteral
select * from mytable where Timestamp > datetime'2015-01-01'
select * from mytable where Timestamp > datetime'2015-01-01 23:59:59.000'
select * from mytable where Timestamp > datetime'{{User::varMyDate,yyyy-MM-ddZ}}'
select * from mytable where Timestamp > datetime'{{User::varMyDate,yyyy-MM-ddTHH:mm:ss.fffZ}}'
select * from mytable where Timestamp > datetime'2015-01-01Z'
select * from mytable where Timestamp > datetime'2015-01-01T00:23:59.000Z'
select * from mytable where ProductId = guid'123223DB-A2CD-48BB-99ED-E3614A5EA97E'
select * from mytable where MyLongId > 9223372036854775787L
select * from mytable where IsEnabled = true
-- Selecting all columns
select * from CustomerTest
-- Selecting multiple columns
select CustomerID,CompanyName from CustomerTest
-- Selecting multiple columns, where some attribute name has space in it
select CustomerID,[Contact Title] from CustomerTest
-- Using Where clause with simple condition, select all columns
select * from CustomerTest Where CustomerID='ALC3R'
-- Limiting records returned from query using TOP clause (Similar as LIMIT found in some RDBMS query engine)
select top 7 * from CustomerTest
-- Using OR
select * from CustomerTest Where CustomerID = 'ALFKI' OR CustomerID = 'BOLID'
-- Using AND
select
* from CustomerTest Where CustomerID = 'ALFKI' AND Age >
-- Using comparison operators
select * from CustomerTest Where CustomerID <> 'ALFKI'
select * from CustomerTest Where CustomerID != 'ALFKI'
select * from CustomerTest Where Age > 5
select * from CustomerTest Where Age >= 5
select * from CustomerTest Where Age < 5
select * from CustomerTest Where Age = 5 AND CustomerID = 'C5'
-- Filter on datetime attribute
select * from CustomerTest Where Timestamp > datetime'2015-01-01'
-- Filter on datetime attribute (Using UTC time). Date or Datetime ending with Z is treated as UTC
select * from CustomerTest Where Timestamp > datetime'2015-01-01T00:00:00.000Z'
-- Filter on guid attribute
select * from CustomerTest Where Timestamp > guid'123223DB-A2CD-48BB-99ED-E3614A5EA97E'
-- Filter on boolean attribute
select * from CustomerTest Where IsFlagged = true
|
How to Read/Write Azure Table Storage data in SSISIntroduction In our previous blog, we saw how toÃÂ read/write Azure Blob Storage Files in SSIS (CSV, JSON, XML Format files).ÃÂ Now in this blog, we will see how to Read/Write Azure Table Storage data in SSIS.ÃÂ To illustrate, we will useÃÂ ZappySys SSIS PowerPack, which includes several tasks to import/export data from multiples sources to multiple destinations like […] |
