1 / 138

Real-World SSIS

A Survival Guide Tim Mitchell. Real-World SSIS. What we’ll cover today. Lessons I’ve learned the hard way Methodologies to solve real problems in SSIS Tools to help out Solutions for SQL 2012 as well as earlier versions Demos. What we won’t cover. No intro to SSIS Books Online.

clea
Download Presentation

Real-World SSIS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. A Survival Guide Tim Mitchell Real-World SSIS

  2. What we’ll cover today • Lessons I’ve learned the hard way • Methodologies to solve real problems in SSIS • Tools to help out • Solutions for SQL 2012 as well as earlier versions • Demos

  3. What we won’t cover • No intro to SSIS • Books Online

  4. Housekeeping • Presentation materials • Lunch / breaks

  5. Housekeeping • Let’s keep it informal • Ask questions • Ask questions • Ask questions

  6. PSA: Community • Survival is easier in groups • Local user groups • Events (SQL Saturday, SQL Bits, PASS Summit) • Online communities • Twitter (#sqlhelp)

  7. About me • Business intelligence consultant • Group Principal, Linchpin People • SQL Server MVP • TimMitchell.net / @Tim_Mitchell

  8. Texas Dictionary • Whole mess: Bountiful amounts of something, usually referring to excess • More than one way to skin a cat: A pet-unfriendly phrase to indicate that there are usually multiple ways to solve the same problem • Ya’ll: A subgroup of the current group • All ya’ll: The whole of the current group

  9. survival (noun) The state or fact of continuing to live or exist, typically in spite of an accident, ordeal, or difficult circumstances. Reference: Dictionary.com (http://dictionary.reference.com/browse/survival)

  10. survival (noun) Survival is simply the state of existing. It’s just a small step above being dead. -- Me Photo credit: Elvis Ripley (http://www.flickr.com/photos/elvisripley/315439191/). Used under Creative Commons license.

  11. Elements of Survival The dangers: • The elements • Predators • Foolishness of fellow survivors • The unexpected

  12. Elements of Survival The dangers: • Dirty data • Complex or poorly defined ETL requirements • Unexpected metadata changes • Unstable sources/destinations • Project managers

  13. Elements of Survival Means of survival: • Common sense of self preservation • Tools • Leaning on others • Learning from others’ mistakes

  14. Elements of Survival Means of survival: • Best practices • Consistency • Document • Tools (buy/build) • Community

  15. Plan to Fail Survival Tip #1:

  16. Planning to Fail

  17. Planning to Fail Data failures: • Missing or offline sources • Changed metadata • Partial loads • Validation issues • Unexpected domain values

  18. If it happens… When X

  19. Planning to Fail Planning for failure in the wild: • Build your shelter before it rains • Layers • Leaves • Bread crumbs

  20. Planning to Fail Planning for failure, the ETL way: • Be a pessimist! • Fail gracefully • Capture error/warning data on failure • Build for restartability (where appropriate)

  21. Failing Gracefully

  22. Planning to Fail Why graceful failure? • Avoid leaving affected systems in an inconsistent state • Avoid repeating wholesale operations • Timely notifications to allow proper response from dev/admin staff

  23. Planning to Fail Graceful failures in SSIS • Control flow: • Event handlers • Precedence constraints • Data flow: • Error row redirection • Lookup failure redirection • Conditional split

  24. Planning to Fail Graceful failures in SSIS • Restartability • SSIS Checkpoints • SSIS transactions • Both methods have shortcomings • Custom restartability can be an option

  25. Planning to Fail Natural failures • Simply stop processing on error • Default behavior • In some cases, can be the right pattern

  26. Demo Designing for failure

  27. Take Notes Survival Tip #2:

  28. Take Notes What to note? • Trails, paths, and shortcuts • Water sources • Hazards • Enemy positions • Weather and wildlife patterns • Sunrise/sunset time http://www.flickr.com/photos/defenceimages/4993131844

  29. Take Notes What to note? • Success and failure of operations • Row counts • Run times • Validation information • Warnings http://www.flickr.com/photos/mezdeathhead/3057797092/

  30. Take Notes Why? • Know what to expect • Plan for growth • Cover your assets http://www.flickr.com/photos/mezdeathhead/3057797092/

  31. Take Notes It’s all about the log. • SSIS logging • SQL Server log • Custom logging http://www.flickr.com/photos/rwphoto/3108504086/

  32. Take Notes SSIS Package Logging • It’s already there • Easy to start • Flexible events and destinations • Can be unwieldy http://www.flickr.com/photos/raver_mikey/4285637209/

  33. Take Notes SSIS Catalog Logging • Version 2012 only • Easiest to configure • Design time or runtime • Least flexible http://www.flickr.com/photos/raver_mikey/4285637209/

  34. Take Notes Custom Logging • Roll your own • Most difficult to set up • Infinitely flexible http://www.flickr.com/photos/raver_mikey/4285637209/

  35. Take Notes Server/engine logging • SQL Engine error log • DMVs • Third party tools • Windows log • PerfMon http://www.flickr.com/photos/raver_mikey/4285637209/

  36. Demo Take Notes

  37. Perform atyour best Survival Tip #3:

  38. Perform at your Best http://www.flickr.com/photos/defenceimages/5786942640/ http://www.flickr.com/photos/defenceimages/4627096866/

  39. Perform at your Best Soldier up! • Recognize and avoid quicksand • React appropriately when you’re stuck • Know your environment

  40. Perform at your Best Soldier up! • Isolate and eliminate the things that slow you down • Recognize design patterns that are detrimental to performance • Look *outside* SSIS (gasp!)

  41. Perform at your Best It’s not just SSIS • The majority of SSIS performance problems have nothing to do with SSIS • Limitations on sources and destinations

  42. Perform at your Best It’s not just SSIS • Don’t just ‘pass the buck’, but do consider other factors: • SQL engine configuration • Disk configuration • Network speed/latency • Physical machine capabilities

  43. Perform at your Best It’s not just SSIS • Proper query techniques for relational sources • Effective indexing for sources and destinations • Using OPTION (FAST <n>)

  44. Perform at your Best Streamline your data flows • Transformations matter! • Know how the blocking properties of transformations

  45. Perform at your Best Streamline your data flows • Nonblocking transforms do not hold buffers • Derived Column • Conditional Split • Row Count

  46. Perform at your Best Streamline your data flows • Partially blocking transforms will queue up buffers as needed • Merge Join • Lookup • Union All

  47. Perform at your Best Streamline your data flows • Fully blocking transforms will not pass any data through until all of the data has been buffered at that transformation • Sort • Aggregate

  48. Perform at your Best Streamline your data flows • Be aware of memory use! • LOB (large object) columns will always spool to disk rather than staying in memory. • [N]VARCHAR(MAX) • Memory buffers may spill over to disk

  49. Perform at your Best Streamline your data flows • Manage your sources • Don’t use table drop down list – specify your query including only the necessary columns • Be mindful of indexes when writing data retrieval queries

More Related