locked
Mapping fields in a Foreach activity RRS feed

  • Question

  • Hi

    I am new to Azure Data Factory and I did a search beforehand to see if anyone else have experiened my problem.

    I am loading in 3 files from blob storage into tables in SQL DW

    One of the files contain two dates and I want to be able to map those dates to YYYYMMDD but I'm not sure how I can do it.

    I've done it when importing just one file by using a Data Flow and using Derived column.

    It's almost like I need to be able to change the mapping depending on which file I am reading at the time.

    Any help will be appreciated

    Sandra
    Thursday, May 7, 2020 2:57 PM

Answers

  • Thank you for the clarification.

    There is a way to specify the date formats in the Mapping section of the Copy activity.

    When the source data type is DateTime or DateTimeOffset, there is a double chevron you can click to input a format.

    SQL already has a predefined DateTime format, so there is no need to specify a format to write it.  There is only a a need to specify how to read the format from the source.

    Wednesday, May 13, 2020 8:41 PM
  • Hi Martin
    Apologies for not getting back
    I have solved this problem
    I am using a Get Metadata still to retrieve several files in the blob directory
    Within the ForEach activity I'm using a  Switch Activity with a case for each file
    Each Case has a CopyData where I am using the mapping to map each file content accordingly
    I am using your suggestion above with the double chevron setting my dates to en-GB.

    Thanks you 
    Friday, May 29, 2020 7:24 AM

All replies

  • Hello Sandra and thank you for your question.

    I think you hit the nail on the head.

    It's almost like I need to be able to change the mapping depending on which file I am reading at the time.

    There are multiple ways to approach the issue.  I can't say which is appropriate for you without knowing more about your requirements.

    How is your current setup working?  It sounds like your execute data flow is inside a loop, and to select the souce file you are using a parameter to set the wildcard filepath.

    Is the sink schema the same for each?

     

    There are two simple, direct ways to solve the obvious issue.

    1. In one data flow, define all three sources.  This is appropriate if you are going to be loading all at the same time anyway.
    2. Define each in its own flow.

    A more complex approach could use some extra logic in the derived column.

    If source data has form that looks like date , then convert to date, otherwise leave intact.

    Friday, May 8, 2020 9:16 PM
  • Hi Martin

    thanks for getting back to me, your answer makes sense although I'm not sure how to do it yet. 
    I should have explained a bit more of how I am doing this

    I have a pipeline which has a Get Metadata which retrieves all the files and then I have a ForEach activity that has one activity which is a Copy Data

    The source is this

    the Sink is this

    The mapping is the same.  In the source I am adding a new field which is being set to today's date.  This is working fine.  but I have dates in the input files that all need mapping from a string to datetime format.

    In previous ones I have set this date format in the dericced column of a data flow.  but I'm not using a dataflow just a copy data. 

    As I am new to this I'm not sure if I'm trying to do something that is not possible in this way.

    Thanks for your help

    I had to remove all the screen shots because it wouldn't let me submit them

    Sandra

    Tuesday, May 12, 2020 8:14 AM
  • Thank you for the clarification.

    There is a way to specify the date formats in the Mapping section of the Copy activity.

    When the source data type is DateTime or DateTimeOffset, there is a double chevron you can click to input a format.

    SQL already has a predefined DateTime format, so there is no need to specify a format to write it.  There is only a a need to specify how to read the format from the source.

    Wednesday, May 13, 2020 8:41 PM
  • Hi, Sandra, I never heard back.  Did my suggestion help, or are you still facing the issue?
    Thursday, May 28, 2020 10:19 PM
  • Hi Martin
    Apologies for not getting back
    I have solved this problem
    I am using a Get Metadata still to retrieve several files in the blob directory
    Within the ForEach activity I'm using a  Switch Activity with a case for each file
    Each Case has a CopyData where I am using the mapping to map each file content accordingly
    I am using your suggestion above with the double chevron setting my dates to en-GB.

    Thanks you 
    Friday, May 29, 2020 7:24 AM