locked
sql task passing db table name as variable RRS feed

  • Question

  • Hi guys,

    I am using a OLEDB connection with an SQL Task

    My variable to pass to the SQL Task is the table name, but it does not work.

    Passing a value (int) is fine.

    Can someone help me?

    It looks like this:

    Variable User:: DestinationTable;  string; dbo.temptable

    Passing parameters: User::DestinationTable; varchar; 0; 50

    SQL: Select * from ?

    Friday, July 20, 2012 9:58 AM

Answers

  • You cannot use the parameter placeholder (?) in the FROM clause.
    Either create dynamic SQL, be it in SQL itself as Chintak suggested, or in the script task.
    Or create a string variable that will hold the SQL statement with an expression, as Joon84 suggested. The actual expression is:

    "SELECT * FROM " + @[User::myTable] + ";"

    Since you said you have a long statement in reality, the last option is probably not feasable is expressions on variables are limited to 4000 characters only.

    MCTS, MCITP - Please mark posts as answered where appropriate.

    • Marked as answer by Eileen Zhao Thursday, July 26, 2012 9:37 AM
    Friday, July 20, 2012 11:19 AM
  • Hi Chintak,

    then, it looks like the execution is not working. Any ideas?

    The package is failing. :(

    Hi Joon84,

    thanks for the suggestion. I already thought about it, but the tablename for this variable comes out of a script task and the query I need is actually much more complex. the selct * from is just a simple try out.

    I though the SQL makes it easier and better understandable rather than scrolling through a VB code in script task.

    I need to do delets, inserts, and a table update after data comparision. The sql statements work on the SQL Server so I thought I can use the SQL task to implement it into SSIS. Destination and Source need to be dynamic. Therefore I need the variables.

    maybe you can create the whole statement for the SQL in the same Script task. and in the same go, assign it to some variable (if you can achieve this). later you can use as per your need.

    regards

    joon

    • Marked as answer by Eileen Zhao Thursday, July 26, 2012 9:37 AM
    Friday, July 20, 2012 11:36 AM

All replies

  • Hi,

    As you pass tablename at runtime, this creates problem in parsing..You can use dynamic sql instad

    declare @t nvarchar(max)
    declare @tablename nvarchar(128)
    select @tablename = ?
    
    select @t = 'Select * from ' + @tablename
    
    exec sp_executesql @t
    
    


    - Chintak (My Blog)

    Friday, July 20, 2012 10:11 AM
  • OR, you prepare the query to be executed in a variable as you are doing now : User::DestinationTable =SQL: Select * from ?.

    then pass this variable as the SQL statement instead of using "SQL: Select * from ?".

    regards

    joon


    • Edited by Joon84 Friday, July 20, 2012 10:38 AM
    Friday, July 20, 2012 10:38 AM
  • It runs though without the line:  exec sp_executesql @t

    Sorry, I am not so familiar with dynamic SQL but that line actually executes the query, doesn.t it?

    Do you know what the issue is?

    Friday, July 20, 2012 10:56 AM
  • Hi,

    yes,  just this is the line which executes query. The code beforw it prepares the statement to be executed.


    - Chintak (My Blog)

    Friday, July 20, 2012 10:59 AM
  • Hi Chintak,

    then, it looks like the execution is not working. Any ideas?

    The package is failing. :(

    Hi Joon84,

    thanks for the suggestion. I already thought about it, but the tablename for this variable comes out of a script task and the query I need is actually much more complex. the selct * from is just a simple try out.

    I though the SQL makes it easier and better understandable rather than scrolling through a VB code in script task.

    I need to do delets, inserts, and a table update after data comparision. The sql statements work on the SQL Server so I thought I can use the SQL task to implement it into SSIS. Destination and Source need to be dynamic. Therefore I need the variables.

    Friday, July 20, 2012 11:14 AM
  • You cannot use the parameter placeholder (?) in the FROM clause.
    Either create dynamic SQL, be it in SQL itself as Chintak suggested, or in the script task.
    Or create a string variable that will hold the SQL statement with an expression, as Joon84 suggested. The actual expression is:

    "SELECT * FROM " + @[User::myTable] + ";"

    Since you said you have a long statement in reality, the last option is probably not feasable is expressions on variables are limited to 4000 characters only.

    MCTS, MCITP - Please mark posts as answered where appropriate.

    • Marked as answer by Eileen Zhao Thursday, July 26, 2012 9:37 AM
    Friday, July 20, 2012 11:19 AM
  • Hi Chintak,

    then, it looks like the execution is not working. Any ideas?

    The package is failing. :(

    Hi Joon84,

    thanks for the suggestion. I already thought about it, but the tablename for this variable comes out of a script task and the query I need is actually much more complex. the selct * from is just a simple try out.

    I though the SQL makes it easier and better understandable rather than scrolling through a VB code in script task.

    I need to do delets, inserts, and a table update after data comparision. The sql statements work on the SQL Server so I thought I can use the SQL task to implement it into SSIS. Destination and Source need to be dynamic. Therefore I need the variables.

    maybe you can create the whole statement for the SQL in the same Script task. and in the same go, assign it to some variable (if you can achieve this). later you can use as per your need.

    regards

    joon

    • Marked as answer by Eileen Zhao Thursday, July 26, 2012 9:37 AM
    Friday, July 20, 2012 11:36 AM
  • Thanks a lot for your help!

    Cheers E.

    Friday, July 20, 2012 11:48 AM