as i'm learning ssis, i have quick question table a -> lookup table a id in table b and get table b id -> lookup table a id in table c -> on found, update table c field b_id from table b.id table a -> fields id table b -> fields id and a_id (refer to table a) table c -> field id and b_idmark it as answer if it answered your question :)

you can use OLEDB Command to pass a column value as a parameter to update command after second lookuphttp://www.rad.pasfu.com

i know how to insert but not updatemark it as answer if it answered your question :)

after lookup transform , connect data path ( green arrow) to oledb command, write your update command in the oledb command, like this: update tableX set field2=? where id=? in this statement question marks are parameter markers , this means that you can set your input column in oledb command as these parameter, you should map input columns with these parameters in column mappings tab.http://www.rad.pasfu.com

i'm using ado.net to connect to dsn because my db is postgres sqlmark it as answer if it answered your question :)

it shows the error, oledb is incorrect type if i use the connection for ado.netmark it as answer if it answered your question :)

use an OLEDB connection and try againhttp://www.rad.pasfu.com

here is the deal, i have a system dsn for my postgres sql and that is what i'm using with ado.net to connect to it. as you suggested, using oledb, oledb command doesn't allow me to using ado.net . does it make sense or i'm missing something else?mark it as answer if it answered your question :)

so you can use a record set destination in data flow task to fill result data stream in a OBJECT Type variable. and then in control flow, after data flow task, put a foreach loop variable to loop through this variable with enumerator as ado. then fill each column you need in a variable in variable mappings tab in foreach loop editor. then add an execute sql task inside foreach loop and set connection as ado.net connection there. and write your update command with parameters there. for ado.net connections, you should use sql command like this: update tableX set field1=@InputColumn where id=@ID and in parameter mappings tab, set variables to this parameters. for list of parameter markers in execute sql task look at this link: http://technet.microsoft.com/en-us/library/ms140355.aspxhttp://www.rad.pasfu.com

hmmm..sounds good. let me try this and get back to you.mark it as answer if it answered your question :)

this is how far i reach.. created data flow task and put it as a recordset destination and it is stored in a variable called rsOrder and variable type is object with two column out (id and order_id) data flow task is connected to for each loop container and setting for "foreach loop" is below:- collection - enumerator - foreach ado enumerator ado object source variable : user::rsOrder checked - rows in the first table. variable mappings - created new variable of type object and it is called id and index given 0 created new variable of type object and it is called order_id index given 1 in foreach container a exectue sql task is created and it is configured as below:- on general tab - connection type = ado.net connection is given = myadonet sqlsourcetype = direct input sql statement = update mytable set order_id=@order_id where id=@id on parameter mapping tab - Variable Name = User::id direction = input Data type = object parameter name = @id variable name = User::order_id direct = input data type = onject parameter name = @order_id it go thru reacordset destination but throws the error at execute sql task, if i remove execute sql task, it works fine (for loop works) and here is the error Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. 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. any idea?mark it as answer if it answered your question :)

Doubt your variables should be object types - type should correspond to the data that is being passed through them so string for text or integer for numbersRgds Geoff

Except for the one that contains your recordset - that of course should be an object..Rgds Geoff

what is your error message? the message you put here is not error , it is warning. put error message herehttp://www.rad.pasfu.com

reza, it is not showing any error in progress tab, just the warning. geoff, if i try to make id and order_id as integer or string then it throws the following error Error: The type of the value being assigned to variable "User::id" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. Error: The type of the value being assigned to variable "User::order_id" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.mark it as answer if it answered your question :)

could you drop these variables and create new variables , don't use object type for User::id and User::order_id variables. http://www.rad.pasfu.com

i figured it out and put the details here soon. thanks for your helpmark it as answer if it answered your question :)

sorry for late reply, here is what i did? i used odbc to connect to postgres sql (ado.net was fine to read and bulk insert but for update it was not working so i have to use odbc) also played around with parameters and finally achieved the result. if need detail information, i can put it here for future help to other members. thanks a lot for helping me out. ciao, pmark it as answer if it answered your question :)