Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Updating Rows in Incremental Loads – Level 4 of the Stairway to Integration Services Services


Updating Rows in Incremental Loads – Level 4 of the Stairway to Integration Services Services

Author
Message
Andy Leonard
Andy Leonard
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 1093
Comments posted to this topic are about the item Updating Rows in Incremental Loads – Level 4 of the Stairway to Integration Services Services

Andy Leonard
Data Philosopher, Enterprise Data & Analytics
dheeraj.marwaha
dheeraj.marwaha
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 101
Hi 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.
jcrawf02
jcrawf02
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1548 Visits: 19324
Similar 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?

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
ddriver
ddriver
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 250
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.
nick.mcdermaid
nick.mcdermaid
SSC Veteran
SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)

Group: General Forum Members
Points: 258 Visits: 770
All 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.
nogoodboyo
nogoodboyo
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 352
ddriver (10/12/2011)
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.


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.
G13M
G13M
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 83
Hi 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?
Andy Leonard
Andy Leonard
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 1093
There 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,
Andy

Andy Leonard
Data Philosopher, Enterprise Data & Analytics
G13M
G13M
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 83
Andy,

I did that, and it did the trick. Thanks again!
heinrich.angela
heinrich.angela
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 91
I had the same Problem, when copying the expression over.
It's the quotation marks.

(FirstName != LkUp_FirstName) || ((ISNULL(MiddleName) ? Humperdinck : MiddleName) != (ISNULL(LkUp_MiddleName) ? Humperdinck : 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 formatting
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search