none
SSIS: sql statements in data source using variables

    Question

  • Hi All,

    I have a oledb source and I want to use variables instead of hardcoding the SQL statement. My query is something like

    Select * from Student where admission_year = '2001' (please ignore the datatype and the syntax)

    I want to store the entire SQL in one variable and the 'year' in another variable. So that, if I just change the value for the year in the table the data should be pulled accordingly.

    Please suggest how I can concatenate the values from both variables and execute as one SQL statement in my Oledb source task.

    Thanks

    Tuesday, July 26, 2011 7:47 AM

Answers

  • Hi All,

    I have a oledb source and I want to use variables instead of hardcoding the SQL statement. My query is something like

    Select * from Student where admission_year = '2001' (please ignore the datatype and the syntax)

    I want to store the entire SQL in one variable and the 'year' in another variable. So that, if I just change the value for the year in the table the data should be pulled accordingly.

    Please suggest how I can concatenate the values from both variables and execute as one SQL statement in my Oledb source task.

    Thanks


    see the example in Jamie's blog

    http://consultingblogs.emc.com/jamiethomson/archive/2005/12/09/2480.aspx

     

    hope this helps!!!


    Shailesh

    Please mark the post as answered if it answers your question.

    Tuesday, July 26, 2011 8:53 AM

All replies

  • better way would be write the SQL in Stored Procedure. You can call stored procedure from SSIS package and pass the variables as parameters in the Stored Procedure. In SP you can concatenate your parameters along with SQL statement. Hope it will help.
    Tuesday, July 26, 2011 8:18 AM
  • You can use parameters in the query  Take a look at

    http://msdn.microsoft.com/en-us/library/ms139904.aspx


    --------------------------------------------------------

    Surender Singh Bhadauria

     


    Tuesday, July 26, 2011 8:22 AM
  • Hi,

    You have to create your SQL statement before the execution of OLEDB source . You can declare two variables, one for your SQL statement and the other for "Year". You can concatenate the Year to your SQL statement in different ways (Script task, Foreach loop expressions..). Then use this variable (SQL) as source in OLDB Source. Let me know if i am not clear or you need more details.


    MSBI Developer
    Tuesday, July 26, 2011 8:27 AM
  • Hi All,

    I have a oledb source and I want to use variables instead of hardcoding the SQL statement. My query is something like

    Select * from Student where admission_year = '2001' (please ignore the datatype and the syntax)

    I want to store the entire SQL in one variable and the 'year' in another variable. So that, if I just change the value for the year in the table the data should be pulled accordingly.

    Please suggest how I can concatenate the values from both variables and execute as one SQL statement in my Oledb source task.

    Thanks


    see the example in Jamie's blog

    http://consultingblogs.emc.com/jamiethomson/archive/2005/12/09/2480.aspx

     

    hope this helps!!!


    Shailesh

    Please mark the post as answered if it answers your question.

    Tuesday, July 26, 2011 8:53 AM
  • better way would be write the SQL in Stored Procedure. You can call stored procedure from SSIS package and pass the variables as parameters in the Stored Procedure. In SP you can concatenate your parameters along with SQL statement. Hope it will help.

    Sorry, I'm not sure I agree with that. Using a stored procedure means you have an extra object to deploy - why not just encapsulate everything in the package?

    The other replies below show how you can achieve what you want to achieve using expressions.

     


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Tuesday, July 26, 2011 9:24 AM
    Moderator