Global variables and Functions
In SQL Server versions prior to 7.0, there was a concept known as global variables.
Global variables stored system-level information such as @@error, which returns the error of the last executed Transact-SQL statement.
These concepts still apply to version 7.0, but they are now referred to as functions, not as global variables.
Input Parameters
One of the benefits of the SQL Server 2012 is that the SQL tasks support mapping of global variables to parameter placeholders within the SQL.
This means you can use the global variable as an input parameter for your stored procedure.
Create a new package, adding a SQL Server connection pointing to the database where you have created the sample stored procedure. Next add an Execute SQL Task, with the code to call your stored
procedure, using a question mark as the placeholder for your parameter:
EXEC dbo.spInputTest ?
Once you have entered the SQL statement you can click Parameters and you will be presented with the Input Parameters tab.
The mapping list view will have one row for each placeholder found in the SQL. There parameters have no meaningful names, they are listed in order, so Parameter 1 will be the first meaningful found, Parameter 2 will be the second placeholder found on so on.
For each parameter found you can select a global variable to map to that parameter. At run time the provider (ODBC or OLE-DB) parameter support will be invoked and the mapped global variable value will be substituted for the placeholder.
It is important to note that the parameter handling uses the provider parameter support since not all providers support parameters, and others provide only limited or unreliable support. Obviously the Microsoft SQL Server providers have full support. Another important point about parameters is that just like when using local variables in SQL they cannot be used to parameterise object names such as procedure names, table names or column names. In a normal SQL statement you can workaround this by using dynamic SQL, but in DTS you have the option of dynamically building the SQL statement
