How to use constant var in SSIS

Question

I have defined a variable as:

   DateSecondsOffset Int default as 1

in a SQL Server Integration Service project. I use it as constant.

In a task, I have the following SQL to set another var NextDT:

  SELECT dateadd(s, max(timestamp), 1) DT from myTable

I would like to replace the 1 with DateSecondOffset so that I can change it when needed. Can I just type in the var name there or prefix it with @ or something else?

Answer 1

If you're in a Script..

Dts.Variables("DateSecondOffset").Value

If you're referring to it in a field..

Scope::DateSecondOffset

Replace Scope with your scope which can be found in the "package explorer" tab under variables.

If you're referring to it in a SQL...

SELECT @DateSecondOffset

Be sure to setup your input/output parameters in a SQL Task and ReadOnly/ReadWrite Variables on a script task.

Answer 2

You have to pass package variable as a parameter for SQL task.

You can define do it
- in "Execute SQL Task Editor"->"Parameter Mapping" section
- or in ReadOnlyVariables options (ReadWriteVariables options if you want to be able to change variable value during the task) in the "Script Task Editor" window

Then declare variable inside sql query and init it with first parameter:

DECLARE @DateSecondsOffset INT
SET @DateSecondsOffset = ?
SELECT dateadd(s, max(timestamp), @DateSecondsOffset ) DT from myTable

See Patrick Index: Passing Variables to and from an SSIS task

Answer 3

The syntax for referencing a parameter in an Execute SQL task depends on the type of connection you're using.

For example ADO.NET connections you use @ParameterName. For OleDB connections you use ?.

Full details can be found here

http://msdn.microsoft.com/en-us/library/cc280502.aspx