Today I was using the SQL Task and I needed to use a where clause in my SQL statement. It was easier then I thought. Here are the steps for the test. Of course the db that we use is AdventureWorks
Step 1: Create a new OledbConnection
Create a new Oledb Connection that points to the AdventureWorks Database
Step2: Create a SQL Task
Drag a SQL task on the control flow and change the value of the follow properties on the General Page
- ResultSet -> FullResult
- Connection -> the connection that you just created that points to the adventureWorks database
- Sql Statement -> select * from purchasing.PurchaseOrderHeader where ShipMethodId = ?
Step 3: Create a new Variable
We want to filter on the ShipmentMethodId so we define a new variable called ShipmentMethodId and we set the value to 5.
Step 4: Define the Parameter in the SQL task
Now we can add a parameter to our SQL task that maps to our Variable. Go to the Parameter Page of the SQL task and add a parameter with follow properties.
- Variable Name: select the Variable ShipmentMethodId
- Parameter Name: change the value to 0
Remark: The Parameter name of a command depends on the type of connection that you use. You can find summary tabel in the BOL under the section "Running Parameterized SQL Commands"
Step 5: Store the ResultSet in a Variable
Create a new variable and called it "Data" and the datatype is object. Go to the ResultSet Page of the SQL Tasks and define a ResultSetName called 0 and map it to the variable "Data".
Step 6: Run the sample
Open SQL Profiler and run you package. You should see something like on the image below.
You can download the sample from here.