Using User Defined Function In Execute SQL Task Within Foreach Loop

hi



I am trying to execute a UDF in an Execute SQL Task within a Foreach loop.

I have an Execute SQL task to get the dataset (Firstname, Lastname, CompanyID) which is going into a full result set called @PendingPar. The Foreach Loop is set to a Foreach ADO Enumerator against @PendingPar and I have created 3 variables at the Foreach Loop scope level called @Firstname (String), @Lastname (String) and @CompanyID (Int32). These are mapped in the Foreach Loop as index 0,1 and 2.



Within the Foreach Loop I have an Execute SQL Task with the sql command



select dbo.udf_GetParTitle (?,?,?)



In the parameter mapping I have Variable Name = User::Firstname Direction = Input Data Type = VARCHAR and Parameter Name = 0, User::Lastname, Input, VARCHAR, 1 and User::CompanyID, Input, VARCHAR,2



Finally the UDF returns a @NewPar value so i have the result set in the execute sql task to Single Row which goes into variable @NewPar. This @newPar will be used later but I cannot get the first bit to work yet.



the error message i get back is



Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "select dbo.udf_GetParTitle (?,?,?)

" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task 1



the initial execute sql task where the dataset is selected works fine but it is within the foreach loop that it fails.



any help gratefully recieved