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