This is a two-part question. Is there a best practice approach/workaround to dynamically SET the return value from a query into an SSIS Variable using Expressions? I've researched and read the docs and it seems SSIS Variable Expressions store the value of the Expression and not the returned/runtime value.
For instance, I'd like a the retuned value of MAX date stored in the Variable called [User::MaxDate] not the query string.
Variable: [User::MaxDate] Expression: (SELECT MAX(dateCol) AS dt FROM tblDate)
If the above is not possible, has anyone leveraged the Execute SQL Task to set values for multiple variables? The idea here is to avoid using an Execute SQL Task for each dynamic variable initailization.
My requirements are to SET initial values for 10 variables on the main control flow. I am trying to avoid using 10 Execute SQL Tasks to accomplish this.
Without using the Script Task or 3rd Party plugins is the above possible in SSIS (Version 2019)?
Thank you
For instance, I'd like a the retuned value of MAX date stored in the Variable called [User::MaxDate] not the query string.
Variable: [User::MaxDate] Expression: (SELECT MAX(dateCol) AS dt FROM tblDate)
You are correct, you are looking to store values in an SSIS variable. A Variable with the EvaluateAsExpression property set to true means it will by dynamic but the scope of the dynamicism is the SSIS package itself.
In your case, you need to ask an external provider for information so you couldn't build an SSIS expression to satisfy that.
If you can get the 10 values you want to store in SSIS variables in a single query, then yes, you can get by with a single Execute SQL Task.
If the 10 columns are all in a single table, like a control table, then you could have a query like
SELECT
MAX(C.SalesFactDate) AS MaxSalesDate
, MAX(C.EmployeeDimDate) AS MaxEmployeeDate
-- etc
FROM
dbo.Control AS C;
If you need to get dates from the actual tables, that too can be a "single" query
SELECT
(SELECT MAX(C.SalesFactDate) FROM dbo.FactSales AS C) AS MaxSalesDate
, (SELECT MAX(C.EmployeeDimDate) FROM dbo.DimEmployees AS C) AS MaxEmployeeDate
-- etc
Either way, you will have a single row of data with 1 to N columns of data you want to associate with SSIS Variables.
On the Main task Execute SQL Task screen, change the Result Set type to Single row from None.
On the Results tab, you'll associate an SSIS variable per column. If you used OLE DB provider, the column name is the zero based ordinal. ODBC is 1 based ordinal and ADO.NET uses named entities.
Assuming I had created a variety of SSIS variables of type Date/db_date/etc and I used an OLE DB Connection manager, the screen would look like
0|@[User::SalesDate]
1|@[User::EmployeeDate]
etc