I have a stored procedure, the stored proc will run for the previous day and the output from the stored proc will be getting into a flat file. I created a SSIS package for the flat file output generation. And then scheduled this SSIS package as a SQL job to run daily. Now I want to add date parameter to the stored procedure and I should be able to run the SQL job on a adhoc basis for the date passed in to the stored proc. Then the output from the stored proc should be going into the flat file. Just wondering how we can do this. Thanks.sqldev

You can Use SSIS variable , use expression property and assign a it getdate(). or what ever date calculation you want, using getdate(). than Use a OLEDB source component to execute stored procedure, here you can map your SP's parameter to your SSIS variable. Gaurav Gupta http://sqlservermsbiblog.blogspot.com/

add a package level variable of datatype you want, let's assume you want a variable of type string . and name variable as DynamicValue for example. then add another package level variable, of string data type, let's name it as QueryCommand. then right click on variable, and select properties, in the properties window set expression with command like this: "exec dbo.MyStoredProcedure "+@[User::DynamicValue] and also set EvaluateAsExpression property of the QueryCommand variable to True. now in the data flow task, in the oledb source, set data access mode as sql command from variable. and set QueryCommand Variable as variable name. Note that you should set default valid value for DynamicValue variable. http://www.rad.pasfu.com