SSIS Dynamic Columns



.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;








0















I created variables that are populated from rows from another table. It should loop through each row to generate my next select statement. However, SSIS isn't accepting '+ @[User::ThisField]+' as a variable it expects AS MyColName after. I don't want this because it will return the column name and not the actual value it contains.



This is my code.



SELECT 
ISNULL('+ (DT_I4)@[User::ThisID]+', 0) AS EEDFID,
'+ @[User::ThisField]+',
EeID
FROM
EeExtraDetails


Has anyone had luck with this?










share|improve this question






























    0















    I created variables that are populated from rows from another table. It should loop through each row to generate my next select statement. However, SSIS isn't accepting '+ @[User::ThisField]+' as a variable it expects AS MyColName after. I don't want this because it will return the column name and not the actual value it contains.



    This is my code.



    SELECT 
    ISNULL('+ (DT_I4)@[User::ThisID]+', 0) AS EEDFID,
    '+ @[User::ThisField]+',
    EeID
    FROM
    EeExtraDetails


    Has anyone had luck with this?










    share|improve this question


























      0












      0








      0








      I created variables that are populated from rows from another table. It should loop through each row to generate my next select statement. However, SSIS isn't accepting '+ @[User::ThisField]+' as a variable it expects AS MyColName after. I don't want this because it will return the column name and not the actual value it contains.



      This is my code.



      SELECT 
      ISNULL('+ (DT_I4)@[User::ThisID]+', 0) AS EEDFID,
      '+ @[User::ThisField]+',
      EeID
      FROM
      EeExtraDetails


      Has anyone had luck with this?










      share|improve this question
















      I created variables that are populated from rows from another table. It should loop through each row to generate my next select statement. However, SSIS isn't accepting '+ @[User::ThisField]+' as a variable it expects AS MyColName after. I don't want this because it will return the column name and not the actual value it contains.



      This is my code.



      SELECT 
      ISNULL('+ (DT_I4)@[User::ThisID]+', 0) AS EEDFID,
      '+ @[User::ThisField]+',
      EeID
      FROM
      EeExtraDetails


      Has anyone had luck with this?







      sql-server ssis dynamic-sql






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Nov 15 '18 at 16:41









      marc_s

      586k13011281272




      586k13011281272










      asked Nov 15 '18 at 16:16









      theJtheJ

      14116




      14116






















          1 Answer
          1






          active

          oldest

          votes


















          0














          That's not going to work that way. Assuming all the @[User::ThisField] data types are the same, then your problem is just how to use expressions. If the value of @ThisField is Col1 and it's numeric and then @ThisField becomes Col2 which is text, the data flow will fails as the metadata cannot change at runtime.



          My metadata doesn't change, explain the expressions part



          Variables don't work into queries the way you have it now, but you're on the correct path. Instead, define a new variable @[User::QuerySource], data type of String and specify that the value is an Expression. Your expression then becomes much as you have it now.



          "SELECT ISNULL("
          + (DT_WSTR, 20)@[User::ThisID]
          + ", 0) AS EEDFID, "
          + @[User::ThisField]
          + " AS MyFieldAlias "
          + ", EeID FROM EeExtraDetails "


          The string concatenation within the SSIS expression language only works with strings so I have explicitly cast your @User::ThisID as string. If I misinterpreted that and you were intending to use the SSIS Expression language's ISNULL function, then that's a different syntax. At any rate, click evaluate and you should see whether your query looks approximately correct for the design-time values.



          My metadata does change



          The best you can do in this situation is cast the results of @[User::ThisField] to string within the SQL Statement like



          + "CAST(", @[User::ThisField, " AS nvarchar(4000))"





          share|improve this answer























            Your Answer






            StackExchange.ifUsing("editor", function ()
            StackExchange.using("externalEditor", function ()
            StackExchange.using("snippets", function ()
            StackExchange.snippets.init();
            );
            );
            , "code-snippets");

            StackExchange.ready(function()
            var channelOptions =
            tags: "".split(" "),
            id: "1"
            ;
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function()
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled)
            StackExchange.using("snippets", function()
            createEditor();
            );

            else
            createEditor();

            );

            function createEditor()
            StackExchange.prepareEditor(
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: true,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: 10,
            bindNavPrevention: true,
            postfix: "",
            imageUploader:
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            ,
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            );



            );













            draft saved

            draft discarded


















            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53323644%2fssis-dynamic-columns%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown

























            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            0














            That's not going to work that way. Assuming all the @[User::ThisField] data types are the same, then your problem is just how to use expressions. If the value of @ThisField is Col1 and it's numeric and then @ThisField becomes Col2 which is text, the data flow will fails as the metadata cannot change at runtime.



            My metadata doesn't change, explain the expressions part



            Variables don't work into queries the way you have it now, but you're on the correct path. Instead, define a new variable @[User::QuerySource], data type of String and specify that the value is an Expression. Your expression then becomes much as you have it now.



            "SELECT ISNULL("
            + (DT_WSTR, 20)@[User::ThisID]
            + ", 0) AS EEDFID, "
            + @[User::ThisField]
            + " AS MyFieldAlias "
            + ", EeID FROM EeExtraDetails "


            The string concatenation within the SSIS expression language only works with strings so I have explicitly cast your @User::ThisID as string. If I misinterpreted that and you were intending to use the SSIS Expression language's ISNULL function, then that's a different syntax. At any rate, click evaluate and you should see whether your query looks approximately correct for the design-time values.



            My metadata does change



            The best you can do in this situation is cast the results of @[User::ThisField] to string within the SQL Statement like



            + "CAST(", @[User::ThisField, " AS nvarchar(4000))"





            share|improve this answer



























              0














              That's not going to work that way. Assuming all the @[User::ThisField] data types are the same, then your problem is just how to use expressions. If the value of @ThisField is Col1 and it's numeric and then @ThisField becomes Col2 which is text, the data flow will fails as the metadata cannot change at runtime.



              My metadata doesn't change, explain the expressions part



              Variables don't work into queries the way you have it now, but you're on the correct path. Instead, define a new variable @[User::QuerySource], data type of String and specify that the value is an Expression. Your expression then becomes much as you have it now.



              "SELECT ISNULL("
              + (DT_WSTR, 20)@[User::ThisID]
              + ", 0) AS EEDFID, "
              + @[User::ThisField]
              + " AS MyFieldAlias "
              + ", EeID FROM EeExtraDetails "


              The string concatenation within the SSIS expression language only works with strings so I have explicitly cast your @User::ThisID as string. If I misinterpreted that and you were intending to use the SSIS Expression language's ISNULL function, then that's a different syntax. At any rate, click evaluate and you should see whether your query looks approximately correct for the design-time values.



              My metadata does change



              The best you can do in this situation is cast the results of @[User::ThisField] to string within the SQL Statement like



              + "CAST(", @[User::ThisField, " AS nvarchar(4000))"





              share|improve this answer

























                0












                0








                0







                That's not going to work that way. Assuming all the @[User::ThisField] data types are the same, then your problem is just how to use expressions. If the value of @ThisField is Col1 and it's numeric and then @ThisField becomes Col2 which is text, the data flow will fails as the metadata cannot change at runtime.



                My metadata doesn't change, explain the expressions part



                Variables don't work into queries the way you have it now, but you're on the correct path. Instead, define a new variable @[User::QuerySource], data type of String and specify that the value is an Expression. Your expression then becomes much as you have it now.



                "SELECT ISNULL("
                + (DT_WSTR, 20)@[User::ThisID]
                + ", 0) AS EEDFID, "
                + @[User::ThisField]
                + " AS MyFieldAlias "
                + ", EeID FROM EeExtraDetails "


                The string concatenation within the SSIS expression language only works with strings so I have explicitly cast your @User::ThisID as string. If I misinterpreted that and you were intending to use the SSIS Expression language's ISNULL function, then that's a different syntax. At any rate, click evaluate and you should see whether your query looks approximately correct for the design-time values.



                My metadata does change



                The best you can do in this situation is cast the results of @[User::ThisField] to string within the SQL Statement like



                + "CAST(", @[User::ThisField, " AS nvarchar(4000))"





                share|improve this answer













                That's not going to work that way. Assuming all the @[User::ThisField] data types are the same, then your problem is just how to use expressions. If the value of @ThisField is Col1 and it's numeric and then @ThisField becomes Col2 which is text, the data flow will fails as the metadata cannot change at runtime.



                My metadata doesn't change, explain the expressions part



                Variables don't work into queries the way you have it now, but you're on the correct path. Instead, define a new variable @[User::QuerySource], data type of String and specify that the value is an Expression. Your expression then becomes much as you have it now.



                "SELECT ISNULL("
                + (DT_WSTR, 20)@[User::ThisID]
                + ", 0) AS EEDFID, "
                + @[User::ThisField]
                + " AS MyFieldAlias "
                + ", EeID FROM EeExtraDetails "


                The string concatenation within the SSIS expression language only works with strings so I have explicitly cast your @User::ThisID as string. If I misinterpreted that and you were intending to use the SSIS Expression language's ISNULL function, then that's a different syntax. At any rate, click evaluate and you should see whether your query looks approximately correct for the design-time values.



                My metadata does change



                The best you can do in this situation is cast the results of @[User::ThisField] to string within the SQL Statement like



                + "CAST(", @[User::ThisField, " AS nvarchar(4000))"






                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Nov 15 '18 at 16:30









                billinkcbillinkc

                46.7k982124




                46.7k982124





























                    draft saved

                    draft discarded
















































                    Thanks for contributing an answer to Stack Overflow!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid


                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.

                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function ()
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53323644%2fssis-dynamic-columns%23new-answer', 'question_page');

                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    Popular posts from this blog

                    Use pre created SQLite database for Android project in kotlin

                    Darth Vader #20

                    Ondo