readBehavior:query to source with Lookup


  • Hi Team,

    I have a pipeline that is using some lookups to find dimension keys based on Ids so the Fact table can be populated with the key values from the Dimensions. So the Persn Lookup returns the PersonKey & Person Id with the Hsptl_End returning the Person and a Datew formated as an Int

    The Source Sql I am using for the Source Fact_Enrolment:

             ,per.[ID]                                                           AS PERSON_ID
             , INT ( activity('Lkup_D_PERSN_KEY').OUTPUT.value [0].PersonKey )   AS PersonKey
             , INT ( activity('Lkup_Prs_HSPTL_ED').OUTPUT.value [0].HsptlEDKey ) AS HSPEDKey
             , CAST(CONVERT(VARCHAR(8), per.DoB, 112) AS INT)                    AS BirthDateKey
             ,'1'                                                                AS CountEnrolment
             , CASE
                          WHEN OPE.EndDate IS NOT NULL
                                     THEN '1'
                                     ELSE 0
               END AS CountHistoricalEnrolment
               [dbo].[PERSON] AS per
               INNER JOIN
                          [dbo].[PRGRM_ENROLMENT] AS OPE
                                     OPE.PERSON_ID = per.ID
               OPE.PERSON_ID IN ( 66111 , 68669 , 68670 , 68674 )---limited to these Ids for testing
               AND per.ID = INT ( activity('Lkup_D_PERSN_KEY').OUTPUT.value [0].PersonId )
               AND per.ID = INT ( activity('Lkup_Prs_HSPTL_ED').OUTPUT.value [0].PERSON_ID )

    I added the SQL in as an Dynamic Content so it can read the Lookup output, then change the code to add a property to tell DF to read it as a query, I have seen this in another pipeline but it had SalesForce as the Source. Below is the copy of the code where the readBehavior goes.

    Which should produce this result but isn't and I have no idea why other than I am not using the Lookup values correctly.

    Any pointers will be helpful




    Friday, May 17, 2019 1:30 AM

All replies

  • Hello Binway, and thank you for your inquiry.

    I do have a suggestion.  To help with debugging, and possibly simplify your query, I recommend that you store the output of each of your lookups into a SetVariable.  Once all of those are complete, do the concatenation / query building in another SetVariable activity, and simple reference that in your CopyData activity.

    The benefit of this, is being able to view what happens every step of the process.  This should also force everything to be a string, so you shouldn't have unexpected types.  This suggestion has helped others.

    Please let me know if this helps you any.

    Martin Jaffer, Azure CXP Engineer.

    Monday, May 20, 2019 5:57 PM
  • Thanks for looking at this Martin.   did a bit of a work around so that I could keeping developing the reporting landscape so have this listed as "Tech Debt".  it might take me a couple of days to return to it, I also notice that the lookup in the data factory is a bit different to SSIS as I am wanting to lookup keys in one table and populate them with corresponding values in another which will keep me learning new features.


    Monday, May 20, 2019 10:33 PM