Roadmap › SSIS — SQL Server Integration Services › Module: Package Basics
Lessons in this course (13)
3. Variables, parameters and expressions
Basic★ Must knowLesson 3 of 13
| Variables | Parameters (2012+) | |
|---|---|---|
| Scope | Package, container or task | Package or project |
| Change at runtime | Yes, by tasks/scripts | Read-only during execution; set before execution |
| Use | Store values between tasks (row counts, file names, result sets) | Pass external values in (server names, folders, dates) |
| Set from | Execute SQL results, Script Task, expressions | SSISDB environments, dtexec /Par, Agent job |
Expressions (SSIS expression language, C-like) make properties dynamic:
@[User::FilePath] + "\\Sales_" + (DT_WSTR, 8) (YEAR(GETDATE()) * 10000 + MONTH(GETDATE()) * 100 + DAY(GETDATE())) + ".csv"
"SELECT * FROM dbo.Orders WHERE ModifiedAt > '" + (DT_WSTR, 30) @[User::LastLoad] + "'"
@[$Project::ServerName] -- project parameter
ISNULL(Discount) ? 0 : Discount -- in Derived ColumnSystem variables: System::PackageName, System::StartTime, System::ExecutionInstanceGUID, System::ErrorDescription (in event handlers).