For more information see www.bbd.co.za and www.drp.co.za

SSIS

Po

we

red

by A

TC

www.bbd.co.za

Microsoft’s ETL solution bundled with SQL ServerE – ExtractT – TransformL – Load

What is it?

Source DestinationRead WriteSSIS Source

Po

we

red

by A

TC

www.bbd.co.za

DEMO

A simple ETL

Po

we

red

by A

TC

www.bbd.co.za

When to use Script

Can one or more componentsdo the same thing?

Use those Unique situation

Build custom componentUse script

YES NO

NOYES

Po

we

red

by A

TC

www.bbd.co.za

Moving Data Around

Users and Resources

Identity Lifecycle

Systems Integration

SOA ESB

Data Consolidation and Data Warehousing

ETL EII

Data Management

Data Cleansing

Data Validation

Confusion

Po

we

red

by A

TC

www.bbd.co.za

Performance• Consider SQL or BCP for simple imports

• File system performance

Data Latency• SSIS is not a near real time solution

SOA, ESB, B2B Integration• No business rules support

• Very basic queue support

• XML support limited

Reasons not to consider SSIS

Po

we

red

by A

TC

www.bbd.co.za

Merging Data from Heterogeneous Data StoresPopulating Data WarehousesCleaning and Standardizing DataBuilding Business Intelligence into a Data

Transformation ProcessAutomating Administrative Functions and Data

Loading

Areas SSIS is strong

Po

we

red

by A

TC

www.bbd.co.za

Command line tools (dtexec, dtutil) cannot co-exist with 32bit versions.

No DTS support.Limitations on data providers – No Access, Excel or

SQL Compact IA64 has more limitations including no designer

support

X64 Limitations

Po

we

red

by A

TC

www.bbd.co.za

DEMO

Running 32bit on 64bit

Po

we

red

by A

TC

www.bbd.co.za

RecordSet Destination• SLOW (5 times than a raw file!)

Memory• SSIS is an in memory process.

SELECT *• Exceptionally bad in SSIS

Use many small packages Comments!!! Understand the components

• Many do the same things in different ways with different trade offs• Lookup vs. Merge Join or Execute SQL vs. Execute T-SQL

• Understand which components run asynchronously and which run synchronously

What to watch out for

Po

we

red

by A

TC

www.bbd.co.za

Parallelism Example I

Po

we

red

by A

TC

www.bbd.co.za

Parallelism Example II

Po

we

red

by A

TC

www.bbd.co.za

Parallelism Example III

Po

we

red

by A

TC

www.bbd.co.za

Source ControlHighest EncryptionEasy loading into

designer

Access by multiple users

DB Roles, DTS RolesSQL BackupsAble to filter packages

Files Server

Storage Options

Details http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/05/05/13523.aspx

Po

we

red

by A

TC

www.bbd.co.za

DEMO

Scheduling a package

Po

we

red

by A

TC

www.bbd.co.za

No more often than 3x avg. execution time.Settings in configuration files.Enable logging (step) and notifications (job).Execute signed packages only.Do not make packages which execute themselves.

Scheduling Guidelines

Po

we

red

by A

TC

www.bbd.co.za

DTS Upgrade Options Run DTS in 2005 or 2008

Missing the package logsRuns under 32 bit

Upgrade using MS WizardNot compatible with most package

Upgrade using DTS xChangeMinutes per package

Starting from scratchAbout 3-6 hrs per package conservatively

Po

we

red

by A

TC

www.bbd.co.za

Package Upgrade Wizard Built into SQL Server 2008 Pros:

FreeWorks on simple packages

Cons:Does not handle ODBCOnly handles a few types of text file use casesNo Dynamic Properties TaskNo UDL or legacy database support in data pumpPackages only have about a 20% chance of working

Po

we

red

by A

TC

www.bbd.co.za

Profiles DTS packages to help with a conversion project plan

Rapidly converts DTS Packages to SSIS (2005 or 2008) and applies SSIS best practices

Converts tasks that are not handled by the existing SQL Server conversion wizard

Includes a SSIS logging repository and reports for trending and alerting

Includes BI xPress for new SSIS packages

DTS xChange Upgrade

Po

we

red

by A

TC

www.bbd.co.za

ActiveX Script UpgradeBoth tools mentioned migrate DTS ActiveX to ActiveX

in SSISActiveX migrates to SSIS but you would not want to

keep it there and it may not runNeed for ActiveX Script Task has been replaced with

built-in, easy to maintain SSIS tasksFile System Object = File System TaskMail objects = Send Mail Task (now has SMTP)ADO objects = Execute SQL Task

Po

we

red

by A

TC

www.bbd.co.za

Q&A