1 / 31

Integration Services in the Real world

Integration Services in the Real world. Brian Garraty, Database Administrator Virginia Beach City Public Schools. Who I Am. DBA for Virginia Beach City Public Schools 10 years working with SQL Server 5 years working with SSIS MCITP: DBA. Who You Are. DBAs? Developers?

calum
Download Presentation

Integration Services in the Real world

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. Integration Services in the Real world Brian Garraty, Database Administrator Virginia Beach City Public Schools

  2. Who I Am • DBA for Virginia Beach City Public Schools • 10 years working with SQL Server • 5 years working with SSIS • MCITP: DBA Integration Services in the Real World, Brian Garraty

  3. Who You Are • DBAs? • Developers? • DBAs with Development Background? • SSIS experience? DTS experience? • SSIS fans? SSIS haters? Integration Services in the Real World, Brian Garraty

  4. Itinerary • Introduction • Real World SSIS Case Studies • Wrap Up • Q&A • Resources Integration Services in the Real World, Brian Garraty

  5. “You sure are a great cook! Can you farm?” –Mitch Hedberg Integration Services in the Real World, Brian Garraty

  6. What SSIS Buys Me • Flexibility • Performance • Rapid development • Dependability • Security Integration Services in the Real World, Brian Garraty

  7. SSIS Rather Than… • xp_cmdshell • bcp/BULK INSERT • osql/SQLCMD • Linked Servers • Stand-alone .NET Projects Integration Services in the Real World, Brian Garraty

  8. How I Learned • Read “Paradigm Shift” article • Rewrote monster, unwieldy stored procedure • True user documentation (blogs) • Rewrote first SSIS project Integration Services in the Real World, Brian Garraty

  9. “Since SSIS has been my hammer, A lot of problems have started looking like nails.” – Me Integration Services in the Real World, Brian Garraty

  10. Case Study #1 Storing Snapshot of Active Directory in SQL Server Tables Integration Services in the Real World, Brian Garraty

  11. AD Snapshot: Requirements • Query Active Directory for: • User Accounts • Groups • Group Memberships • QA the results • Load data into SQL Server tables Integration Services in the Real World, Brian Garraty

  12. AD Snapshot: Linked Server Approach • Create linked server to ADSI • Query linked server to temp tables • QA temp tables (2nd iteration) • Replace data in destination tables from temp table Integration Services in the Real World, Brian Garraty

  13. AD Snapshot: Linked Server Problems • Partial results (limited to 2000 results) • Timeouts without error message • “An error occurred” Errors • Not Configurable Integration Services in the Real World, Brian Garraty

  14. AD Snapshot: SSIS Approach • Script tasks in Control Flow • Query AD via DirectoryServices.DirectorySearcher • Stage results in XML • QA • Execute SQL Task for row counts • Script task to check threshold and throw error Integration Services in the Real World, Brian Garraty

  15. AD Snapshot: SSIS Approach (con’t) • Expression Precedence Constraint • For each item • Truncate via Execute SQL Task • Data Flow Task loads table from XML Integration Services in the Real World, Brian Garraty

  16. Case Study #2 Index Defragmentation Integration Services in the Real World, Brian Garraty

  17. Index Defrag: Requirements • Targeted defragmentation • Configurable thresholds • Support for 2005 syntax and features • Report-only mode Integration Services in the Real World, Brian Garraty

  18. Index Defrag: Without SSIS • Many choices • Powershell • Stored procedure • SMO • Valid options - No problems slide here Integration Services in the Real World, Brian Garraty

  19. Index Defrag: SSIS Approach • Generic SSIS package – can run on any server • Store thresholds in configuration • ForEach with SMO to loop dbs Integration Services in the Real World, Brian Garraty

  20. Index Defrag: SSIS (con’t) • Dynamic SQL to query indexes • sys.dm_db_index_physical_stats • sys.dm_db_index_usage_stats • ForEach with NodeList to loop indexes • Script Task to build defrag statement Integration Services in the Real World, Brian Garraty

  21. Other Projects • Monster Stored Procedure • Queries by Committee • Rendered Report Compare • Nightly Data Extracts via Secure-FTP Integration Services in the Real World, Brian Garraty

  22. Wrap Up Integration Services in the Real World, Brian Garraty

  23. Praise for SSIS • Parallelism • Expressions • Configurations • .NET Integration Services in the Real World, Brian Garraty

  24. Praise for SSIS (con’t) • Debugging • Data Viewers • Logging • Security • No linked servers • Not necessarily dependent on SQL Service Account Integration Services in the Real World, Brian Garraty

  25. Gripes: Dark Side of SSIS • GUI intensive • Visual Studio outside DBA comfort zone • Requires .NET Skills Helpful • What you see != What you get • Expressions • Configurations Integration Services in the Real World, Brian Garraty

  26. Gripes: Dark Side of SSIS (con’t) • Source Control Integration • Expressions – Syntax and Editor • Copy/Paste and ID’s • Sorting – DB vs. SSIS Integration Services in the Real World, Brian Garraty

  27. My* Top 10 Tips and Best Practices • Store packages as files - always • ROOT_FOLDER and common directory structure • Naming Conventions - Project, Package, and Task • Use indirect configurations • Break project into multiple packages Integration Services in the Real World, Brian Garraty

  28. My* Top 10 Tips and Best Practices • Do not sort in T-SQL • Use logging – expression can timestamp file name • Learn to use ForEach container with NodeList • Use CmdExec steps in SQL Agent with Proxy Accounts to run packages • Create separate SSMS solution to manage source control of all T-SQL code Integration Services in the Real World, Brian Garraty

  29. Questions and Answers? Integration Services in the Real World, Brian Garraty

  30. Resources • Jamie Thomson’s old blog (now on SQLBLOG) • http://bit.ly/5BeYDh • Paradigm Shift Article on SSC • http://bit.ly/5nMIks • SQL Lunch • http://sqllunch.com • My Blog • http://NULLgarity.wordpress.com • #SSISHELP Integration Services in the Real World, Brian Garraty

  31. Thanks! Brian Garraty twitter.com/@NULLgarity NULLgarity.wordpress.com NULLgarity@gmail.com Integration Services in the Real World, Brian Garraty

More Related