Share via

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
2026-04-02T19:15:45.27+00:00

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?

SQL Server Integration Services

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Q&A Assist
    2026-04-02T19:15:56.5666667+00:00
    AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more

    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:

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.