book

Microsoft® SQL Server™ 2005 Integration Services Step by Step

Name: Microsoft® SQL Server™ 2005 Integration Services Step by Step
Author: Hitachi Consulting Paul Turley Joe Kaspizak, Scott Cameron, Satoshi Iizuka, and Pablo Guzman
ISBN: 9780735624054

by Hitachi Consulting Paul Turley Joe Kaspizak, Scott Cameron, Satoshi Iizuka, and Pablo Guzman

June 2007

Beginner to intermediate

480 pages

12h 30m

English

Microsoft Press

Read now

Unlock full access

Introduction
Finding Your Best Starting PointAbout the Companion CD-ROMSystem RequirementsInstalling and Using the Sample FilesConventions and Features in This Book
I. Getting Started with Integration Services
1. Introduction to SQL Server Integration Services
Common SSIS ApplicationsSSIS Objects and Process Control ComponentsSSIS Process ControlSSIS Control FlowSSIS Data FlowSSIS Data PipelineSSIS Event HandlerSSIS ComponentsSSIS Development StudioSSIS Runtime ServicesSSIS Package DeploymentSQL Server 2000 DTS MigrationChapter 1 Quick Reference
2. Building Your First Package
Exploring Business Intelligence Development StudioSolution ExplorerDocking Utility WindowsExploring an SSIS Project in BIDSOpen SSIS Sample SolutionView the ScrapReason data source view in the Data Source view designerView the CreateLists package in the package designerOpen the Data Flow Task – Employee ListExplore the connection managersOpen the OLE DB Source – Employee Query taskOpen the Derived Column – FullName taskOpen the Sort – Department Shift Employee taskOpen the Flat File Destination – Employees CSV taskView the final outputExecute the CreateLists packageView Employees, ScrappedProducts, and customer records files in the data folderReview the ImportCustomers packageExcute the ImportCustomers packageUsing the SSIS Import and Export WizardCreating Tables in a New DatabaseRun the wizard in BIDSReview the is2005sbs database by using Management StudioCreate the QuickStart solution to contain the QuickStartIS SSIS projectImport tables into a new QuickStartODS database with a new packageReviewing Package ElementsReviewing a Package Created Using the Import and Export WizardReview the preparation SQL taskReview the data flow taskTesting a PackageExecuting the Package in the DesignerExecute the QuickStartIS.dtsx packageStop debugging and close the package designerReview the QuickStartODS database tables by using SSMSChapter 2 Quick Reference
II. Designing Packages
3. Extracting and Loading Data
Connection ManagersConnection Manager TypesCreating a New Integration Services ProjectCreate a new Integration Services projectAdding Connection ManagersAdd an OLE DB connection manager for the is2005sbs databaseAdd an Office Excel connection manager to the Employee.xls fileCreating a Data FlowCreate a data flow taskAdding Data AdaptersAdd an OLE DB source data adapterAdd the localhost.is2005sbs Connection Manager to the OLE DB Source data adapterMap the connection manager to the data adapterAdd an Excel Destination data adapterAdd the Employee connection manager to the Excel Destination data adapterExecuting the PackageExecute the packageUsing Data Sources and Data Source ViewsCreating a Data SourceCreate a data sourceCreating a Data Source ViewCreate a data source viewCreating a New Named QueryCreate a named queryCopying Data from a Named Query to a Flat FileCopy data from a named query products table to a flat fileConnect to a flat file destinationExecuting the PackageStop DebuggingChapter 3 Quick Reference
4. Using Data Flow Transformations
Creating Data Flow in a PackageData Flow SourcesData Flow TransformationsData Flow DestinationsData Source ConnectionsSSIS TransformationsRow TransformationsRowset TransformationsSplit and Join TransformationsData Quality TransformationsData-Mining TransformationsOther TransformationsSynchronous and Asynchronous TransformationsUsing Expressions in PackagesExpression Usage in SSISConditional Split TransformationDerived Column TransformationVariablesPrecedence ConstraintsThe For Loop ContainerExpressions ElementsBuilding ExpressionsUsing Data Flow TransformationsOpening and Exploring the SSIS ProjectOpen and explore the SSIS projectPreview the NewProducts.txt fileCreating the Data Flow TaskCreate the Data Flow taskUsing a Flat File SourceConfiguring a Flat File SourceCreate a data adapterAdding a Connection ManagerAdd a connection managerAdding a Conditional Split TransformationAdd a Conditional Split transformationAdding a Derived Column TransformationAdd a Derived Column transformationViewing the Properties of the Derived Column TransformationView the properties of the Derived Column transformationAdding a Flat File Destination Data Adapter and Executing the PackageAdd a flat-file destination data adapterExecute the packageSending Output to Different DestinationsAdd a Multicast transformationAdd a SQL Server destination data adapterCreate the task that deletes data from the tableExecute the package and check the resultsConfiguring Error OutputTypes of ErrorsError OptionsExploring the LookupGeography PackageOpen and explore the packageCreating a TaskCreate the Data Flow Task – Lookup Geography taskCreating and Naming a Flat File SourceCreate a flat fileAdding a Data Conversion TransformationConvert the GeographyKey column to an integerAdding a Lookup TransformationAdd a Lookup transformationAdding a Flat File Destination for Lookup ErrorsWrite lookup errors to a fileAdding a Flat File Destination for Successful LookupsWrite successful rows to another flat fileExecuting the Package and Checking the ResultsExecute the package and check the resultsChapter 4 Quick Reference
5. Managing Control Flow
Control Flow ElementsControl Flow ComponentsWork with tasks and DelayValidationUsing ContainersAdd sequence containersTest whether the output file is foundAssign a value to the variableCreate a placeholderAdding a Fuzzy Lookup TransformationAdd a Fuzzy Lookup transformationAdd a SQL Server destinationAdd an Execute SQL taskAdding a Foreach Loop ContainerAdd a Foreach Loop container to Sequence Container – File ExistsApplying Precedence ConstraintsApply precedence constraintsChapter 5 Quick Reference
6. Scripting Tasks
Understanding Scripting TasksImplementing a Script TaskCreating a New Script Task and Initiating CodeCreate a new project and add a Script taskHandling ErrorsAdd error-handling code in the Script TaskProviding a Message to the Progress TabModify the script to fire an eventProviding Verbose Information to the Log FileConfigure package log settingsUsing VariablesConfigure variable settingsModify the script to read variablesModifying a Variable at Run TimeAdd a new Script task and update variables at run timeUnderstanding the Script ComponentImplementing the Script ComponentReviewing a Sample ProjectImplement validation using the Transformation script componentUnderstanding an ActiveX Script TaskImplementing an ActiveX Script TaskCreate a new package with a single ActiveX Script taskChapter 6 Quick Reference
7. Debugging Packages
Debugging Control FlowUnderstanding BreakpointsSet breakpoints and suspend package executionReviewing Debug WindowsCall StackBreakpointsExecute package and review debug windowsUnderstanding Progress MessagesExecuting a Package PartiallyDisable a task and execute a package partiallyDebugging Data FlowBrowsing Data By Using Data ViewersSet data viewers and browse dataUnderstanding Other OptionsDebugging Script TaskWalk Through Code by Using BreakpointsSet breakpoints and walk through codeReviewing State by Using VSA FeaturesReview state, using debug windowsChapter 7 Quick Reference

