about update

when transfering data from text file to sql server using dataflow how can i update a record if it exists.Is it not possible to use or call stored procedure in the dataflow.

please let me know

I am doing as it is shown in the link you provided.but i connect to a database if it had to update a record after using union all.

And it inserts a new record.

Would you please make it much more clear with it.

I am doing as provided in the link.

What is happening here is

1.I am sending the same text file.

First time if the table does not have records it will insert all the record.

When i run the same package for second time.It is the same text file no changes made.

when i use conditional spit it comest up with few records as update.Records already exists with no change.

and what it does is it does not update the existing record but inserts a new bunch of records

in the conditional split

what the codition which i had give is this

output name:fields change

[Column 1] != LookUpSysId || [Column 0] != LookUpCeOrgCode || [Column 2] != LookUpMrn || [Column 3] != LookUpServiceDate || [Column 4] != LookUpPxCode || [Column 5] != LookUpPxCodeType || [Column 6] != LookUpPxDesc || [Column 7] != LookUpOrderingProviderCode || [Column 8] != LookUpOrderingProviderIinternalId || [Column 9] != LookUpLoc

please let me know

I have a problem with updating a record.It inserts it with new value instead of update.

What is happening here is i have a look up where i am checking if the incoming data is new or it already exists.

which is working fine.

then i have a conditional split where i check to see if any modifications are done to records

what i do here as place a condition [coulnm 0] != code || [column1] != state.i write the check condition for all my columns like this.

placing || in between.

if this output is success i transfer to update database else

i dont use database

what am i doing wrong here .please let me know.

sureshv wrote:

I have a problem with updating a record.It inserts it with new value instead of update. What is happening here is i have a look up where i am checking if the incoming data is new or it already exists. which is working fine.

If you configured the lookup correctly (and I guess so because you said is working correctly) you should be getting rows in the 2 outputs of your lookup. The error output (red arrow) represents no-existing rows and would be the ones to be inserted hence need to be followed by a destination component. The output (green arrow) represents the existing rows; the ones to be updated hence, and following Jamie¡¯s article, should be followed by the conditional split to determine if a change has occurred and then for either and OLEDB command transform to perform the update.

Notice that Union all transforms in the article are not required; they just mean the ¡®end of the dataflow¡¯.

Also, check the error output of the lookup transform is set to ¡®redirect rows¡¯; as that is not explicitly explained on Jamie¡¯s article.

If after checking all this details you still get only inserts; that means your lookup is failing (not matches found); so review the mapping logic in the lookup (yes again!)

BTW; have you downloaded the sample package on the article

I havent downloaded any sample package on this article.please let me know where i can get that.

I am explaining in detail please guide me with this.

I have 10 columns coming  from my text file.

In the lookup i connect one key column and check in the check box (available inputs to available lookups) and  the chekboxes i check one more key column

What hapens when i run is that if any information in these two columns is changed it inserts as a new record which is correct (hope it is).

i connect the green arrow to conditional split

there what is that codition which i need to give to check if the record has changed or not changed.

How do i use the oldb command to figure out if insert or update

would you please let me know

thank you

Rafael Salas wrote:

Also, check the error output of the lookup transform is set to ¡®redirect rows¡¯; as that is not explicitly explained on Jamie¡¯s article.

Thanks Rafael - spot on as usual. I've updated the article to state this.

-Jamie