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


The SSIS Data Pump - Step 2 of the Stairway to Integration Services


The SSIS Data Pump - Step 2 of the Stairway to Integration Services

Author
Message
Andy Leonard
Andy Leonard
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2425 Visits: 1102
Comments posted to this topic are about the item The SSIS Data Pump - Step 2 of the Stairway to Integration Services

Andy Leonard
Data Philosopher, Enterprise Data & Analytics
Ron Kunce
Ron Kunce
SSC Eights!
SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)

Group: General Forum Members
Points: 840 Visits: 496
Andy,

The most terrific pair of articles I've read in a long time. I was particulary impressed with the way you handled options and errors. Most all of the other SSIS articles out there seem to follow a single best-case process flow explanation without touching on all the possible options or errors. You seemed to cover these quite well without getting bogged down in too much explanation or diversion from the main thrust of the topic. I cannot wait until the next articles in this chain are posted.

This last brings up the only confusion I have as this is February 20, 2011; yet you have dated your Step 2 article with a date of 2011/04/06, which I believe may have something to do its future appearance date in the main SQLSeverCentral newsletter issue, even though it is already part of the Stairway series. However, I sure hope I don't have to wait that long in seeing the followup articles posted here.

But, again Terrific articles on clearing up mysteries of SSIS for those of us just starting out in using it professionally. Also, I am shocked that I am the first to rate it and comment on it!

Ron K.

"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
miki_home
miki_home
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: 125
Andy this is brilliant. It deals with "how to" in a way an novice like me can understand, whilst showing us how to do something the real world might actually want us to do.

Keep it up? Please?

MIKI
Read It
Read It
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 100
Great job!!! Really, really looking forward to the next one. Is it done yet? Whistling
iamviren
iamviren
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 139
GReat Job Andy....... Really helpful for newcomers from starting..... :-)
Roland Brown
Roland Brown
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 56
How about something that occurs frequently in the business world - loading extenral data from a csv, txt or xls file? And please explain the process of creating the staging table. My requirement may involve a fresh staging table each time (DROP/CREATE or TRUNCATE), or I may want to append data to an existing table.

I'm also interested in learning best practices for the transform stage. Should the initial code load the data "as-is" from the csv and then run a SP to execute the field manipulation and transform logic, or should that logic reside in SSIS code. How does SSIS-centric code remain maintainable and in a central repository (other than msdb)?

Finally, I'm interested in error reporting and correction strategies. If the bulk insert fails on a few rows what is the recommended procedure for identifying and presenting these rows to the user for correction? How should the input file be archived post ETL?

I strongly urge the author to work through a "real world" ETL scenario from an externally sourced CSV file(s), FTP'd into a local server folder, through data load, transformation, error correction and reporting, archival and balancing. This is a fairly standard scenario that all IT professionals confront on a frequent basis.

Thank you.
Anipaul
Anipaul
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9919 Visits: 1407
Great writing .. I am loving to read this....



Samuel Vella
Samuel Vella
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2089 Visits: 2144
Lovely article

One extra tip about formatting the layout.... the first component you select will be used as the source for the other components new placement on the data flow canvas.
So if you have 5 components to realign select the one which you know is in the right place, then ctrl+click (or ctrl+drag select) to select the others then perform the highlighting operation.
This is very useful when you've got quite a few components which need arranging relative to each other but not all at once.

I do take a slight issue with your conclusion that it covered at lot of features... it barely scratched the surface in my opinion but I hope you'll be covering the rest in later articles!
Mark Cowne
Mark Cowne
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6718 Visits: 25623
Great articles and timely (for me!). One question though, for the OLE DB Destination fast load, are there any recommended values for "Rows per batch" and "Maximum insert commit size" or are they better left as defaults?

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537




Andy Leonard
Andy Leonard
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2425 Visits: 1102
Thanks everyone for reading the article, the helpful suggestions, and kind words!

Roland: I have plans (and outlines) to show loading from several sources. Excel and flat files are included. Your question about transform strategy is interesting because the answer varies from use case to use case (a fancy way of saying "it depends"). There are use cases for ELT, for example, where I use SSIS to extract the data from a source and load it to the destination, and then perform the transformations using T-SQL executed by Execute SQL Tasks. I'll cover that, either here or in an upcoming article on Data Warehouse Development (please stay tuned). Stage table creation strategy is largely driven by the client, culture, and data environment. I often employ the technique demonstrated in this article: using the New button beside the Name of the Table or View dropdown in the destination adapter. I recommend copying the Data Definition Language (DDL) statement from the Create Table window and storing it in a .sql file - something I failed to cover in the article. The .sql file can then be imported into the SSIS project (under Miscellaneous) and source-controlled right alongside the SSIS packages. At deployment time, the .sql files are executed and the packages deployed.

Samuel: There is more to come. :-)

Mark: There is often a balance to be struck between speed and concurrency. Batch and Commit size settings for fast loads are going to vary based on many factors (another fancy way of saying "it depends"). Fast loads run much faster than non-fast-loads, but they also lock the table differently. You can use the properties to manipulate the load in a way that achieves the balance (between speed and concurrency) you seek.

:{>

Andy Leonard
Data Philosopher, Enterprise Data & Analytics
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