Map the end of month date to target table column


  • Hello,

     We are using Azure Data Factory to help us archive our data at the last day of the month. The source data is coming from various Salesforce objects and the targets are various SQL Server tables that corresponds to each of the Salesforce objects we want archiving.

    Each one of the SQL Server tables has a field called "DataArchivedDate" which we want to populate with the last day of the month date (i.e. when archiving our January data we want that field to be populated with 1/31/2019). 

    Currently I can't find any option that enables me to do that. In the pipeline mapping section i'm only seeing the fields coming from Salesforce. Is there a way for me to map the date of the last day of the month to the "DataArchivedDate" column in the target table?



    Saturday, January 12, 2019 6:18 PM

All replies

  • What you can do is you can add a constraint in the SQL Server table as GETDATE() for DataArchivedDate column.

    The column will be filled by the date in which the record is inserted, i.e. the Archival date.



    • Proposed as answer by Wang Zhang Monday, January 14, 2019 9:52 AM
    Sunday, January 13, 2019 6:33 AM
  • Thank you Pratik. We did try that option but our main problem right now is that the pipeline mapping forces us to map one of the source fields to "DataArchivedDate". If I try to exclude "DataArchivedDate" from the mapping I keep getting an error.

    So my question is this, how can a setup the pipeline mapping so that nothing gets mapped to "DataArchivedDate" and at the same time have the GETDATE() function populating this field?



    Tuesday, January 15, 2019 2:09 AM
  • Is the column NULL or NOT NULL type? I was having the same mapping issue earlier and what I did is I converted the column from NOT NULL to NULL and added a DEFAULT constraint. This helped me to resolve the issue.

    Maybe you can try the same.



    Tuesday, January 15, 2019 3:13 AM
  • Thank you Pratik. I'll give it a try.
    Wednesday, January 16, 2019 9:08 PM
  • Hi

    set rowcount 12 
    SELECT    MONTH(DataArchivedDate) as dateMonth,

    YEAR(DataArchivedDate) as dateYear,

    DAY(DataArchivedDate) as dateDay

    FROM         Salesforce  Order By  DataArchivedDate  desc



    Wednesday, January 16, 2019 10:02 PM