Pass parameter to SSIS

SQL Server Integration Services

Steve Jensen


I have a SSIS package that imports (appends) data to a SQL table from an ODBC source. This package is run from within a VS project.

I'd like to pass a parameter to this package to limit the records it imports. I have seen some discussion of this but I need a detailed step-by-step or a good working example. Specifically:

1. How to get the parameter to the package to begin with. The VB function that runs the SSIS only passes parameters to the stored procedure that executes the SSIS.

2. How to refer to the parameter value in the SQL statement of the data reader object in the SSIS package. Instead of saying 'Select * from table1' it needs to say 'Select * from table1 WHERE field1 = @passedparam'

Top

Re: Pass parameter to SSIS

Rafael Salas


I would add a SSIS variable to the package to hold the parameter you want to pass, let's say @PassedParam.

I would assign the parameter value via dtexec command line; using the SET option.

I would put the SQL Statement into another SSIS variable that is based in a expression (EvaluateAsExpression property in the variable equal to TRUE). That expression would use look at the @PassedParam variable.

Let me know if this make sense...






Top

Re: Pass parameter to SSIS

Steve Jensen

Thank you. This makes conceptual sense, but I am unfamiliar with dtexec. Does the app have to shell to the OS to run this Do you have an example



Top

Re: Pass parameter to SSIS

Steve Jensen

Sorry, I answered my own question. There is a good overview of dtexec with examples at http://msdn2.microsoft.com/en-us/library/ms162810.aspx .


Top