Integration Services in the Real world

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