Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

The SSIS Data Pump - Step 2 of the Stairway to Integration Services Expand / Collapse
Author
Message
Posted Monday, February 14, 2011 10:37 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:39 PM
Points: 388, Visits: 1,034
Comments posted to this topic are about the item The SSIS Data Pump - Step 2 of the Stairway to Integration Services

Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #1063686
Posted Sunday, February 20, 2011 12:50 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, March 17, 2014 2:19 PM
Points: 457, Visits: 470
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
Post #1066884
Posted Monday, March 21, 2011 8:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 9:10 AM
Points: 14, Visits: 117
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
Post #1081343
Posted Wednesday, March 30, 2011 9:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 10:36 AM
Points: 5, Visits: 97
Great job!!! Really, really looking forward to the next one. Is it done yet?
Post #1086313
Posted Wednesday, March 30, 2011 7:10 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 27, 2014 10:10 AM
Points: 51, Visits: 127
GReat Job Andy....... Really helpful for newcomers from starting.....
Post #1086587
Posted Tuesday, April 05, 2011 10:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 16, 2013 3:03 PM
Points: 5, 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.
Post #1089009
Posted Wednesday, April 06, 2011 3:33 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, March 20, 2014 7:32 AM
Points: 5,191, Visits: 1,368
Great writing .. I am loving to read this....


Post #1089118
Posted Wednesday, April 06, 2011 3:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:46 AM
Points: 335, Visits: 1,956
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!
Post #1089131
Posted Wednesday, April 06, 2011 4:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:13 AM
Points: 1,694, Visits: 19,550
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?


____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1089139
Posted Wednesday, April 06, 2011 6:56 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:39 PM
Points: 388, Visits: 1,034
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
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #1089240
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse