Changing a Date automatically every month in copy activity in Azure Data Factory to copy data from SAP HANA to Azure SQLDB

Question

I am working in azure data factory (ADF) to copy data from SAP HANA to Azure SQLDB. I am using "copy activity" in ADF to perform this operation using SQL query to query the table in HANA and sink it into SQLDB. The query contains reporting_date which I need to manually update every month to fetch the data.

I want to remove this redundant procedure of changing dates manually and then run it every month because i have 350+ pipelines to change dates every month and this task becomes very hectic and time consuming. Is there any way to change the date at one place, it works as an input to the source query and query gets automatically updated with the date? Please find attached screenshots for my requirement.

ADF screenshot

Query to be updated:

SELECT "SAPINSTANCE", "MCO", "COUNTRY", "BUKRS", "EntityName", "XBLNR", "HKONT", "TXT50", "GSBER", "BELNR", "BLART", "LTEXT", "CC_BLDAT", "CC_BUDAT", "WAERS", "MONAT", "SGTXT", "GJAHR", "BKTXT", "UMSKZ", "S_LTEXT", "AUFNR", "PROJK", "PRCTR", "EBELN", "KOART", "AUGBL", "CC_AUGDT", "REBZG", "LIFNR", "NAME1", "CC_SHKZG", "KOSTL", "BSCHL", "BUZEI", "EBELP", "KTOKS", "ZUONR", "XINTB", "XLOEB", "XSPEB", "XOPVW", "XKRES", "TYPE_WISE_CLASS", "DESCRIPTION", "MCLASS", "CC_NUM_OF_DAYS_AGEING", "CC_AGEING_BUCKET", "CC_GROUP_CURRENCY", "CC_REPORTING_DATE_VAR", "MARKET_CLUSTER", "HWAER", "CURR_UNIT_TO_EURO_1", sum("CC_WRBTR") AS "CC_WRBTR", sum("CC_GROUP_CURRENCY_VALUE") AS "CC_GROUP_CURRENCY_VALUE", sum("CC_LOC_AMT_IN_MIN") AS "CC_LOC_AMT_IN_MIN", sum("CC_AMT_IN_EUR_MIN") AS "CC_AMT_IN_EUR_MIN", sum("CC_DMBTR_E") AS "CC_DMBTR_E" 
FROM "_SYS_BIC"."table"('PLACEHOLDER' = ('$$IP_CLUSTER$$', 'Africa'), 'PLACEHOLDER' = ('$$IP_COUNTRY$$', '*'), 'PLACEHOLDER' = ('$$IP_FISCAL_YEAR$$', '*'), 'PLACEHOLDER' = '$$IP_REPORTING_DATE$$', '20200831'), 'PLACEHOLDER' = ('$$IP_AGEING_BUCKET$$', '''*'''), 'PLACEHOLDER' = ('$$IP_BUKRS$$', '*')) 
GROUP BY "SAPINSTANCE", "MCO", "COUNTRY", "BUKRS", "EntityName", "XBLNR", "HKONT", "TXT50", "GSBER", "BELNR", "BLART", "LTEXT", "CC_BLDAT", "CC_BUDAT", "WAERS", "MONAT", "SGTXT", "GJAHR", "BKTXT", "UMSKZ", "S_LTEXT", "AUFNR", "PROJK", "PRCTR", "EBELN", "KOART", "AUGBL", "CC_AUGDT", "REBZG", "LIFNR", "NAME1", "CC_SHKZG", "KOSTL", "BSCHL", "BUZEI", "EBELP", "KTOKS", "ZUONR", "XINTB", "XLOEB", "XSPEB", "XOPVW", "XKRES", "TYPE_WISE_CLASS", "DESCRIPTION", "MCLASS", "CC_NUM_OF_DAYS_AGEING", "CC_AGEING_BUCKET", "CC_GROUP_CURRENCY", "CC_REPORTING_DATE_VAR", "MARKET_CLUSTER", "HWAER", "CURR_UNIT_TO_EURO_1"

The above query acts as an input in the copy activity and needs to be updated with date every month manually. Please help!

Have you considered to set "reporting_date" as a Global parameter? Then you can refrence the value of the Global parameter to the query in every pipeline? — Joseph Xu

Cninroh Cninroh · Accepted Answer · 2020-08-25T09:33:29

I presume that you want to use "current_date" or similar dynamic value when triggering Pipeline execution using a scheduled event (for instance weekly, or monthly).

You can achieve that by dynamically concatinating your Query with current date, or perform more advanced date function operations if necessary:

@concat('SELECT FROM schema.table t WHERE reporting_date =', formatDateTime(utcnow(), 'yyyy-MM-dd'))

You can find more detailed list of whats possible in the following url: https://docs.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions#date-functions

Changing a Date automatically every month in copy activity in Azure Data Factory to copy data from SAP HANA to Azure SQLDB

1 Answers