In my Data Flow OLE DB Source, can I write a SQL Query that is dependent on a SSIS Variable

Bobby P 271 Reputation points

For instance...

If [User::InitialorMonthly] == 'Initial'

SELECT (Everything)

ELSE

SELECT Based on [ModifyDate] being from the 15th of last month to now...[User::InitialorMonthly] == 'Monthly'

I could CASE it out in my T-SQL but if I've already derived it.......why can't I use the SSIS Variable to determine which T-SQL to run?

  1. Lakshmi Narayana Garikapati 1,330 Reputation points • Microsoft External Staff • Moderator

    Hi Bobby P,

    Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we'll assist further

    Thanks,

    Lakshmi

  2. Lakshmi Narayana Garikapati 1,330 Reputation points • Microsoft External Staff • Moderator

    Hi Bobby P,

    Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we'll assist further

    Thanks,

    Lakshmi

Sign in to comment

Answer recommended by moderator

Viorel 127K Reputation points

Maybe such a query will work as well:

select *
from ...
where [User::InitialorMonthly] == 'Initial'
or ( [User::InitialorMonthly] == 'Monthly' and conditions based on [ModifyDate] )
0 comments No comments
Sign in to comment

1 additional answer

  1. AI answer

    Yes. Use the SSIS variable to supply either:

    1. The entire SQL command, or
    2. The parameter values used by a single SQL command.

    Because the OLE DB Source cannot branch logic itself, the choice of which query to run must be made outside the component and then passed in.

    Two common patterns:

    1. SQL command from variable
      • Create a string variable, for example User::SqlToRun.
      • In a Script Task or Expression on the variable, set its value based on User::InitialorMonthly, e.g.:
        • If InitialorMonthly == "Initial" → "SELECT ..."
        • Else → "SELECT ... WHERE ModifyDate BETWEEN ..."
      • In the OLE DB Source Editor, set Data access mode to SQL command from variable and select User::SqlToRun.
      • The OLE DB Source will execute whatever SQL text is in that variable.
      This is explicitly supported: the OLE DB source can use SQL command from variable, and the variable must be a string in scope of the Data Flow task.
    2. Parameterized SQL + variables
      • Use a single parameterized query in the OLE DB Source, e.g. with ? placeholders.
      • Click Parameters and map each ? to SSIS variables (date range, flags, etc.).
      • Use SSIS expressions or other control-flow logic to set those variables based on User::InitialorMonthly.
      • At run time, the same query runs, but with different parameter values.

    If the logic truly requires different SQL shapes (different WHERE clauses, joins, etc.), pattern 1 (SQL command from variable) is the usual approach in a Data Flow.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.
Sign in to answer

Your answer