none
How to do parameter mapping when DFT-Source uses "SQL Command from variable" and the variable expects a parameter?

    Question

  • Hi,

    I have a SSIS package where DFT source uses "SQL Command from variable" and the variable uses an expression like below -

    SELECT * FROM TableA WHERE column1 = ?

    And this set up was running fine on Source. But now, I want to add one more condition to this query and pass parameter to that. Here, I'm unable to find the parameter mapping for this variable / DFT source.

    On analyzing, what I found is, first, if you use DFT Source as "SQL Command" and do parameter mapping and then change this to "SQL Command from variable", and the variable expects a parameter, then the DFT will still work. But, I cannot see / edit the parameter mapping for this variable / source.

    Is this actual behavior of SSIS - DFT Source? Or is there any way to see this parameter mapping in this scenario?

    Thanks

    Wednesday, July 24, 2013 11:56 PM

Answers

All replies

  • If you are using sqlcommand from variable, you dont need to use parameters button in source properties, instead you can dynamically set the parameters with variable in the expression of SourceQuery variable.

    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Prajesh Thursday, July 25, 2013 9:28 AM
    Thursday, July 25, 2013 12:05 AM
  • Hi,

    As Prajesh Said, You can create a SSIS string variable and set its "Evaluate as Expression" Property to "True" and build your source query dynamically there.

    Another option would be create a SSIS string variable and form the SQL Query in a script task, and choose the variable in Source component(SQL Command from variable)

    Here is an example

    http://www.select-sql.com/mssql/how-to-use-a-variable-inside-sql-in-ssis-data-flow-tasks.html


    Rajkumar

    Thursday, July 25, 2013 3:09 AM
  • Thanks for your reply.

    I knew it can be done in different ways. But this particular scenario is surprising me as I'm not able to find the mapping that was done before. I want to know if this is known scenario and is there any way to find out using properties / window or is it a SSIS bug? :)

    Pls note that I came to now about this invisible mapping in .dtsx file (opened in notepad).

    Thanks

    Thursday, July 25, 2013 5:47 AM
  • Pls note that I came to now about this invisible mapping in .dtsx file (opened in notepad).

    Which version of SSIS are you using? And most importantly which version of Visual Studio - i.e., BIDS or SQL Server Data Tools are you using?


    Kind regards M

    Thursday, July 25, 2013 7:41 AM
  • By the way, this is definitely the case with SSIS2012 and Visual Studio 2012.

    I agree, it is odd and worth reporting this through Connect. In my opinion, it should not be the case.


    Kind regards M

    Thursday, July 25, 2013 7:48 AM
  • I'm using SQL Server data tools with visual studio 2010.

    Thanks

    Thursday, July 25, 2013 5:26 PM
  • Hi Soujanya,

    The best is that you should define variable of type string, vQuery. Goto properties of this variable and hit evaluate as expression to true. Then in the ellipses button enter your query (with any number of conditions) as

    "
    SELECT * FROM TABLE
    WHERE COLA>'"+[User::vDate]+"' AND COLB<='"+[User::vDate1]+"'
    "

    Here, the you will pass the values of two varibales to this query dynmaically at run time. Then in ole db source, when  your data access mode, is SQL command from variable, you will select vQuery variable...

    Thanks, hsbal

    Thursday, July 25, 2013 6:19 PM
  • As I said earlier, I knew other ways to handle this. But, first of all, how to see the existing mapping is the problem. I think, as "M Vega" said, it seems to be an issue in SSIS 2012.
    Friday, July 26, 2013 10:40 PM