I have an SSIS Project (Visual studio 2019 - Project Deployment Model) which contains 100 SSIS Packages.
The name structure of each package is TableName_Table.dtsx (For example: If my table name is Employees then the package name will be: Employees_Table.dtsx).
Every SSIS package has 2 variables
@[User::TableName]
@[User::TableColumn]
which have hardcoded values,
and a variable that uses above variables values to create a query (Expression Evaluation)
@[User::Logging_sql_statement] =
"Insert into dbo.Log_Table (table_name,delta_column_name,start_time)
values ('" + @[User::TableName] + "','"+ @[User::TableColumn] + "','"+ (DT_WSTR, 50) (DT_DBTIMESTAMP) @[System::StartTime] + "')"
My problem is the following:
Recently we made some changes in the SSIS logging mechanism that we used, so I had to go to every SSIS package of 100 SSIS packages
and modify the Expression at @[User::Logging_sql_statement] Variable.
So in order to avoid having to modify 100 SSIS in the future if new change occurs
I wondered if there was a way I could save the expression body to a project parameter
and then evaluate its value as an expression to @[User::Logging_sql_statement] variable in every SSIS Package.
In the above scenario, I would only have to modify the value of the parameter in the future.
So i created a Project Parameter and set its value hardcoded like this:
@[$Project::Parameter] =
"Insert into dbo.Log_Table (table_name,delta_column_name,start_time)
values ('" + @[User::TableName] + "','"+ @[User::TableColumn] + "','"+ (DT_WSTR, 50) (DT_DBTIMESTAMP) @[System::StartTime] + "')"
and then i went to SSIS Packages and did the following at the expression builder:
@[User::Logging_sql_statement] = @[$Project::Parameter]
but instead of getting the expression evaluation of @[$Project::Parameter] value, i only get its value.
For example:
in SSIS Package with name Employees_Table.dtsx and variables (with hardcoded values)
@[User::TableName] = Employees
@[User::TableColumn] = EmployeeName
I want the following
@[User::Logging_sql_statement] = Expression Evaluation(@[$Project::Parameter])
to give the following value as a result:
Insert into dbo.Log_Table (table_name,delta_column_name,start_time)
values ('Employees','EmployeeName','yyyy-mm-dd hh:mm:ss')
instead of the above, I get
Insert into dbo.Log_Table (table_name,delta_column_name,start_time)
values ('" + @[User::TableName] + "','"+ @[User::TableColumn] + "','"+ (DT_WSTR, 50) (DT_DBTIMESTAMP) @[System::StartTime] + "')
is there a way to achieve this?
