Azure Data Factory (ADFv2) Parameter Passing: Date Filtering (blog post 1 of 3)

I just had the pleasure of deleting in my blog backlog, three ADFv1 blog posts in the making.  The arrival of Azure Data Factory v2 (ADFv2) makes me want to stand up and sing Handel's Hallelujah Chorus.  Yes, my fine friend, ADFv2 is a real game player now.  Let us begin!

Assumptions:

1. You have an ADFv2 environment in which to work.  These examples will not work in ADFv1.
2. You are using VSTS GIT for source code control.  You will also need VSTS GIT for easy copying and editing.
3. You are familiar with creating ADF linked services aka data connections.

Summary: This blog post will give you examples of the following ADFv2 functionality

1. Calling a SQL Server stored procedure via ADFv2's new Lookup capability, and retaining the output dataset in memory
2. Passing dynamic date filters to the Salesforce SELECT statement.  (I use Salesforce because most people can figure out everything else, but SOSQL is not very intuitive.)
3. Creating date-specific destination folders in Azure Blob Storage (ABS).
4. Concatenating string values
5. Correctly referencing pipeline parameters and activities in ADFv2 dynamic content

This blog post is one of three in a series.  I will update this post with a link when they become available.

• Azure Data Factory (ADF) v2 Parameter Passing: Table Names (2 of 3): I do not like hard-coding table names This blog post will parameter the table name, remove all hard-coded column definitions, and use a SELECT *.
• ​Azure Data Factory (ADF) v2 Parameter Passing: Putting it All Together (3 of 3): When you combine a Salesforce filter with a parameterized table name, the SELECT * no longer works.  This blob post will show you how to parameterize a list of columns and put together both date filtering and a fully parameterized pipeline.

Setup
To get "this extract start time" we obviously have to incorporate a metadata layer in our solution.  ADFv2 can now Lookup to Cosmos DB as well as several other data sources listed here, but I'm an old fashioned SQL Server lady and I prefer to use an Azure Database for things like this.  It doesn't hurt to keep tables and procs, like what is included in the sample t-sql file at the end of this post, in an Azure Data Warehouse, but honestly, replicated 60 times?  If you can afford it, stand up a simple Azure SQL Database for metadata collection.

Step #1: Create your ADFv2 control objects in your metadata SQL database

1. etl.ADFControl table
2. etl.vwADFControl view
3. etl.usp_ADF_Get_ADFControl_NextExtract stored procedure
4. etl.usp_ADF_Set_ADFControl stored procedure

The CREATE t-sql scripts for these objects can be downloaded below. 

Step #2: Create your three ADFv2 linked services (data connections)

1. SQL Server metadata source
2. Salesforce, or some other data source
3. Azure Blob Storage, or some other data destination

Tip: If you have never created a linked service before in ADF, use the wizard and do not try to copy someone's JSON.  A limited amount of data connection information is (understandably) stored in VSTS GIT, so you will need to manually update your data connections in each environment (DEV, STG, UAT, PRD etc.) anyway.

Step #3: Create your ADFv2 datasets
I am going to show you screen prints here, but the JSON for these datasets is provided at the end of this blog post.  This blog post uses a hard-coded table name.  In blog post 2 of 3, I'll show you how to use a single input and output dataset to extract all tables from a single source.
metadata DB used for all objects
Your lookup and execute SQL tasks will use this same dataset.

input dataset for your source
This is your source dataset.  You actually do not need to define every column, but we'll tackle that later.

output dataset for your destination
This is your destination dataset definition.  You actually do not need to define every column here either, but more on that later.

File Path Part 1
@concat(pipeline().parameters.blobContainerName,' /', formatDateTime(pipeline().parameters.actualRunTime, 'yyyy'), '/', formatDateTime(pipeline().parameters.actualRunTime, 'MM'), '/')
File Path Part 2
@concat('AccountPartner-', formatDateTime(pipeline().parameters.actualRunTime, 'yyyy'), formatDateTime(pipeline().parameters.actualRunTime, 'MM'), formatDateTime(pipeline().parameters.actualRunTime, 'dd'), ' ', formatDateTime(pipeline().parameters.actualRunTime, 'hh'), formatDateTime(pipeline().parameters.actualRunTime, 'mm'), '.txt')

You may be thinking, "This is a lot of stuff to just import one table!" but stay tuned.   I promise you, you will have less development time, cost of ownership, and less downtime by moving data with ADFv2 then you would with SSIS packages especially if you have > 2GB  of data in a source object.  Using parameters is the key to success.

Step #4: Create your ADFv2 pipeline

Lookup Activity: GetLastExtractStart

Click <Preview data> to see the result set from the stored procedure.  This value can be referenced with the following syntax:
​activity('GetLastEtxractStart').output.firstRow.NextExtractStartDateTime

​Stored Procedure Activities: Set Start and End Dates

SetLastExtractStart activity

SetLastExtractEnd activity

​Copy Activity: The actual copy data from source to destination filtered by YourSource.LastModifiedDate

<----Source Query Dynamic Syntax
SELECT Id, SystemModstamp FROM AccountPartner WHERE SystemModstamp >= @{formatDateTime(activity('GetLastEtxractStart').output.firstRow.NextExtractStartDateTime,'yyyy-MM-ddTHH:mm:ssZ')}
​

If you are new to ADFv2, here are a couple of screen prints on how to validate, save, publish and run your pipeline on demand.

Conclusion of the Matter
This blog post is intended for developers just starting out in ADFv2, and especially for ADFv2 developers pulling Salesforce data.  I do not expect you to have needed all the information provided, but I am hoping you'll find a tip or trick that will save you time.  Referencing activities and pipeline parameters is easy to do, for example, but when I first worked on this, I found very few examples.  Finding the right syntax for a Salesforce date filter took me about a week.  

You may be frustrated transitioning from SSIS to ADFv2, but don't give up.  Remember that ADFv2 is not a data transformation tool -- it is a data copy tool.  SSIS <> ADFv2, and in a world where we are moving to MPP architectures and ELT via stored procedures, ADFv2 as an "orchestrator" is worth considering.

Supporting Downloadable Files

json_for_adfv2_parameter_passing_1.zip
File Size:	2 kb
File Type:	zip

Download File

---

adfcontrol_sql_server_metadata_objects.zip
File Size:	1 kb
File Type:	zip

Download File

---