InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 14:53:53 GMT20http://www.sqlservercentral.com/Forums/Topic1188881-208-1.aspxAndy,I have a question about the operation and flow of records. Let's make the following change to your data.. Let's assume that the data coming in was using a super key (FirstName, LastName, Email). Then we write the data using only the PK E-Mail, but we grab all the records.(Lets asume we had the following data already there.. Tim, Jones, tj@aol.com. : Now the data coming in.. (Tim, Jones, Timj@aol.com), (Tim, Jones, Jonest@aol.dom), (Tim, Jones, tj@aol.com)I think the operation would be.. All records would go to the matching and then tj@aol.com would be dropped, since it matches. The others would update the existing record, so only the last write would exist in the database.My test seems to prove this... just want to make sure my logic is correct.Tue, 26 Feb 2013 07:39:31 GMTdwilliscphttp://www.sqlservercentral.com/Forums/Topic1188881-208-1.aspxI had the same Problem, when copying the expression over.It's the quotation marks.(FirstName != LkUp_FirstName) || ((ISNULL(MiddleName) ? [b]“[/b]Humperdinck[b]”[/b] : MiddleName) != (ISNULL(LkUp_MiddleName) ? [b]“[/b]Humperdinck[b]“[/b] : LkUp_MiddleName)) || (LastName != LkUp_LastName)Delete these quotes around Humperdinck and type them back in. This should fix the problem.Another way like already mentioned is copying your expression over to Notepad. This will remove all formattingFri, 12 Oct 2012 07:17:44 GMTheinrich.angelahttp://www.sqlservercentral.com/Forums/Topic1188881-208-1.aspxAndy,I did that, and it did the trick. Thanks again!Fri, 25 May 2012 08:12:31 GMTG13Mhttp://www.sqlservercentral.com/Forums/Topic1188881-208-1.aspxThere could be another character hiding out at character 57. My suggestion: Copy the expression to Notepad, make sure it's all on a single line, then copy from Notepad back into the Expression field.Hope this helps,AndyThu, 24 May 2012 20:07:10 GMTAndy Leonardhttp://www.sqlservercentral.com/Forums/Topic1188881-208-1.aspxHi Andy, thanks for the great stairway!I'm having a problem with the condition in the Conditional Split. I copied it exactly as you have it:(FirstName != LkUp_FirstName) || ((ISNULL(MiddleName) ? “Humperdinck” : MiddleName) != (ISNULL(LkUp_MiddleName) ? “Humperdinck” : LkUp_MiddleName)) || (LastName != LkUp_LastName)But I get this error:Error at Data Flow Task [Conditional Split [104]]: Attempt to parse the expression "(FirstName != LkUp_FirstName) || ((ISNULL(MiddleName) ? “Humperdinck” : MiddleName) != (ISNULL(LkUp_MiddleName) ? “Humperdinck” : LkUp_MiddleName)) || (LastName != LkUp_LastName)" failed. The token " " at line number "1", character number "57" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.Error at Data Flow Task [Conditional Split [104]]: Cannot parse the expression "(FirstName != LkUp_FirstName) || ((ISNULL(MiddleName) ? “Humperdinck” : MiddleName) != (ISNULL(LkUp_MiddleName) ? “Humperdinck” : LkUp_MiddleName)) || (LastName != LkUp_LastName)". The expression was not valid, or there is an out-of-memory error.Error at Data Flow Task [Conditional Split [104]]: The expression "(FirstName != LkUp_FirstName) || ((ISNULL(MiddleName) ? “Humperdinck” : MiddleName) != (ISNULL(LkUp_MiddleName) ? “Humperdinck” : LkUp_MiddleName)) || (LastName != LkUp_LastName)" on "output "Updated Rows" (120)" is not valid.Error at Data Flow Task [Conditional Split [104]]: Failed to set property "Expression" on "output "Updated Rows" (120)".Character number "57" is the space before the first '?'. If I remove the space, it still complains but about the '?' itself. I am running 2008R2.Any ideas?Wed, 23 May 2012 13:45:15 GMTG13Mhttp://www.sqlservercentral.com/Forums/Topic1188881-208-1.aspx[quote][b]ddriver (10/12/2011)[/b][hr]I do this sort of operation fairly frequently and just looking at the SSIS package for even the simple case in this example sets me on edge. There are plenty of tricks for doing this from a staging table, like comparing checksums rather than individual fields. Also our processes identify that a records needs updating and they report against what was updated on a field by field basis, including previous and new values for just the changed fields. I really don't see how you would do that in SSIS while maintaining any performance.[/quote]I agree with every you say here, particularly the checksum method. I would add though that for small, and always will be small update sets, the RBAR method in SSIS is useful and quick to implement. I usually use the SSIS RBAR for systme monitoring type process such as sweeping through jobs on all servers looking for failure times or abnormal durations, where change logging is not a requirement.Wed, 19 Oct 2011 17:13:08 GMTnogoodboyohttp://www.sqlservercentral.com/Forums/Topic1188881-208-1.aspxAll of the above comments boil down to: Should we use an ELT or ETL approach?ETL being what SSIS is designed to do - transform the data in a stream in the ETL tool.ELT being how DTS used to work - load the data into staging tables into the database and do all the work in the database.Its only worth using a ETL approach if we already know the state of the records in the source. Which in turn requires triggers and logs etc. in the source.The RBAR update constraint basically means you need to make this a ELT approach. Then you may as well make the entire thing ELT rather than having to contend with two different approaches.Thats my thoughts.Sun, 16 Oct 2011 21:24:31 GMTnick.mcdermaidhttp://www.sqlservercentral.com/Forums/Topic1188881-208-1.aspxI do this sort of operation fairly frequently and just looking at the SSIS package for even the simple case in this example sets me on edge. There are plenty of tricks for doing this from a staging table, like comparing checksums rather than individual fields. Also our processes identify that a records needs updating and they report against what was updated on a field by field basis, including previous and new values for just the changed fields. I really don't see how you would do that in SSIS while maintaining any performance.Wed, 12 Oct 2011 14:30:05 GMTddriverhttp://www.sqlservercentral.com/Forums/Topic1188881-208-1.aspxSimilar question to dheeraj.marwaha, although not the same. Had to do this recently for a process that changed, and after building it using lookups and running into trouble because it wasn't set-based, I'm confused. In short, if we're going to identify the add/change/delete records then do set-based updates from a staging table, why would we EVER want to use lookups in SSIS? Why not just grab the inbound file and load straight to a staging table, then compare in SQL? No need to write expressions, process the file to identify issues, etc?Wed, 12 Oct 2011 07:27:03 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic1188881-208-1.aspxHi Andy,Thanks a lot for detailed discussion. But my concern in this implementation is related to slowness when the number of records are more. Unnecessary, this package will compare all the record in source table to all the records in destination table to find its state.My suggestion is to use staging table which will contain only the delta changes in the source table and comparison should be done in delta records.Wed, 12 Oct 2011 05:16:10 GMTdheeraj.marwahahttp://www.sqlservercentral.com/Forums/Topic1188881-208-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Stairway+Series/76390/">Updating Rows in Incremental Loads – Level 4 of the Stairway to Integration Services Services</A>[/B]Wed, 12 Oct 2011 00:05:53 GMTAndy Leonard