Can a variable set equal to a query with parameters be used in an OLEDB Source

  • dndaughtery

    SSChampion

    Points: 11086

    I have an OLEDB Source that Id like to store the query in a table and set a variable with an EXEC SQL task and use that as the variable holding the query with oledb src set to SQL command from variable. The query will have parameters that Id like to pass in from the OLEDB Src's Parameters. Can this be done?

  • dndaughtery

    SSChampion

    Points: 11086

    Answered my own question. You cant do it that way. Also, you could use an expression task to replace placeholders in a query as long as the max length of the query is less than 4000. If its over 4000 I'm thinking the best bet is to store the query in a table and upon retrieving the query replace the placeholders with the values you need it to be before returning it.

  • Jeff Moden

    SSC Guru

    Points: 995457

    dndaughtery wrote:

    Answered my own question. You cant do it that way. Also, you could use an expression task to replace placeholders in a query as long as the max length of the query is less than 4000. If its over 4000 I'm thinking the best bet is to store the query in a table and upon retrieving the query replace the placeholders with the values you need it to be before returning it.

    First, what makes you think there's some sort of limit at 4,000?

    Also, simple place holder replacement does NOT prevent the possibility of SQL Injection.  You need to sanitize anything being passed for place holder replacement.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88215

    If you need to dynamically create the SQL statement - here is one possibility:

    1. Create a stored procedure on the source/destination system that generates the SQL as needed.  This should not utilize dynamic SQL to query - rather it should use parameters to read from configuration tables to determine how to build the final query.
    2. Use an Execute SQL Task to execute the stored procedure with the appropriate parameters.  Use output parameters to return the constructed SQL statement (and other configuration items if needed) - or you can use a resultset if you need to execute multiple queries that are all constructed with the same column values to be returned.  Note: you could even return many different SQL statements and branch to different data flows based on each individual query.
    3. The output parameter - or one of the columns returned in a resultset will be mapped to a user variable.  That variable will then be utilized as the source in an OLEDB Source.

    The important part of this is that your stored procedure does not need to utilize dynamic SQL.  It uses input parameters to read data from a configuration table and builds the SQL based on the rules you define - and you don't use any type of string manipulation based on the parameters, only based on the values you get from your configuration table.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply