execute sql task into a ADO loop

Hi all,

I got this problem and before post I've read many post similar in the forum (sorry for my english I try to do my best....i'm italian)

This is my scenario.

1) SQL

SELECT

UPPER(SUBSTRING(FileImportazione, 7, 50)) AS str,

Id

FROM Sources

WHERE (TipoImportazione = 0)

This is an SQL task and from this I create a Object variable "ObjDs" on scope package1

2) I introduce a loop (Foreach ADO Enumerator) on my object variable "ObjDs".

Here I mapping two variables "IDsources" and "str" (as you caqn see from my first sql)

Now I set a breakpoint into the beginning of my cicle and really see the dinamic change of the values from "watch" windows.

Now start my problem.

Into this loop I must do "X" operation....but first of all I must Update a table...so I simple SQL TASK...on the dinamic ID of my variable.

Ok, I put a SQL tast into the loop, set SQLsourceType = Variabile, resulset = none, and create an expression like this (SQLstatementsource):

"UPDATE New_pangea.dbo.Sources SET InAggiornamento = "+ (DT_WSTR, 4) @.[Utente::IDsources]

If I don't cast (DT_WSTR, 4) I got always an error...

The data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Attempt to set the result type of binary operation ""UPDATE New_pangea.dbo.Sources SET InAggiornamento = " + @.[Utente::IDsources]" failed with error code 0xC0047080.

so I put and validate myexpression.

before my global variable @.[Utente::IDsources] change the value to true of EvalutateAsExpression.

Something goes always wrong...When I try debug...this is the error

Errore in Execute SQL Task: Failed to lock variable "UPDATE New_pangea.dbo.Sources SET InAggiornamento = 0" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Errore in Execute SQL Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

Hope someone can help me, thanks Alen

Make sure the variables have the proper scope - it sounds like one of your variables is scoped to a single task. In the variables window, make sure the scope is set to the package for all variables.|||

I've checked...al my variables are on the packagin scope.

Where can be problems?

And the stange is if I print my var on a pop-up windows...I see rigth value...

I don't understood as can print my var correctly but can create a dinamic SQL.

MsgBox(Dts.Variables("str").Value.ToString & " " & Dts.Variables("IDsources").Value.ToString)