LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1879
Members Online:
0
Guests Online:
42

Total Post History
Posts:
81451
Topics:
18716

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
mwojcik

Other

FAQ
All times are UTC [ DST ]

SSIS "Upsert" In Data Flow Task?

Microsoft SQL Server
Please wait...

SSIS "Upsert" In Data Flow Task?

Postby AlexCuse on Fri Mar 01, 2013 5:45 pm

This is what I've got. Seem reasonable?

I realize I could just dump everything into a staging table and slice and dice using T-SQL but that seems a bit more likely for something to go haywire.

Capture.PNG
You do not have the required permissions to view the files attached to this post.
Say what you like about the tenets of National Socialism Dude, at least it's an ethos
User avatar
AlexCuse
LTD Admin
LTD Admin
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031
 
Posts: 5523
Joined: Tue Oct 09, 2007 5:26 pm
Location: Pennsylvania, US
Unrated

Re: SSIS "Upsert" In Data Flow Task?

Postby onpnt on Fri Mar 01, 2013 6:06 pm

That is exactly how the process is typically done. Although, in some really large tasks, a staging table and the same processing model in SSIS is needed. There are some reasons to that - locking issues and resource usage during the load. But that is when a row-by-row validation gets the point it takes far too long. Even in those cases, fine tuning the DF engine and buffer still lends itself to enhancing performance to a point you can really pump through a lot of rows really fast.
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI

Re: SSIS "Upsert" In Data Flow Task?

Postby onpnt on Fri Mar 01, 2013 6:13 pm

Take advantage or event handlers on the areas also as well as error handling so you don't have one row completely kill the entire process.
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI

Re: SSIS "Upsert" In Data Flow Task?

Postby AlexCuse on Fri Mar 01, 2013 6:16 pm

Cool thanks - been into this stuff the last few weeks after not doing any of it for a few years. Good to know I haven't totally lost it ;)

I'm on 2008 now instead of 2005 - loving that I can write the script components in C# now!
Say what you like about the tenets of National Socialism Dude, at least it's an ethos
User avatar
AlexCuse
LTD Admin
LTD Admin
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031
 
Posts: 5523
Joined: Tue Oct 09, 2007 5:26 pm
Location: Pennsylvania, US
Unrated

Re: SSIS "Upsert" In Data Flow Task?

Postby onpnt on Mon Mar 04, 2013 1:56 am

I remember how happy that one made me too. The data flow engine internals are far better in 2008 as well. 2012 is even better yet, when you are ready to get there.

Let me know if I can help out at all.
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI
Unrated