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

Question

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

dndaughtery

SSChampion

Points: 11290
More actions
January 14, 2020 at 2:23 pm

#3715233

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?

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

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

dndaughtery SSChampion Points: 11290 More actions · Answer 1

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: 1004340 More actions · Answer 2

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.

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 SSC Guru Points: 90303 More actions · Answer 3

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

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.
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.
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.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs