SSIS for Absolute Beginners by George SQ UI L LACE SQL is in the name CuriousGeoeSquillacecom August 20 2015 Speaker Background Husband Dad MCT MCSE SQL Server 2012 MCSA MCSE Data Platform ID: 772870
Download Presentation The PPT/PDF document "SSIS for Absolute Beginners" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
SSIS for Absolute Beginners by George SQUILLACE“SQL” is in the name!CuriousGeo@e-Squillace.com August 20, 2015
Speaker Background Husband, Dad MCT MCSE SQL Server 2012 MCSA MCSE: Data PlatformMCSE: Business IntelligenceCertified in every version of SQL Server from 2000 -> currentCertified in every version of Windows Server from NT 4.0 -> Server 2008Almost 30 years in I.T.! For fun: (no particular order)ReadingBicycling (road /city / trail)TravelMusic / Acoustic GuitarComputersWould love to learn how to flyWould love to learn how to surf 22 years of service at New Horizons96 testing eventsMCT since 1997SQLSaturday speakerMicrosoft Virtual Academy presenter
Agenda What is “ETL” ?Why Is ETL Needed?What is “SSIS”?Tour of the Development EnvironmentControl Flow BasicsData Flow BasicsDebugging & Error Handling BasicsDeployment Basics
What is “ETL”,and Why Is It Needed?
What is “ETL”? Extract, Transform, & LoadConnect to data sources for Extraction Connect to data destinations for LoadingOptional: Transform/process data as requiredETL is very frequently required within an organizationOh, and sometimes an E-L-T process is used instead
Why is “ETL” Needed? Import and export dataWorking with trading partnersCombine data from multiple sourcesHomogeneous sources (all SQL Servers)Heterogeneous sourcesMake data available to applications other than the sourceMake data available for peopleCombining data from various sources for analysis Create a data warehouseCleanse dataStandardize dataA DW is a foundation for a variety of analyses, such as….interactive analysis with OLAP cubes, Tabular and other data modelsReportingWhat is “The Big Lie”?
What is SQL Server Integration Services, or“SSIS”?
What is SSIS? ETL Execution PlatformControl Flow EngineData Flow EngineUnit of Execution: “Package”ETL Development EnvironmentExtensibleIncludes:Wizards Import/Export Data WizardPackage Migration WizardPackage Configuration WizardPackage Deployment WizardCommand Line ExecutablesDTExecDTExecUIDTUtil How does SSIS relate to SQL Server?
Why SSIS? There are competitors…CommercialPretty much every database engine platform has a “data integration” featureOracleIBMSAPInformatica PentahoOpen sourceTalendCloverETLSSIS is included in the SQL Server product (depending on the Edition ) Mature feature set (originally introduced in SQL Server 2005)Enterprise class performance“We loaded 1TB in 30 Minutes with SSIS, and So Can You”Huge support communityWebsites and help forumsBlogsBooksBI-specific PASS chaptersSoftware tools
Development Environment
Visual Studio (SSDT for BI) Basics Key Terms:SolutionProjectTemplate
What is the SSISDevelopment Environment? Visual Studio Shell + Business Intelligence Development TemplatesNamed: SQL Server Data Tools for BI (SSDT-BI)Installation method LicensingSQL Server 2012 comes with the Visual Studio 2010 ShellCan upgrade to SSDT for BI – VS 2012 or VS 2013Ironically, there is no SSDT (yet) that ships with SQL Server 2014Can download SSDT for BI - 2013
Tour of the “Dev” Environment
Control Flow Basics
Control Flow Elements TasksContainersPrecedence Constraints
Frequently Used Control Flow Tasks Data Profiling Task“hunt for treasure and landmines”Execute SQL TaskFile System TaskExecute Process TaskSend Mail TaskExecute Package TaskScript TaskData Flow Task The “star of the opera”
Control Flow Containers Why Containers?Containing / organizingExecutable unit within the packageCan be enabled/disabledTwo of the three containers provide LoopingTransaction protection contextThink “all, or nothing”Checkpoint contextRestart pointThree Kinds Sequence ContainerFor Loop ContainerFor Each Loop Container
Precedence Constraints Precedence Constraints define:Workflow orderWorkflow conditionsDownstream Task & Container Execution can be based on:Constraint evaluationSuccessFailureCompletionExpressionExpression and ConstraintExpression or ConstraintMultiple Constraint evaluationLogical ANDLogical OR
Data Flow Basics
Data Flow Basics Various ways to accomplish ETL in SSISBULK INSERT TaskExecute SQL Taskbcp.exeCommand line utility for importing and exporting text filesMost typically used ETL tool in SSIS is the Data Flow TaskThe “DFT” defines a “pipeline” At least one sourceAt least one destinationOptional: one or more transformations
Data Flow Sources Database EnginesOLE-DB SourceADO .NET SourceODBCFile sourcesFlat fileExcel fileRaw fileXML fileOthers (pretty much anything)
Data Flow Destinations Database EnginesOLE-DB destinationADO .NET destinationFile destinationsFlat fileExcel fileRaw fileOther destinationsODBC destinationRecordset destination…
Data Flow Transformations Row transformationsCharacter mapCopy columnDerived columnImport column / Export columnRowset transformationsAggregateSortPivot and UnpivotRow sampling and Precentage samplingSplit & join transformationsConditional SplitLookup Merge Merge Join Multicast Union AllAuditing transformationsAuditRow countBI transformationsSlowly changing dimensionFuzzy groupingFuzzy lookupData mining queryCustom transformationsScript component
Control Flow & Data Flow Demonstration
General Coding & Development Principles “Always accept pain as early in the development cycle as possible.”~ Dave Rodabaugh, BI Developer“If I have to do something once I’m probably going to have to do it again.”Abstraction / Encapsulation“Write once; run many!”Modularity / “Think reusable”
What are values that might be different from Package execution to execution? Server / Instance nameDatabase nameConnection StringTable nameFile System PathFile NameFile ExtensionQuery Parameter Values such as Comparison DatesQuery result set
Q: Where Can Dynamic / Programmatic Actions Be Implemented in SSIS? A: In many places!Control FlowPrecedence ConstraintsExecuteSQL Tasks (Reading and / or Writing Variables and the use of Parameterized Queries)Parameterized queriesStore the whole query string in a variableStore the output of a query for subsequent processingFor Loop ContainerForEach Loop ContainerScript Task code Data Flow Connection Manager ExpressionsDerived Column Transformation ExpressionsConditional Split Transformation ExpressionsScript Component codeVariable & Parameter ValuesPackage ConfigurationsAlmost Any Property of Almost Every Task and Component
Variables – A Building Block of Dynamic Packages What are Variables? / Why have Variables?Two Variable Classifications in SSISSystem VariablesUser VariablesCreation – HowToVariable Scope and “(un)changeability”Available Data Types
Parameters – Another Building Block of Dynamic Packages What are Parameters? / Why have Parameters?Applies only to the Project Deployment Model of developmentTwo Parameter classifications in SSISProjectPackageDetails:Creation – HowToVariable Scope and “(un)changeability” Available Data Types
Expressions – Creating Derived Property Values Expressions can be used to derive almost any property value and make the value dynamicCan be derived from the combination of:Column ValuesSystem & User Variable ValuesOperatorsVariety of f unctionsMathematicalStringNULL functionsDate & TimeType casts
Debugging & Error Handling Basics
Debugging & Error Handling LoggingControl Flow debugging featuresBreakpointsConditionalAlso within Script codeStatus WindowsLocalsWatchEvent Handler“Scoped, Conditional, Control Flow” Data Flow featuresData ViewersIgnoring or redirecting failed rows
Deployment Basics
Deployment: Dev -> Prod Starting with SQL Server 2012 there are now two different deployment modelsProject Deployment ModelRequires a special database created, the “SSIS Catalog”Package Deployment ModelMSDBFile systemPackage configurationsEach deployment model has very different deployment requirements and methods
Using SSIS Programmatically Building Packages ProgrammaticallySamples for creating SSIS packages programmaticallyEzAPI – Alternative package creation APIRunning and Managing Packages Programmatically
Biml(Business Intelligence Markup Language) Stairway to Biml Level 1: What is Biml?Level 2, Biml Basics Level 3, Building an Incremental Load PackageLevel 4, Using Biml as an SSIS Design Patterns EngineLevel 5, Biml Language ElementsBiml is better even for simple packages Generate multiple SSIS packages using BIML and metadata8 Practical BIML TipsBimlScript
Extensibility: Free & Commercial Tools CodeplexSSIS Community Tasks and Components (free & commercial)Toolsi.e., SSIS Expression Editor & TesterConnection ManagersLog ProvidersControl Flow Tasks ForEach EnumeratorsScript Task examplesScript Component examplesData Flow Sources, Transforms, & DestinationsCommercialPragmatic Works – Task Factory Abilineage , SSIS Pipeline components Eldos - BizCrypto, like SFTP functionality for SSISCozyRoc - SSIS+, Control Flow & Data Flow componentsKeelio - Dynamics GP SSIS Toolkit and XML ToolkitKonesans - a number of components, some of which are freeMelissa Data
References & Resources Microsoft course 20463C, available through your local New Horizons Computer Learning CenterImplementing a Data Warehouse with SQL Server (5 days)www.e-Squillace.com (linked to my BI homepage)Books Implementing a Data Warehouse with Microsoft SQL Server 2012 Training Kit (Exam 70-463)SQL Server 2008 Integration Services Problem-Design-Solution (Wrox)Web & other links:Integration Services Expression Reference SSIS Expression Examples Advanced Integration Services Expressions Integration Services Data Types CAST & CONVERT SSIS Data TypesA VB .NET Toolkit for Writing SSIS Script TasksSSIS Expression Cheat Sheet at PragmaticWorksPragmaticWorks free one hour webinars
Thank you!