SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Building an Incremental Load Package – Level 3


Building an Incremental Load Package – Level 3

Author
Message
Andy Leonard
Andy Leonard
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3479 Visits: 1113
Comments posted to this topic are about the item Building an Incremental Load Package – Level 3

Andy Leonard
Data Philosopher, Enterprise Data & Analytics
Ramesh Velayudhan
Ramesh Velayudhan
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1897 Visits: 922
Andy, Superb article! Informative, nicely written and elaborated with appropriate sample data. Can't wait to read your next article on BIML!!

Thanks a ton.Smile
jbm6401
jbm6401
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 83
Questions from a newbie that's only been scripting queries for 2+ years:

You have it set up for deltas that are in table 2 that are not in table 1, to which data from table 2 is added to table 1, which is a great idea. I see that potential on the ETL that we do. My question on this issue is what happens to the deltas that are in table 1 that are not in table 2? What we do is an update on a personnel system here and some people get deleted from the system. So I wonder if this system can remove the ones that are removed when the data is updated. I have not studied this series in super detail, so if the answer is in your series, please let me know and I apologize for the inconvenience.

Related to above, we get text files that are imported into tables within a staging database. Could this work without the use of creating additional tables for a pre_load? If not, then I guess we can do a pre_stage table and use this process to increment our actual staging tables.

Someone suggested I explore this process for a possible alternative to using bcp queryout and/or Data Flow tasks for a bunch of files created from sql scripts. Would this be part of your Stairway discussion down the road?

Thanks.
Andy Leonard
Andy Leonard
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3479 Visits: 1113
Hi jbm6401,

You will want to check out the Stairway to Integration Services for answers to these types of questions.

Hope this helps,
Andy

Andy Leonard
Data Philosopher, Enterprise Data & Analytics
jbm6401
jbm6401
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 83
Thanks.
james_luetkehoelter
james_luetkehoelter
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 252
Awesome post Andy. This will help tremendously in trying to explain some SSIS patterns to my team!
Andy Leonard
Andy Leonard
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3479 Visits: 1113
Thank you, James!

:{>

Andy Leonard
Data Philosopher, Enterprise Data & Analytics
Peter Schott
Peter Schott
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3972 Visits: 1954
Finally got around to working through the examples. Very thorough and well-documented. I look forward to that BIMLScript class before the PASS Conference now and am looking forward to the rest of the series.

Only issue I had that was odd was the test package from article #2 had a different encryption level. I rebuilt it so everything had the same encryption level, but the package wouldn't debug until I deleted that first test package.

I tried to anticipate the code you listed so I'd get a bit more experience seeing what was available. Sometimes successfully, sometimes not so much, but it was a good learning experience going through the example. Now on to #4.
theboyholty
theboyholty
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 Visits: 123
Andy this is turning into a really interesting series and I can't wait to see what further BIML wizardry you've got in store for us.
Misha_SQL
Misha_SQL
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2220 Visits: 1023
I just returned from PASS where I first heard about BIML, so this article was very timely. Thank you for this! It's very powerful stuff. I noticed that apparently biml compiler does a lot of things "behind the scenes". For example, we never specified column mapping between the output of the "Conditional Split" and stgUpdates destination. I guess since in this case the column names matched, they were simply mapped based on their names. But what if they were different, which, I suspect, is a more common scenario? We would probably need to write much more code.



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