1 / 70

SSIS for the Disinterested

Brian Garraty @ NULLgarity. SSIS for the Disinterested. Who I am. Brian D. Garraty SQL Server DBA, Va Beach Public Schools HRSSUG Leadership Team Background in C++, VB, ASP, C# @ NULLgarity NULLgarity.wordpress.com >10 years experience with SSIS & DTS. Why I’ve Come to You Today.

guye
Download Presentation

SSIS for the Disinterested

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. Brian Garraty @NULLgarity SSIS for the Disinterested

  2. Who I am • Brian D. Garraty • SQL Server DBA, Va Beach Public Schools • HRSSUG Leadership Team • Background in C++, VB, ASP, C# • @NULLgarity • NULLgarity.wordpress.com • >10 years experience with SSIS & DTS

  3. Why I’ve Come to You Today • SSIS is often • Misunderstood • Disliked • Considered difficult to learn • Subject to ridicule

  4. Why I’ve Come to You Today • SSIS is actually • Fairly simple (mostly) • Pretty good at a lot of things • Fun • The right set of tools for the job (sometimes)

  5. Success Requires • A desire to learn new things • The time to learn new things • Willingness to work through frustrations • Willingness to start over • The right project

  6. Who I Come to You As Today • Brian D. Garraty, SSIS Defender • Brian D. Garraty, SSIS Apologist

  7. Who You Are • Type of Work You Do • Experience with SSIS • Rank your love of SSIS on Scale of 1 to 10 • 10 = God, Family, Country, SSIS • 5 = Like it like a friend • 1 = I want to fight it, really

  8. Where we are headed… • Starting from scratch – 100 Level • Control Flow • Data Flow • Configurations • Dynamic SSIS • What you can do with SSIS • What I have done with SSIS

  9. Where we are headed… • Defense Will Rest • If I knew then... • Resources • Questions

  10. Out of Scope • SSIS in SQL Server Denali • How to do this Stuff • Demos

  11. SSIS 101 SQL Server Integration Services

  12. History • Bundled Extract, Transform, Load (ETL) Platform • Rewrite of Data Transformation Services (DTS) • Released with SQL Server 2005

  13. Dev Environment • Developed in Visual Studio (err…”Business Intelligence Development Studio”) • Solutions, Projects, Packages

  14. Run!!!!!!

  15. Solution Explorer

  16. Package Level Connections

  17. Variables

  18. Runtime Environment • Packages are • Deployed as files or to msdb • Executed via dtexec Utility (or wrapper to it) • SSIS Service • Runs on the server • Doesn’t do much

  19. A New Paradigm • Control Flow • Ordered workflow • Isolated tasks • Precedence Constraints • Data flow tasks • Data Flow • Move data from Point A to Point B • Manipulate data along the way

  20. Control Flow Where You Build Your Workflow

  21. Control Flow Picture

  22. Control Flow Toolbox

  23. Sequence Containers • Group related tasks • Can be enabled/disabled • Can serve as source for Precedence Constraints, even when empty

  24. Loop Containers

  25. Precedence Constraints

  26. Execute SQL Tasks

  27. Script Task

  28. Other Control Flow Tasks • Execute Process Task • Send Mail Task • Execute Package Task • Data Flow Task…

  29. Control Flow Picture

  30. Data Flow Where You Manipulate Data

  31. Data Flow Picture

  32. Data Flow Sources

  33. Data Flow Transformations

  34. Data Destinations

  35. Data Flow Picture - Big

  36. Dynamic SSIS What You See Isn’t Always What You Get

  37. What can be dynamic? • Object property values • Variable values

  38. Expressions • Determined Dynamic Values • Expressions evaluate at runtime • Example: Use current date to build unique log file name

  39. Configurations • Instructed Dynamic Values • XML file • Example: Connection Strings

  40. Dynamic SSIS • What you see may not be what you get… • But it is what you asked for!

  41. What You Can Do with SQL Server Integration Services

  42. Disparate Data Sources • Join data from Oracle with data from SQL Server? • No problem. • No linked server. • No OPENROWSET • No xp_cmdshell • No bcp

  43. Discover Workloads • Have two SQL Servers with 1500 databases, give or take, and need to pull data from all? • No problem. • No linked server. • No OPENROWSET • No xp_cmdshell • No bcp

  44. Parallelize Workloads • Multiple lines == Multiple threads • Often equals, at least

  45. Offload Workloads • Pull data off OLTP systems • Crunch it to your heart’s content • Location agnostic • OLTP = Online Transactional Processing

  46. Exploit Your Talents • Seemless integration with your comfort zone • .NET • Stored Procedures • XML

  47. The Random • Can you have it email me? • Can you only email when X and Y but not Z? • This program needs to run when the extract finishes but only if…

  48. What I’ve Done with SQL Server Integration Services

  49. Hosted Data Warehouse Pull • Extract data from numerous sources • Stage to database • Apply transforms and business logic • Write final data to files • Zip files and ftp to host • Track each run

  50. (Oh no!) System Integration • How will the data get from these 100 databases on these two servers to this one? • Can you not send any dups? • Can you email us the dups? • Can you track the dups that have been fixed?

More Related