Global Variables and Stored Procedure Parameters: "Input Parameters
One of the benefits of the SQL Server 2000 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. "
I am now posting to my blog