8. Managing Package Execution
Understanding Package ConfigurationsConfiguration BenefitsConfiguration TypesUnderstanding the XML Configuration FileSpecifying a New XML Configuration File LocationCreating and Editing an XML Configuration FileOpening the SSIS Project and Executing the PackageCreate a new SSIS projectCreating an XML Configuration FileCreate an XML configuration fileEditing the XML Configuration FileEdit the XML configuration fileTesting the Package with the New ConfigurationTest the packageMultiple Configuration FilesEnvironment VariableRegistry EntryParent Package VariablesSQL Server TablesDirect and Indirect ConfigurationsUsing Configuration FilesDetermining Configuration OrderEvaluating Configuration FailureUsing Multiple ConfigurationsCreating Multiple Configuration FilesCreating the Database and the OLE DB Connection ManagerCreate a new databaseCreating the Environment VariableCreate a database environmentCreating the Environment Variable ConfigurationConfigure the environment variableCreating the SQL Server ConfigurationCreate a SQL Server tableTesting the Package with the New ConfigurationTest the new configurationExploring the Parent PackageView the ParentPackage.dtsx packageCreating the Parent Package Variable ConfigurationCreate the ParentProductsDestination parent package variable configurationTest the package with the new configurationExploring Package Execution OptionsUsing the SQL Server Import and Export Wizard to Execute PackagesStart the SQL Server Wizard from BIDSStart the SQL Server Wizard from Management StudioUsing DTExecUI to Execute PackagesRun a package by using the DTExecUI utilityUsing DTExec to Execute PackagesRun a package by using the DTExec utilityUsing SQL Server Management Studio to Execute a PackageRun a package by using SQL Server Management StudioExtending Package Execution OptionsExecute Package tasksExecute Process tasksUsing SQL Server AgentExecute tasks and containers and then disable the task and execute the packageUsing the Execute Package UtilityUse the Execute Package UtilityUse the command-line utilityExecuting a package by using SQL AgentUnderstanding Package LoggingImplementing Package LoggingConfiguring Package LoggingConfigure package loggingExecuting the Package and Viewing the LogsExecute the package and view the logsConfigure container and task loggingExecute the package and view the logsChapter 8 Quick Reference
III. Managing Packages
9. Detecting and Handling Processing Errors
Basic Error Detection and HandlingUnderstanding Metadata LineageUnderstanding ValidationUnderstanding Precedence ConstraintsUnderstanding Data Flow TransformationsConfiguring a Transformation to Fail When an Error OccursConfiguring a Transformation to Re-route Error-Causing RecordsConfiguring a Data Flow Transformation to Ignore ErrorsUnderstanding Event HandlersUsing Event Handlers to Perform TasksTriggering an Event HandlerUsing the Event Handlers Provided by SSISCreating Event HandlersDetermine whether the QuickStartODS database is on your computerCreate the QuickStartODS databaseAccessing the SSIS Design EnvironmentOpen the NewProducts.dtsx packageCreating an OnPreExecute Event HandlerCreate an OnPreExecute event handlerAdding a Task to an Event HandlerAdd an Execute SQL task to the OnPreExecute event handlerConfiguring the TaskConfigure the Execute SQL taskMapping SSIS Variables to SQL Statement ParametersConfigure the Execute SQL task to run parameterized SQL statementsCreating a Log Finish Event HandlerAdd a Log Finish Execute SQL task to an OnPostExecute event handlerCreating a Log Error Event HandlerAdd a Log Error Execute SQL task to an OnError event handlerExecuting the PackageExecute the NewProducts.dtsx package and view the results in Management StudioTesting the Package with Invalid DataTest the NewProducts.dtsx package with invalid dataCreating an Event Handler to Fix the ProblemCreate an OnTaskFailed event handler for executable lookup namesCreating a Task to Move the File with Invalid DataCreate a File System task and a new connection managerSetting Connection Manager SettingsSet connection manager settingsPreventing Events from Escalating to Containers and PackagesPrevent the OnTaskFailed event from escalating from the Lookup Names task to the Foreach Loop containerChanging Error Count PropertiesChange the MaximumErrorCount property from 1 to 100Executing the PackageExecute the NewProducts.dtsx packageMaintaining Data Consistency with TransactionsConfiguring TransactionsUsing Checkpoint RestartsUnderstanding the Benefits of CheckpointsConfiguring Packages for CheckpointsUsing Checkpoints and TransactionsPreparing to Use Checkpoints and Transactions to Fix the ErrorOpen the PrepLoadDimProd.dtsx package and view the tables in Management StudioBecoming Familiar with the LoadDimProd PackageBecome familiar with the packageFixing the ErrorFix the errorImplementing CheckpointsImplement a checkpoint file to restart the package at the point of errorChapter 9 Quick Reference
10. Securing and Deploying SSIS Packages
Creating a Deployment UtilityUsing the Package Installation WizardEnable the deployment utilityBuild the SSIS Sample ProjectConnect to Integration ServicesSecuring a PackagePackage EncryptionPassword ProtectionProtectionLevel PropertyRole-Based SecurityApplying SecurityChange the protection level of a package to EncryptAllWithPasswordAssign reader and writer roles to a packageDeployment OptionsPush DeploymentPull DeploymentManaging Packages on the SSIS ServerImport the ImportCustomers package to the file systemImport to MSDBExport a deployed packageExecute the file system packageMonitor running packagesCreating and Applying a ConfigurationAdding a Configuration to the ProjectUse the SSIS BIDS menu to add a configurationExecuting a Deployed PackageMonitoring Package Execution and Event LogsApplying a ConfigurationDelete the existing package from SSMSAdd a configuration to the ImportCustomers.dtsx packageDeploy the packageStart the Package Installation WizardInspect the alternate configuration filesRun the deployed packageExecute the package by using the configuration filesExecute the packageChapter 10 Quick Reference
11. Optimizing SSIS Packages
SSIS Engine OverviewRuntime EngineData Pipeline EngineMemory Buffer ArchitectureBuffer UsageExecution TreesSynchronous and Asynchronous ProcessingData BlockingBlocking TransformationsPartially Blocking TransformationsRow TransformationsNon-blocking TransformationsSourcesBuffer SettingsManaging ParallelismData Source TuningPerformance ManagementLoopsFlat File SourcesFilters and VariablesData Destination ManagementLocking ModesDrop or Disable IndexesUse Explicit TransactionsPerformance-Tuning ExercisesCreate a destination tableWorking with Buffer PropertiesTest execution performanceWorking with a SQL Server DestinationUse a SQL Server connection managerTest DefaultBufferMaxRows and DefaultBufferSizeDesign ConsiderationsTest a package with blocking transformationsPerformance ManagementExecution TreesExecution PlansIterative Design OptimizationLogging an Execution PlanView logging options and add an event handlerSSIS Log ReportsChapter 11 Quick Reference
IV. Applying SSIS to Data Warehousing
12. Data Warehouse Concepts
Data Warehouse ObjectivesData Warehouse CharacteristicsProviding Data for Business Analysis ProcessesIntegrating Data from Heterogeneous Source SystemsCombining Validated Source DataOrganizing Data into Nonvolatile, Subject-Specific GroupsStoring Data in Structures Optimized for Extraction and QueriesData Warehouse FundamentalsBusiness Intelligence Solution GoalsCombining Relevant Data from Multiple SourcesProviding Fast and Easy AccessFocus on DecisionsData GranularitySupporting Business DecisionsUpdate Frequency and PersistenceHistorical DataChanging DimensionsSurrogate KeysAdditive MeasuresReviewing an Operational and Database SchemaCreating a Database DiagramCreate a new diagram for the is2005sbs databaseOrganize the diagramSave the diagramQuery the is2005sbs databaseData Warehouse System ComponentsFact and Dimension TablesDimension Table CharacteristicsStar Schema DimensionsSnowflake Schema DimensionsReviewing and Comparing a Data Warehouse Database SchemaCreating a Database DiagramAdd relationships to the databaseCreate a new diagram for the is2005sbsDW databaseSave the diagramQuery the is2005sbsDW databaseReset the relationships for is2005sbsDWData Warehouse in SummaryReset the Relationships for is2005sbsDWChapter 12 Quick Reference
13. Populating Data Warehouse Structures
Data Warehouse CharacteristicsImplementing Staging TablesTypes of Staging SchemesStaging Data from Multiple SourcesStaggered StagingPersisted StagingAccumulated StagingChunked Accumulated StagingOther Destination ConsiderationsManaging Dimension Tables Part 1Loading Dimension Tables by Using a Left Outer JoinCreate a new project for dimension table load packagesAdd tables to find new dimension membersConfigure a Left Outer Join Merge join taskAdd a conditional split to find new customersAdd a flat file destinationManaging Dimension Tables Part 2Loading Dimension Tables Part 2Add a new packageAdd a Lookup task processAdd a flat file destinationSlowly Changing DimensionsManaging Slowly Changing DimensionsAdd a new package for designing a slowly changing dimension processAdd a Percentage Sampling transformationConnect to SSMS to create a simple database for a new dimension tableCreate a new table within a SQL Server destinationInitialize the CurrentRecord column for Slowly Changing Dimension referenceAdd a new control flow for customer dimension updatesAdd a Slowly Changing Dimension transformationManaging Fact TablesAggregating Data in Fact TablesLoading Fact TablesAdd a new package for fact table load processingAdd a Multicast task and an Aggregate taskAdd a Derived Column taskAdd a Data Conversion taskAdd a new data flow to load the fact table from the staging tableAdd a variable and a Row Count taskAdd an OLE DB destination and configure Error OutputChapter 13 Quick Reference
14. SSIS General Principles
Designing SSIS PackagesOVAL Principles of SSIS Package DesignUsing SSIS Components in Your DesignUsing VariablesUsing the Lookup Task versus Merge JoinUsing Database SnapshotsDesigning for Performance and MaintenanceUsing Fast ParseDefining Your Best PracticesCreate a Master–Child PackageOpen project and build packagesAdd Execute Package tasksAdd Data Flow tasks to the child packagesAdd a Row Count task and variableDisable an Execute Package taskOrganizing Package ComponentsUsing Prefixes to Identify SSIS Package ComponentsDefining Project FoldersManaging Performance and DebuggingManaging Buffers and MemoryManaging CPU UseManaging SSIS Application DeploymentDesigning for DeploymentUsing SSIS Package ConfigurationsManaging Multiple Schemas with SSIS Package DesignLogging ReportsChapter 14 Quick Reference
A. About the Authors
Paul TurleyJoe KasprzakScott CameronSatoshi IizukaPablo GuzmanSupporting Author: Anne Bockman Hansen
B. For Web Developers
Also See
C. Additional SQL Server Resources for Developers
Index
About the Authors
Copyright

Overview

Teach yourself the fundamentals of SQL Server Integration Services—one step at a time. This practical, learn-by-doing tutorial delivers the guidance you need to transform and consolidate data—and build solutions that support your business intelligence needs.

Discover how to:

Design and execute packages that transform data between files and relational databases

Configure connection managers to access other data sources

Create data flows that alter, split, match, and merge data

Develop event-handlers and monitor package performance

Encrypt sensitive information and control access to packages

Customize script code to automate Control Flow and Data Flow tasks

Debug, troubleshoot, and optimize packages

CD features:

All practice exercises

Sample databases

Fully searchable eBook

For customers who purchase an ebook version of this title, instructions for downloading the CD files can be found in the ebook.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Expert SQL Server™ 2005 Integration Services

Publisher Resources

ISBN: 9780735624054Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills