How does the question mark function in the SQL query?

Question

I'm attempting to edit an ETL package(SSIS) that queries a SQL table and outputs csv files for every StationID and I'm having trouble understanding how the question mark is being used in the query definition below. I understand ? is used a parameter but I don't understand how it's used in the date function below:

SELECT TimeSeriesIdentifier, StationID, ParameterID FROM dbo.EtlView WHERE
LastModified > DATEADD(hour, ?*-1, GETDATE()) 
AND StationID LIKE
CASE WHEN ? = 0 THEN
StationID
ELSE
?
END

Answer 1

The parameterization available in SSIS is dependent upon the connection manager used.

OLE DB and ODBC based connection managers use ? as the variable place holder, whereas ADO.NET uses a named parameter, @myVariable.

OLE DB begins counting at 0 whereas ODBC used a 1 based counting system. They are both however ordinal based systems so in your CASE expression the two ? are for the same variable. But, you'll have to list that SSIS Variable twice in the parameter mapping dialog because it's ordinal based - i.e. (param, name) => @HoursBack, 0; @MyVar, 1; and @MyVar, 2;

A "dumb trick" I would employ if I had to deal with repeated ordinal based parameters or if I was troubleshooting packages is to make the supplied query use local variables in the query itself.

DECLARE
    @HoursBack int = ?
,   @MyVariable int = ?;

SELECT
    TimeSeriesIdentifier
,   StationID
,   ParameterID
FROM
    dbo.EtlView
WHERE
    LastModified > DATEADD(HOUR, @HoursBack * -1, GETDATE())
    AND StationID LIKE 
        CASE
            WHEN @MyVariable = 0 THEN StationID
            ELSE @MyVariable
        END;

Now I only have to map the SSIS Variable @MyVar once into my script as the "normal" TSQL parameterization takes over. The other benefit is that I can copy and paste that into a query tool and sub in the ?s with actual values to inspect the results directly from the source. This can be helpful if you're running into situations where the strong typing in SSIS prevents you from getting the results into a data viewer.

Answer 2

SSIS is building a parameterized query.

You can get more information about this here (MySQL-specific): What is the question mark's significance in MySQL at "WHERE column = ?"?

Or you can get a more generally-applicable response here: What does a question mark represent in SQL queries?