locked
Dynamically - Build Table using a SELECT QUERY - Pass Result to Array Variable - SELECT INTO <NEW TABLE> From Array Variable RRS feed

  • Question

  • Hi All,

    I am trying to do something weird I guess which ADF does not allowing or I guess I am not getting gist of it.  As per requirement:

    1) From SQL DW - Write a SELECT statement on a table and fetch required columns (This i am doing as part of Lookup - Query)

    2) Pass on Lookup activity values to a Array Variable (using SET- VARIABLE)

    3) Another Lookup Activity where Try to perform SELECT * INTO <NEW TABLE> FROM <ARRAY VARIABLE>

    I am not able to write a dynamic query as @{variables('var_Source_Target_Mapping_Result')} needs a variable name and in dynamic query its not recognizing it. 

    Is there any other way to perform this thing, I tried COPY DATA but problem is table name is dynamic

    Query For 1st SELECT Statement

    SELECT 
    WorkDBName,
    SourceDBName,
    SourceTableName,
    TargetDBName,
    KeymapDBName,
    KeymapTableName,
    ChangeKey,
    ChangeDetectionColumn,
    BusinessWorkViewName
    FROM 
    ctlfwk.SDS_IDM_Mapping
    WHERE
    Target_System_Entity_Mapping_ID IN 
    (
    SELECT 
    Target_System_Entity_Mapping_ID 
    FROM 
    ctlfwk.vw_Target_Entity_Mapping 
    WHERE 
    Target_System_Code ='@{pipeline().parameters.p_ChildPkg_Target_System_Code}'
    AND 
    Target_Entity_Name = '@{pipeline().parameters.p_ChildPkg_Target_Entity_Name}'
    )

    Query For SELECT INTO <NEW TABLE> FROM <ARRAY VARIABLE>

    IF NOT EXISTS (
    SELECT  TABLE_NAME
    FROM    information_schema.TABLES
    WHERE   
    TABLE_NAME = 'Flow'
    AND
    TABLE_SCHEMA = '@{pipeline().parameters.p_ChildPkg_Target_System_Code}' ) 
    BEGIN
    DECLARE @FlowTabQuery NVARCHAR (MAX) ='
    SELECT 
    WorkDBName,
    SourceDBName,
    SourceTableName,
    TargetDBName,
    KeymapDBName,
    KeymapTableName,
    ChangeKey,
    ChangeDetectionColumn,
    BusinessWorkViewName
    INTO '@{pipeline().parameters.p_ChildPkg_Target_System_Code}' .'+'Flow 
    FROM  '+
    '@{variables('var_Source_Target_Mapping_Result')}'+'
    )'

    EXEC sp_executesql @FlowTabQuery
    SELECT ' TABLE GOT CREATED '
    END
    ELSE
    BEGIN
    Declare @DropQuery NVARCHAR (1000) = 'DROP TABLE '+ '@{pipeline().parameters.p_ChildPkg_Target_System_Code}'+'.Flow'
    EXEC sp_executesql @DropQuery

    DECLARE @FlowTabQuery2 NVARCHAR (MAX) ='
    SELECT 
    WorkDBName,
    SourceDBName,
    SourceTableName,
    TargetDBName,
    KeymapDBName,
    KeymapTableName,
    ChangeKey,
    ChangeDetectionColumn,
    BusinessWorkViewName
    INTO '+
    '@{pipeline().parameters.p_ChildPkg_Target_System_Code}'+'.'+'Flow 
    FROM  '+
    '@{variables(''var_Source_Target_Mapping_Result'')}'+'
    )''


    EXEC sp_executesql @FlowTabQuery2
    SELECT ' TABLE GOT DROP AND RE-CREATED'
    END

    Regards

    GS


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++


    • Edited by GURSETHI Thursday, February 27, 2020 6:09 AM ok
    Thursday, February 27, 2020 6:08 AM

Answers

  • Hello Gursethi and thank you for your question.  There are several options available I would like to make you aware of.

    In a copy activity it is possible to have the table name set dynamically;  Go to the dataset, and add a parameter.  Then under 'Connection' tab you can reference this parameter.  In the copy activity, you will be required to pass a value. (Pictures at bottom)

    In SQL the variable names start with @.  In pipeline expression, expressions start with @.  So to pass a SQL variable name, we need to escape the @.  To pass '@var' without the Factory trying to evaluate it, use '@@var'.  Try this in a set variable activity.

    • Marked as answer by GURSETHI Friday, February 28, 2020 7:30 AM
    Friday, February 28, 2020 2:37 AM
  • Hi Martin, Thanks for your reply. Yes I already did used copy activity (was trying to avoid it at first place). Thanks for your reply. Regards GS

    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

    • Marked as answer by GURSETHI Friday, February 28, 2020 7:30 AM
    Friday, February 28, 2020 7:30 AM

All replies

  • Hello Gursethi and thank you for your question.  There are several options available I would like to make you aware of.

    In a copy activity it is possible to have the table name set dynamically;  Go to the dataset, and add a parameter.  Then under 'Connection' tab you can reference this parameter.  In the copy activity, you will be required to pass a value. (Pictures at bottom)

    In SQL the variable names start with @.  In pipeline expression, expressions start with @.  So to pass a SQL variable name, we need to escape the @.  To pass '@var' without the Factory trying to evaluate it, use '@@var'.  Try this in a set variable activity.

    • Marked as answer by GURSETHI Friday, February 28, 2020 7:30 AM
    Friday, February 28, 2020 2:37 AM
  • Hi Martin, Thanks for your reply. Yes I already did used copy activity (was trying to avoid it at first place). Thanks for your reply. Regards GS

    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

    • Marked as answer by GURSETHI Friday, February 28, 2020 7:30 AM
    Friday, February 28, 2020 7:30 AM