locked
Setting Filename at destination in ForEach Loop RRS feed

  • Question

  • I have a pipline where I have a ForEach Loop Container that pulls data from several DB Servers into a Azure Data Lake Gen 2.

    I have the following breakdown:

    I have a table which I'm using to populate the list of servers I need the ForEach Loop to iterate through.  The Lookup task queries that list of Servers and passes that to ForEach container(Servername, DatabaseName, Schema, TableName).

    In the ForEach Loop container I have a Copy Data Task.

    Source: I'm passing in the items from the Lookup item into the Dataset Properties of the Source.

    @item().Server
    @item().DatabaseName
    @item().Schema
    @item().Tablename

    Sink: I'm passing in the data import into a blob container with the following file path

    main/data import/@CONCAT(item().tablename,'_',formatdatetime(utcnow(),'HHmm'),'.csv')

    What I'm looking for is if it's coming from a Database called 'DB1' I want the file to be prefixed with Billing for example.  If the connection is coming from like let's say 'DB2' it should be prefixed as Financial.  So it would look like the following:

    main/data import/Billing_table1_1345.csv

    main/data import/Financial_table1_1345.csv

    Monday, April 13, 2020 2:16 PM

Answers

  • Hello Diango , 

    Since you are already using a Lookup , you can use the power of SQL :) 

    INSERT  INTO TESTSERVERNAME(SERVER,DATABASENAME,[SCHEMA],TABLENAME) VALUES ('S1','DB1','SCHEMA1','TABLLE1')
    INSERT  INTO TESTSERVERNAME(SERVER,DATABASENAME,[SCHEMA],TABLENAME) VALUES ('S2','DB2','SCHEMA2','TABLLE2')
    
    SELECT SERVER,DATABASENAME,[SCHEMA],TABLENAME,
     CASE DATABASENAME  WHEN 'DB1' THEN  CONCAT('BILLING','_',TABLENAME)
    					WHEN 'DB2' THEN CONCAT('FINANCIAL','_',TABLENAME)
    					ELSE DATABASENAME 
             END 
    		 AS FILENAME
    FROM TESTSERVERNAME

    Now you can use the 

    @CONCAT(item().FILENAME,'_',formatdatetime(utcnow(),'HHmm'),'.csv')

    Hope this helps . 

    If you think if its appropriate , you can mark this as a answer/up vote the reply  , so that other members of the community can benefit from this .


    Thanks Himanshu

    • Proposed as answer by HimanshuSinha-msft Tuesday, April 14, 2020 12:30 AM
    • Marked as answer by Diango Tuesday, April 14, 2020 1:41 PM
    Tuesday, April 14, 2020 12:30 AM

All replies

  • Hello Diango , 

    Since you are already using a Lookup , you can use the power of SQL :) 

    INSERT  INTO TESTSERVERNAME(SERVER,DATABASENAME,[SCHEMA],TABLENAME) VALUES ('S1','DB1','SCHEMA1','TABLLE1')
    INSERT  INTO TESTSERVERNAME(SERVER,DATABASENAME,[SCHEMA],TABLENAME) VALUES ('S2','DB2','SCHEMA2','TABLLE2')
    
    SELECT SERVER,DATABASENAME,[SCHEMA],TABLENAME,
     CASE DATABASENAME  WHEN 'DB1' THEN  CONCAT('BILLING','_',TABLENAME)
    					WHEN 'DB2' THEN CONCAT('FINANCIAL','_',TABLENAME)
    					ELSE DATABASENAME 
             END 
    		 AS FILENAME
    FROM TESTSERVERNAME

    Now you can use the 

    @CONCAT(item().FILENAME,'_',formatdatetime(utcnow(),'HHmm'),'.csv')

    Hope this helps . 

    If you think if its appropriate , you can mark this as a answer/up vote the reply  , so that other members of the community can benefit from this .


    Thanks Himanshu

    • Proposed as answer by HimanshuSinha-msft Tuesday, April 14, 2020 12:30 AM
    • Marked as answer by Diango Tuesday, April 14, 2020 1:41 PM
    Tuesday, April 14, 2020 12:30 AM
  • This actually does help and you have solidified my thought process.  Thank you very much!
    Tuesday, April 14, 2020 1:42 PM