execute SQL task with in a For Each Loop

hi guys,

i'm trying to do a bulk insert through a t-sql which accepts 3 params.

i frist run a SQL statement to get a list of DB's to update through a SQL task, then i save the output to a variable object. which is been used by the For each loop.

i've done the mapping with the object fine. but i want to call a SQL task within for each loop, and parse the above results as parameters. i tried parsing it as

exec mysp3 @.[User::fundid], @.[User::dbCode], @.[User::subfund]

where fundiid, dbcode and subfund are user defined variables that gets updated by the ado recordset.

this fails with the error msg:

SSIS package "SP test.dtsx" starting.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec ConnectODBC @.[User::fundid], @.[User::dbcode], @.[User::subfund] failed with the following error: "Incorrect syntax near 'User::fundid'.". 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

Warning: 0x80019002 at Shred the contents of the variable: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "SP test.dtsx" finished: Failure.

i also tried

exec mysp3 ?, ?, ?

then mapping the inputs still doesn't work

same thing with

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec ConnectODBC ?,?,? 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

Look here:

http://www.sqlis.com/default.aspx?81

|||

Thanks Loom,

just what i wanted. i didn't know you have to interpret your expressions differently depending on the connection type. well now i do

by the way because of these different data conection types, my variable types are different. for example i save my fundid variable as int32. when using a OLE DB connection under parameter mapping there's no int, but numeric and it craps out when i try to do the mapping. is there a way to cast this? when passing to the t-sql?