SSIS Azure Table Storage Source Adapter (Bulk Extract,Read,Copy NoSQL Data)
PreviousNext

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

Download SSIS PowerPack

Content

Step-By-Step

In this section you will learn how to use Azure Table Source Adapter to extract data..
  1. In order to connect to Azure Storage Service from SSIS you will need Storage Account Name and Access Key. Ask your SysAdmin or responsible person to provide that information to you. Here is sample Account Name and Access Key(this is just example key which may differ in your case)
    Account Name: mystorageaccount
    Access Key: Eby8vdM02xNOcqFlqUwJPLlmEtlCDXJ1OUzFT50uSRZ6IFsuFq2UVErCz4I6tq/K1SZFPTOtr/KBHBeksoGMGw==

    Microsoft also provides offline simulator to test Azure Storage functionality. This is great way to try out Azure Storage functionality without worrying about Cost but we strongly recommend you to test everything with Azure Cloud Account before going to Production.
    Click here to read more how to get your Storage Account Name and Access Key


    If you don't have Azure Storage account then its easy to get Trial Azure Storage account or use your MSDN to get credit each month which will be more than enough for real testing. If you prefer to avoid all this hassle then Microsoft provides another great way to test Azure Storage functionality in totally offline mode on your local machine. You can just download Azure Storage Emulator and start testing. Click here to learn more how to configure Azure Storage Emulator for Testing and Development purpose.

  2. Once you have Account Name (its like UserID) and Access 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.
  4. Double click on the DataFlow task to see DataFlow designer surface.
  5. From the SSIS toolbox drag and drop Azure Table Storage Source on the dataflow designer surface.
  6. Double click Azure Table Storage Source to configure it.
  7. Click on [New] button for Select Connection Manager. Enter AccountName server name, userid, password, database and Click Test connection to verify credentials.
    Configure SSIS Azure Table Storage Source Connection
  8. Click OK to save connection information.
  9. Select table from the Tables dropdown or change AccessMode to Query and type SQL Statement and click preview. You can use SQL Style query language.
    Configure SSIS Azure Table Storage Source Connection

    ---- OR ----


    Configure SSIS Azure Table Storage Source Connection
  10. Click OK to save settings.
  11. From the SSIS toolbox drag and drop Trash Destination on the dataflow designer surface.
  12. Now single click on the Azure Table Storage Source once you see blue arrow from source ... connect it to Trash Destination.
  13. Right click on the path and Add Data Viewer.
  14. Execute the package and verify source data in data viewer.
    Configure SSIS Azure Table Storage Source Connection

Component Properties

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.

Output Column Properties

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

Remarks

Azure Table Storage API doesn't support SQL Queries like traditional RDBMS so we have implemented light weight parser which takes your SQL Queries and turn into Native Azure Table Storage query. Here is simple example
SELECT TOP 2 * FROM Customer WHERE CustomerID='ALFKI' OR CustomerID='BOLID'

Azure Table Storage SQL Query Language

If you work with traditional RDBMS and you recently come to NoSQL world you will quickly realize that most of NoSQL database including Azure Table Storage is missing familiar SQL query language. But no worry we got you covered. We have implemented custom query parser which will do hard work for you so you don't have to spend time learning new query language. Our query engine will convert SQL query to Native
query language.

SQL Grammar for Azure Table Storage:

<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
            


Supported Operators:

Query Date/time, Boolean and GUID:

Azure Table Storage Query Examples:

-- 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 > 3

 

-- 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

Settings UIettings UI

SSIS Azure Table Storage Source Adapter Settings SSIS Azure Table Storage Source Adapter Select Columns SSIS Azure Table Storage Source Adapter General Settings

Azure Table Storage Source FAQs

References

See Also


Copyrights reserved. ZappySys LLC.