Transcript Brian Garraty @NULLgarity        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.

Brian Garraty
@NULLgarity







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

SSIS is often
 Misunderstood
 Disliked
 Considered difficult to learn
 Subject to ridicule

SSIS is actually
 Fairly simple (mostly)
 Pretty good at a lot of things
 Fun
 The right set of tools for the job (sometimes)





A desire to learn new things
The time to learn new things
Willingness to work through frustrations
Willingness to start over
The right project
Brian D. Garraty,
SSIS Defender
 Brian D. Garraty,
SSIS Apologist



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

Starting from scratch – 100 Level
 Control Flow
 Data Flow
 Configurations
 Dynamic SSIS


What you can do with SSIS
What I have done with SSIS




Defense Will Rest
If I knew then...
Resources
Questions



SSIS in SQL Server Denali
How to do this Stuff
Demos
SQL Server Integration Services



Bundled Extract, Transform, Load (ETL)
Platform
Rewrite of Data Transformation Services
(DTS)
Released with SQL Server 2005


Developed in Visual Studio
(err…”Business Intelligence Development
Studio”)
Solutions, Projects, Packages

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

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
Where You Build Your Workflow



Group related tasks
Can be enabled/disabled
Can serve as source for Precedence
Constraints, even when empty

Execute Process Task
Send Mail Task
Execute Package Task

Data Flow Task…


Where You Manipulate Data
What You See Isn’t Always What You Get


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

What you see may not be what you get…

But it is what you asked for!
with SQL Server Integration Services

Join data from Oracle with data from SQL
Server?
 No problem.
 No linked server.
 No OPENROWSET
 No xp_cmdshell
 No bcp

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


Multiple lines == Multiple threads
Often equals, at least

Pull data off OLTP systems
Crunch it to your heart’s content
Location agnostic

OLTP = Online Transactional Processing



Seemless integration with your comfort zone
 .NET
 Stored Procedures
 XML



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…
with SQL Server Integration Services






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




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?



Extract users, groups, & membership
Load data into SQL Server
Ensure it always works
The Dark Side of SSIS

If you aren’t comfortable in a GUI-intensive
IDE, SSIS will be a challenge


But you do get what you asked for!
WYSIWYAF



Inconsistent
Difficult to Master
Flying solo (No Intellisense)



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



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


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




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!

No Undo (yet)
Variable Scope is read-only
Variable Scope is context-default

Audience?


Best Practices and Words of Caution

Benefits:
 Encapsulation
 Readability
 Collaboration




Packages in development must be files
You will have multiple file projects
To minimize changes on deployment, deploy
as files
More info: bit.ly/lSniJS


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: bit.ly/iuLctD


Store location of configuration file in
environment variable
More info: bit.ly/ksAQOG



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
 bit.ly/e17DUR

Jamie Thomson’s Blogs
 sqlblog.com/blogs/jamie_thomson
 consultingblogs.emc.com/jamiethomson

SSIS Community Tasks and Components
 ssisctc.codeplex.com

Locally grown SSIS Training
 andyleonard.net