Azure Data Factory – Updates: March 2015

Posted on by Reza Rad

Previously in another post I’ve mentioned what Azure Data Factory is and a sample scenario of data transfer with it.

Microsoft recently published a new version of it, which has really interesting features. In a nutshell features are:

  • New Version of Data Management Gateway
  • On Premises File System Linked Services
  • On Premises Oracle Linked Services
  • Encoding options for text files on Azure Blog storage
  • Invoking Stored Procedures with additional parameters.

Have a read in details about these new features below;

 

 

New Version of Data Management Gateway

DMG

Data Management Gateway has a new released version which is version 1.4.5549.1.  Data Management Gateway is a tool that enable connection from cloud to on-premises SQL Server instance. You can read more about how to install DMG from Azure Data Factory control panel here.

 

You can download the new version of Data Management Gateway here:

http://www.microsoft.com/en-us/download/details.aspx?id=39717

 

New Linked Service: On-Premises File System

Previously Azure Data Factory was only capable of connecting to three linked services or data sources: Azure Blob Storage, Azure SQL Server Database, On-Premises SQL Server Database (with the help of Data Management Gateway). Now Azure Data Factory also supports On-Premises File System. this new linked services helps you to build data transformation scenarios when you want to transfer files from on-premises to Azure or reverse.

filesystem

With this new linked service you can copy any type of files (xml, text, csv…). for using this linked service you need to install and configure Data Management Gateway version 1.4.5492.1 or higher.

You can use this linked service either with username and password in the JSON script of Linked Service, or encrypted version of it. Here are examples of it from MSDN link below:

with username and password:

{
    "Name": "MyFilesystemLinkedService",
    "Properties":
    {
        "type": "OnPremisesFileSystemLinkedService",
        "host": "\\\\Contosogame-Asia", 
        "userid": "Admin", 
        "password": "123456", 
        "gatewayName": "mygateway"
    }
}

Encrypted method:

{
    "Name": "MyFilesystemLinkedService",
    "Properties":
    {
        "type": "OnPremisesFileSystemLinkedService",
        "host": "localhost", 
        "encryptedcredential": " TWFuIGlzIGRpc3Rpbmd1aXNoZWQsIG5vdCBvbmx5IGJ5IGhpcyByZWFzb24sIGJ1dCBieSB0aGlz",
        "gatewayName": "mygateway"
    }
}

 

 

Read more about On-Premises File System Linked Service here:

https://msdn.microsoft.com/library/dn930836.aspx

 

New Linked Service: On-Premises Oracle Database

This is really exciting news, there are many databases on Oracle database servers, and usually you have to deal with that as part of your data transfer solutions. Fortunately Azure Data Factory now support Oracle database linked services, which means you can transfer data from Oracle database on-premises to Azure.

oracle

 

Here is an example JSON script for this new linked service:

{
    "name": "LinkedServiceOracle",
    "properties": {
        "type": "OnPremisesOracleLinkedService",
        "ConnectionString": "data source=<data source>;User Id=<User Id>;Password=<Password>;",
        "gatewayName": "<gateway name>"
    }
}

 

Read more about On-Premises Oracle Linked Service here:

https://msdn.microsoft.com/library/dn948537.aspx

 

Encoding Name for Text Files on Azure Storage

There is a new EncodingName property for text files on Azure that you can specify the encoding of the file (such as utf-8….). This property is part of Format property of the Location.

encoding

 

Invoking Stored Procedures with Additional Parameters

This is the last, but not the least great new feature. It is now possible to invoke stored procedures with parameters when you transfer data from SQL Server on-premises or SQL Azure. Here is an example usage of this in SqlSink section of the Dataset JSON script;

"sink":
{
    "type": "SqlSink",
    "SqlWriterTableType": "MarketingType",
    "SqlWriterStoredProcedureName": "spOverwriteMarketing", 
    "storedProcedureParameters":
            {
                "stringData": 
                {
                    "value": "str1"     
                }
            }
}

See an example of this feature in this post:

http://azure.microsoft.com/en-us/documentation/articles/data-factory-copy-activity-advanced/#invoke-stored-procedure-for-sql-sink

 

This  link has examples of new features added to ADF:

http://azure.microsoft.com/blog/2015/03/30/azure-data-factory-update-new-data-stores/

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

Leave a Reply