brian garraty @ nullgarity n.
Skip this Video
Loading SlideShow in 5 Seconds..
SSIS for the Disinterested PowerPoint Presentation
Download Presentation
SSIS for the Disinterested

Loading in 2 Seconds...

play fullscreen
1 / 70

SSIS for the Disinterested - PowerPoint PPT Presentation

  • Uploaded on

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 >10 years experience with SSIS & DTS. Why I’ve Come to You Today.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'SSIS for the Disinterested' - beate

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
who i am
Who I am
  • Brian D. Garraty
  • SQL Server DBA, Va Beach Public Schools
  • HRSSUG Leadership Team
  • Background in C++, VB, ASP, C#
  • @NULLgarity
  • >10 years experience with SSIS & DTS
why i ve come to you today
Why I’ve Come to You Today
  • SSIS is often
    • Misunderstood
    • Disliked
    • Considered difficult to learn
    • Subject to ridicule
why i ve come to you today1
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)
success requires
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
who i come to you as today
Who I Come to You As Today
  • Brian D. Garraty,

SSIS Defender

  • Brian D. Garraty,

SSIS Apologist

who you are
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
where we are headed
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
where we are headed1
Where we are headed…
  • Defense Will Rest
  • If I knew then...
  • Resources
  • Questions
out of scope
Out of Scope
  • SSIS in SQL Server Denali
  • How to do this Stuff
  • Demos
ssis 101
SSIS 101

SQL Server Integration Services

  • Bundled Extract, Transform, Load (ETL) Platform
  • Rewrite of Data Transformation Services (DTS)
  • Released with SQL Server 2005
dev environment
Dev Environment
  • Developed in Visual Studio

(err…”Business Intelligence Development Studio”)

  • Solutions, Projects, Packages
runtime environment
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
a new paradigm
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
control flow
Control Flow

Where You Build Your Workflow

sequence containers
Sequence Containers
  • Group related tasks
  • Can be enabled/disabled
  • Can serve as source for Precedence Constraints, even when empty
other control flow tasks
Other Control Flow Tasks
  • Execute Process Task
  • Send Mail Task
  • Execute Package Task
  • Data Flow Task…
data flow
Data Flow

Where You Manipulate Data

dynamic ssis
Dynamic SSIS

What You See Isn’t Always What You Get

what can be dynamic
What can be dynamic?
  • Object property values
  • Variable values
  • Determined Dynamic Values
  • Expressions evaluate at runtime
  • Example: Use current date to build unique log file name
  • Instructed Dynamic Values
  • XML file
  • Example: Connection Strings
dynamic ssis1
Dynamic SSIS
  • What you see may not be what you get…
  • But it is what you asked for!
what you can do
What You Can Do

with SQL Server Integration Services

disparate data sources
Disparate Data Sources
  • Join data from Oracle with data from SQL Server?
    • No problem.
    • No linked server.
    • No xp_cmdshell
    • No bcp
discover workloads
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 xp_cmdshell
    • No bcp
parallelize workloads
Parallelize Workloads
  • Multiple lines == Multiple threads
  • Often equals, at least
offload workloads
Offload Workloads
  • Pull data off OLTP systems
  • Crunch it to your heart’s content
  • Location agnostic
  • OLTP = Online Transactional Processing
exploit your talents
Exploit Your Talents
  • Seemless integration with your comfort zone
    • .NET
    • Stored Procedures
    • XML
the random
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…
what i ve done
What I’ve Done

with SQL Server Integration Services

hosted data warehouse pull
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
oh no system integration
(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?
active directory extract
Active Directory Extract
  • Extract users, groups, & membership
  • Load data into SQL Server
  • Ensure it always works
the defense rests
The Defense Rests

The Dark Side of SSIS

gui intensive
  • If you aren’t comfortable in a GUI-intensive IDE, SSIS will be a challenge
  • But you do get what you asked for!
expression syntax
Expression Syntax
  • Inconsistent
  • Difficult to Master
  • Flying solo (No Intellisense)
wound tight
Wound Tight
  • SSIS binds itself to your meta data and holds on tight
  • SSIS continually checks that things have not changed
  • Single column change in data source must trickle all the way down your data source
what s the difference
What’s the Difference?
  • Source Control? In my experience you can check things in, label them, and do “gets”.
  • Trying to do a Diff might induce panic
  • If needed, seek a third party
  • The more dynamic you get, the less parallel you tend to get
read only if only
Read only? If only!
  • Just viewing details of SSIS components often leads SSIS to want to make changes to the file
  • Code review often leads to check out the entire project
but it works on my machine
But It Works on My Machine!
  • The server is not your machine
  • Early on, budget some time to work through issues upon deployment
  • Drivers, rights, O/S, platform, etc. may cause problems
  • Be patient!
misc rants
Misc Rants
  • No Undo (yet)
  • Variable Scope is read-only
  • Variable Scope is context-default
  • Audience?
if i knew then
If I Knew Then…

Best Practices and Words of Caution

multiple package projects
Multiple Package Projects
  • Benefits:
    • Encapsulation
    • Readability
    • Collaboration
deploy as files
Deploy as Files
  • Packages in development must be files
  • You will have multiple file projects
  • To minimize changes on deployment, deploy as files
  • More info:
root folder variable
Root Folder Variable
  • String variable to store location of package
  • Use to build relative file paths
    • Input files (xsd, raw, etc)
    • Output files (xml, raw, etc)
    • Connection strings (packages, log files, etc)
  • More info:
indirect configurations
Indirect Configurations
  • Store location of configuration file in environment variable
  • More info:
recreate over edit
Recreate over Edit
  • In response to certain changes, delete
  • Take advantage of ease of creating a new item rather than suffer through unpleasant editing experience
  • Particularly true for file connections
  • The New ETL Paradigm, Jamie Thomson
  • Jamie Thomson’s Blogs
resources con t
Resources (con’t)
  • SSIS Community Tasks and Components
  • Locally grown SSIS Training