Hi All, I am having a sql select query stored in a variable in SSIS. The columns are not fixed in this query i.e. the query may be like: select a, b, c from TableA or select a from TableA. Now I want to use this query as a source and do certain transformation on the values and store it another table. But I am not able to do that because the mapping keeps changing at runtime. What approch I should take. I was thinking of using script task but couldnt find any such sample on net. Since I am new to SSIS, any help would be grateful. Thanks.

Dynamic metadata support is not there in SSIS. You may have to write custom code for thisNitesh Rai- Please mark the post as answered if it answers your question

Thanks Nitesh!! Could you suggest some article or blog where I can find the sample code or any other help docs.

Hi All, I am having a sql select query stored in a variable in SSIS. The columns are not fixed in this query i.e. the query may be like: select a, b, c from TableA or select a from TableA. Now I want to use this query as a source and do certain transformation on the values and store it another table. But I am not able to do that because the mapping keeps changing at runtime. What approch I should take. I was thinking of using script task but couldnt find any such sample on net. Since I am new to SSIS, any help would be grateful. Thanks. I can think of a solution, provided that the maximum number of columns included in the data pulling query is limited: You can add a new variable to your package to feed in the number of columns that are included in the query (I suppose you are using some external code to set the value for the variable that holds the SQL Query, right?). Let's name it ColumnCount. At run time, based on the query, you can set the value for this variable as well. For each scenario (number of columns in the query) add a new DFT or any other type of Control Flow Item you like to your package (I guess you are going to do some sort of data transfer using your package, which has Lookup transformation(s) in it). Then, edit the Expression property of each Control Flow Item, so that the value of "Disable" property is evaluated based on the value fed in for ColumnCount variable. You can set it by right-clicking on the Control Flow Task and selecting Property, and then clicking on ... button beside Expression in Properties pane. For example, if your query's column counts is variable from 1 to 3, you'll have 3 DFTs in your packahe (namely DFT1, DFT2, and DFT3) and the Expression value of each one of them will be: DFT1 -> @[User::ColumnCount] != 1 DFT2 -> @[User::ColumnCount] != 2 DFT3 -> @[User::ColumnCount] != 3 Let me know if you need more help in implementing this solution. Cheers!Please mark as answer if this helps. Thank you. http://thebipalace.wordpress.com

Thanks Nitesh!! Could you suggest some article or blog where I can find the sample code or any other help docs. If 3rd party tools are an option, you could use the CozyRoc dataflow component. Their dataflow supports dynamic metadata. However, if the number of possible queries is limited, you should go with the solution of SaeedB.