INTEGRATION SERVICES IN THE REAL WORLD Brian Garraty, Database Administrator Virginia Beach City Public Schools.
Download ReportTranscript INTEGRATION SERVICES IN THE REAL WORLD Brian Garraty, Database Administrator Virginia Beach City Public Schools.
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 Integration Services in the Real World, Brian Garraty 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 Itinerary Introduction Real World SSIS Case Studies Wrap Up Q&A Resources Integration Services in the Real World, Brian Garraty “You sure are a great cook! Can you farm?” –Mitch Hedberg Integration Services in the Real World, Brian Garraty What SSIS Buys Me Flexibility Performance Rapid development Dependability Security Integration Services in the Real World, Brian Garraty SSIS Rather Than… xp_cmdshell bcp/BULK INSERT osql/SQLCMD Linked Servers Stand-alone .NET Projects Integration Services in the Real World, Brian Garraty 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 “Since SSIS has been my hammer, A lot of problems have started looking like nails.” – Me Integration Services in the Real World, Brian Garraty Case Study #1 Storing Snapshot of Active Directory in SQL Server Tables Integration Services in the Real World, Brian Garraty 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 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 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 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 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 Case Study #2 Index Defragmentation Integration Services in the Real World, Brian Garraty Index Defrag: Requirements Targeted defragmentation Configurable thresholds Support for 2005 syntax and features Report-only mode Integration Services in the Real World, Brian Garraty Index Defrag: Without SSIS Many choices Powershell Stored procedure SMO Valid options - No problems slide here Integration Services in the Real World, Brian Garraty 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 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 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 Wrap Up Integration Services in the Real World, Brian Garraty Praise for SSIS Parallelism Expressions Configurations .NET Integration Services in the Real World, Brian Garraty 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 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 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 My* Top 10 Tips and Best Practices 1. 2. 3. 4. 5. 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 My* Top 10 Tips and Best Practices 6. 7. 8. 9. 10. 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 Questions and Answers? Integration Services in the Real World, Brian Garraty 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 Thanks! Brian Garraty twitter.com/@NULLgarity NULLgarity.wordpress.com [email protected] Integration Services in the Real World, Brian Garraty