I'm creating a SSIS package where I have an Expression Task connected to a Data Flow Task in the Control Flow.
The Expression task is:
@[User::DateVariable] = year(DATEADD("day", - @[$Project::DaystoReload] , getdate() ))*10000
The OLE DB Source uses a SQL Command from Variable as the source, and it has the appearence:
"SELECT Country, Population, Region, Date
INNER JOIN [" + @[User::DatabaseName] + "].[dbo].[Country] where Date >= ?"
User::DatabaseName has EvaluateAsExpression set to TRUE, it is a string. DateVariable which is created from the Expression Task Before the Data Flow task is of data type Int32.
When I use the Query in SSMS, and I use the value which Expression Task evaluates DateVariable to "hard-coded" then I get the correct output, but in SSIS I get the error that
The SQL command requires 1 parameter, but the parameter mapping only has 0
I've been trying to Google this issue, but I haven't found a solution for when the OLE DB Source uses SQL Command from Variable, but rather just the SQL command in which you have a option called Parameter which you can map to the parameter. I haven't found such an option using Sql command from variable.
Does anyone have any insight into this?
You cant pass parameters inside OLEDB SOURCE with SQL Command via Variable.
You have to pass a variable instead. And since you already stored a value inside your Date Variable, it should be possible to use
So your SQL Variable should be like this:
"SELECT Country, Population, Region, Date
INNER JOIN [" + @[User::DatabaseName] + "].[dbo].[Country] where date >= "+ (DT_WSTR,20)@[User::Date]