3

I am using SELECT UpdateDate FROM dbo.log command in a execute sql task. I'm fairly new to this so please bear with me. I want to store the value as a variable then pass that into the where clause of a subsequent data flow. My questions are:

  1. What is the correct way to setup the Execute SQL Task. In General I have the OLE DB Connection and direct input with the query above. Result Set is set to Single row and then I am storing this to a variable I have created called User:: UpdateDate. For some reason this doesn't work?
  2. I then want to call this date in a data flow. ie. SELECT * FROM Users WHERE RecordDate > User::UpdateDate. I believe the syntax is different for this.

I would really appreciate some help with this. Many thanks in advance

Hadi
  • 36,233
  • 13
  • 65
  • 124
skippy
  • 172
  • 4
  • 17
  • 1
    Are you returning more than 1 value in your select, that wont work (what error are you getting for it not working)? In your SQL task you need to use the parameter tab on right and select your variable there, and use a ? in your query, and the ? will assocate to the variable you use in the paramater tab/list in the SQL task – Brad Mar 15 '19 at 12:06
  • No there is only one record in that table. It brings back a date time. IE. '15/03/2019 12:47'. I just want to save that to a variable for step 1. – skippy Mar 15 '19 at 12:09
  • Then as long as result set is set to 1 row, and you have the variable in the result tab to be returned. Go to Result Set, and click Add, and put your variable name in there. It will populate it then. – Brad Mar 15 '19 at 12:11
  • Thanks Brad for your help. Unfortunately that doesn't work, to confirm: ResultSet:SingleRow SQLStatement:SELECT UpdateDate FROM dbo.log. ParameterMapping is blank, ResultSet ResultName:UpdateDate, VariableName: User::Variable. It fails that Execute SQL Step – skippy Mar 15 '19 at 12:15
  • What error are you getting? Sounds like maybe more than one record is being returned since you have no where in your select. Or your variable datatype is incorrect – Brad Mar 15 '19 at 12:19
  • Here is detailed instructions with images. Make sure you are doing all these steps (you can skip half way down). https://www.red-gate.com/simple-talk/sql/ssis/ssis-basics-using-the-execute-sql-task-to-generate-result-sets/ – Brad Mar 15 '19 at 12:21

2 Answers2

2

In your Execute SQL Task Editor, configure the Parameter Mapping as shown below, obviously use your own variable, in this example I'm using PackageStartTime.

enter image description here

Then in your SQL statement, use below:

SELECT * FROM Users WHERE RecordDate > ?

To save value from a SQL Statement, you will need to set the Result Set to single row and configure result set as shown in the example below:

enter image description here

Hadi
  • 36,233
  • 13
  • 65
  • 124
iMajek
  • 153
  • 1
  • 8
  • Thanks, that is what I was trying to go over but didnt have screen shots for it. – Brad Mar 15 '19 at 12:47
  • you might want a top 1 added to your query. There's nothing preventing multiple rows being returned. – KeithL Mar 15 '19 at 12:50
  • Why using 2 Execute SQL Task while the OP is asking for a parameterized query inside a data flow task?? – Hadi Mar 15 '19 at 19:58
1

Execute SQL Task with ResultSet

First of all, create a variable of type System.Date example: @[User::UpdateDate].

Add an Execute SQL Task select the OLEDB connection and use the following command as SQL Statement:

SELECT TOP 1 UpdateDate FROM dbo.log

Set the ResultSet property to Single Row and in the ResultSet Tab add a Row with the following values:

ResultName = 0   (which means the first column)
VariableName = @[User::UpdateDate]

Additional Information

OLEDB Source with parameterized SQL Command

Inside the Data Flow Task, add an OLEDB Source, select the Access Mode to SQL Command. And write the following command:

SELECT * FROM Users WHERE RecordDate > ?

Click on the Parameters button and map the variable @[User::UpdateDate] as the first parameter.

Additional Information

Hadi
  • 36,233
  • 13
  • 65
  • 124