Execute SQL task to update variables

Hi there

I'm attempting to update a variable using the Execute SQL task, I've read a lot of posts on this and seems reasonably simple but obviously not. The first time I ran it the variable was updated correctly, I then manually changed the variable value and since then it doesn't work.

I have a task with the following properties;

Resultset: SingleRow

SQLStatement: SELECT MAX(Player_Daily_Data_Pull_Date) as 'PlayerDaily' From Job_Control

On the resultset tab I have the resultsetname = PlayerDailyand the variable I want to update.

The variable has a type of datatime and it's scope is the container that I'm running the sql task within.

Any help would be appreciated.

Derek

What are the symptoms of this not working? Are there errors or warnings?

Donald

|||

Hi Donald

No symptoms or problems, the package executes successfully. Apart from the fact that the variables aren't updated at the end of the package when I look at them in the variable screen.

Derek

|||

Hi Derek,

The Variable Screen will always show the Initial Value set for that variable.

To check whether your program works fine -

1) Change the DateTime variable to 'Package' Scope.
2) Place a breakpoint for the PreExecute Event for the following task
3) Execute the Package and When the program stops for the BreakPoint - Select Debug Option -> Windows -> Locals
4) Scroll through the list and Locate the DateTime Variable.
5) You will find that the DatTime Variable is updated with the value returned by the SQL Task.

Alternatively -

1) Change the DateTime variable to 'Package' Scope.
2) On Success of the SQL Task - Have a Script Task
3) Put that DateTimeVariable in ReadOnlyVariables list in Script Task
4) Have the following lines of code in the Public Sub Main() method in the script task

Windows.Forms.MessageBox.Show(Dts.Variables("<DateTimeVariable>").Value.ToString())
Dts.TaskResult = Dts.Results.Success

5) You will observe that the datetime value returned by the SQL Statement in SQL Task will be poped up in a small popup window.

After the successful execution of the package - if you go to the Variables window - you will observe that it will only show the initial value. The runtime value assigned to the DateTimeVaribale isn't stored permanently and the runtime value will only be available during execution.

Thanks,
Loonysan

|||

Thanks Loonysan

you were right, it was working just couldn't see it.

Cheers,

Derek