Azure Data Factory (ADF) v2 Parameters Passing: Table Names (blog post 2 of 3)

In the first of three blog posts on ADFv2 parameter passing, Azure Data Factory (ADFv2) Parameter Passing: Date Filtering (blog post 1 of 3), we pretty much set the ground work.  Now that I hope y'll understand how ADFv2 works, let's get rid of some of the hard-coding and make two datasets and one pipeline work for all tables from a single source.  You know I have to say it ... can SSIS "show me [this] money"?

This solution consists of three ADFv2 objects (yes, only three!!)

1. Pipeline
2. Input dataset
3. Output dataset

... and with these three ADFv2 objects coupled with an Azure Database metadata layer, we will extract every Salesforce table known to mankind.  Are you ready to get started?

Assumption: You have read blog post 1 of 3 on parameter passing with ADFv2 and now understand the basic concept of parameter passing and concatenating values in ADFv2.

Summary: This blog post builds on the first blog and will give you additional examples of how to pass a table name as dynamic content.  It will show you how to use a 1.) single input dataset, 2.) single output dataset and 3.) single pipeline to extract data from all tables in a source.

Setup:
The JSON for all datasets and pipelines is attached below, but if you take a moment to understand the screen prints, it might be easier than reading JSON.  
​1.  Single Input Dataset

2.  Single Output Dataset
File path prefix -->
@pipeline().parameters.blobContainerName
File path suffix -->
@concat(pipeline().parameters.tableName, '-', 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')

Tip: Dynamic content and text values that contain an "@pipline..." syntax are not the same thing.

3.  Single ADFv2 Pipeline
The primary add-in for this blog post is the lookup for a column list and the additional parameter being used for the table name.  The @pipeline().parameters.actualRunTime value is passed by an Azure Logic App not explained here, or you could use the pipeline start or a utcdate.  In blog post 3 of 3 we are going to put in a ForEach loop that will allow us to spin through a list of tables.

Lookup Activity - GetLastExtractStart

Lookup Activity -GetcolumnList

Execute Stored Procedure - SetThisExtractStart

Copy Activity
This is where it all comes together.  You concatenate your looked up column list with your looked up table name, and now you are using a single input dataset for all source tables.

@concat('SELECT ', activity('GetColumnList').output.firstRow.ColumnList, ' FROM ', pipeline().parameters.tableName, ' WHERE SystemModstamp >= ', formatDateTime(activity('GetLastExtractStart').output.firstRow.NextExtractStartDateTime,'yyyy-MM-ddTHH:mm:ssZ'))

---

Execute Stored Procedure - SetThisExtractEnd

The Conclusion of the Matter:
I've taken the time to provide all of these screen prints in loving memory of SSIS.  Once you transition, you can figure most of this out with a bit of persistence, but isn't it nice to have screen prints to show you the way sometimes?  The most challenging part of this is the @concat() of the source copy activity.  Every data source will require this in their own syntax (SOSQL, t-sql etc.), or beware -- in the syntax of the ODBC driver that is sitting behind Microsoft's data connector.

I should have blog post 3 out for you by end of mid-August, so please stay tuned.

Supporting Downloadable Files:

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

Download File

---

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

Download File

---