book

Microsoft® SQL Server® 2012 Integration Services

by Rakesh Parida Wee-Hyong Tok Matt Masson, Xiaoning Ding, and Kaarthik Sivashanmugam

September 2012

Beginner to intermediate

672 pages

21h 25m

English

Microsoft Press

Read now

Unlock full access

Who Should Read This BookAssumptionsWho Should Not Read This BookOrganization of This BookFinding Your Best Starting Point in This BookConventions and Features in This BookSystem RequirementsCode SamplesInstalling the Code SamplesUsing the Code SamplesAcknowledgmentsErrata & Book SupportWe Want to Hear from YouStay in Touch
Common Usage Scenarios for SSISConsolidation of Data from Heterogeneous Data SourcesData Type SupportMetadata Exposed by ProviderSupport in 32 and 64-Bit EnvironmentsPerformanceMovement of Data Between SystemsLoading a Data WarehouseCleaning, Formatting, or Standardization of DataIdentification, Capture, and Processing of Data ChangesCoordination of Data Maintenance, Processing, or AnalysisEvolution of SSISSetting Up SSISSQL Server Features Needed for Data IntegrationIntegration ServicesSQL Server Data ToolsDatabase Engine ServicesManagement ToolsSQL Server Editions and Integration Services FeaturesSummary
Control FlowTasksPrecedence ConstraintsVariables and ExpressionsContainersForeach Loop ContainerFor Loop ContainerSequence ContainerConnection ManagersPackages and ProjectsParametersLog ProvidersEvent HandlersData FlowSource AdaptersDestination AdaptersTransformsSSIS CatalogOverviewCatalogFoldersEnvironmentsReferencesSummary
What’s New in SSIS 2012Upgrade Considerations and PlanningFeature Changes in SSISSupport for Data Transformation ServicesActiveX Script TaskData ViewersDependencies and ToolsUpgrade RequirementsSoftware RequirementsHardware RequirementsUpgrade DurationUpgrade ScenariosVersion Upgrade PathsUpgrade in Side-by-Side InstallationEdition UpgradeUnsupported Upgrade ScenariosUpgrade ValidationIntegration Services UpgradeUpgrade AdvisorPerforming UpgradeUpgrading Installed Files and ObjectsUpgrading SSIS Projects and PackagesAddressing Upgrade Issues and Manual Upgrade StepsConversion to Projects after UpgradeProject Conversion Wizard Launch OptionsCommand Line, Start Menu, or SQL Server Management StudioSQL Server Data Tools in the Context of an Opened ProjectSummary
The Integration Services DesignerVisual StudioUndo and RedoGetting Started WindowToolboxVariables WindowZoom ControlAutosave and RecoveryStatus IconsAnnotationsConfiguration and DeploymentSolution Explorer ChangesProject ParametersConnection ManagersParameter TabVisual Studio ConfigurationsProject CompilationDeployment WizardProject Conversion WizardImport Project WizardNew Tasks and Data Flow ComponentsChange Data CaptureCDC Control TaskCDC SourceCDC SplitterExpression TaskDQS Cleansing TransformODBC Source and DestinationControl FlowExpression AdornersConnection ManagersExecute SQL TaskData FlowConnection AssistantsImproved Column MappingEditing Components in an Error StateGroupingSimplified Data ViewersRow Count and Pivot Transform User InterfacesFlat File Source ChangesRagged-Right Delimited FilesEmbedded QualifiersScriptingVisual Studio Tools for ApplicationsScript Component Debugging.NET 4 Framework SupportExpressionsRemoval of the Character LimitNew Expression FunctionsLEFT FunctionREPLACENULL FunctionTOKEN FunctionTOKENCOUNT FunctionSummary

Improvements in SQL Server 2012Package Format ChangesVisual Studio ConfigurationsUsing Source Control Management with SSISConnecting to Team Foundation ServerAdding an SSIS Project to Team Foundation ServerChange ManagementChanges to the SSIS Visual Studio Project FileBest PracticesUsing Small, Simple PackagesOne Developer Per PackageConsistent Naming ConventionsSummary
SSIS Project Deployment ModelsPackage Deployment ModelConverting from Package to Project Deployment ModelProject Deployment ModelConverting from Project to Package Deployment ModelDevelop an Integration Services ProjectCreating an SSIS ProjectDesigning an Integration Services Data FlowUsing Parameters and the ForEach ContainerUsing the Execute Package TaskBuilding and Deploying an Integration Services ProjectSummary
Previous Connectivity Options in SSISProviders for Connectivity TechnologyOLE DB, ADO.NET, and ODBCTask and Transform SupportabilityPerformanceDatabase Vendor SupportNew Connectivity Options in SSIS 2012Introducing ODBCODBC and Database VendorsODBC DriversODBC TracingODBC Components for SSISODBC SourceBasic Setup of ODBC SourceBulk Fetch LogicAdvanced ODBC SourceODBC DestinationBasic Setup of ODBC DestinationBulk Insert LogicAdvanced ODBC DestinationConnectivity Considerations for SSIS64-Bit and SSIS64-Bit Processors and WOW64SSIS Tools on 64-Bit ArchitectureSQL Server Data ToolsSSIS Catalog and Package Execution ToolsConnectivity to Other Sources and DestinationsConnecting to Microsoft Excel and AccessJet ProvidersACE ProvidersOther Considerations for ACE and JetConnecting to OracleMicrosoft OLE DB and .NET Providers for OracleOracle OLE DB ProviderODBC ConnectionsAttunity Connectors for OracleCreating Custom ComponentsUsing Script ComponentsSummary
CDC in SQL ServerUsing CDC in SQL ServerCDC Scenarios in ETLsStages in CDCInitial Data LoadingSyncing CDC ChangesCDC in SSIS 2012CDC StateCDC State StringTypes of CDC StatesStoring a CDC StateCDC Control TaskConfiguring a CDC Control TaskCDC Control Task OperationsData Flow Component: CDC SourceConfiguring a CDC Source ComponentNet vs. All ChangesCDC Splitter ComponentTypes of ChangesUsing the Splitter ComponentCDC for OracleIntroductionComponents for Creating CDC for OracleCDC Service Configuration MMCInstallationUsing the CDC Service Configuration MMCWhat Happens When a Service Is Created?Oracle CDC Designer MMCInstallationOracle CDC InstanceUsing the CDC DesignerCreate Oracle CDC Instance WizardMSXDBCDC DatabaseTables in MSXDBCDCStored Procedures in MSXDBCDCOracle CDC Service Executable (xdbcdcsvc.exe)InstallationCommandsConfig commandCreate commandDelete commandData Type HandlingSSIS CDC ComponentsSummary
Data Profiling TaskFuzzy Lookup TransformationFuzzy Grouping TransformationData Quality Services Cleansing TransformSummary
Configuration BasicsHow Configurations Are AppliedProperty PathWhat to ConfigureChanges in SSIS 2012Configuration in SSIS 2012ParametersProject ParametersPackage ParametersCreating Package ParametersCreating Project ParametersMore About Project.paramsAPI for Creating ParametersUsing ParametersParameters in Expressions and Property PathsParameters and Execute Package TasksUse of Project ParametersDifferences between Parameters and VariablesConfiguring Parameters on the SSIS CatalogConfiguring, Validating, and Executing Packages and ProjectsConfiguration Through SSMSConfiguring and Validating PackagesExecuting with Configured ValuesConfiguration Using SQL Agent, DTExec, and T-SQLSQL Agent and ConfigurationDTExec and ConfigurationConfiguring Using T-SQL Stored ProceduresSSIS EnvironmentsRelative and Absolute ReferencesEvaluation Order of ParametersPackage Deployment Model and Backward CompatibilityPackage Deployment ModelLimitations of the Package Deployment ModelExecuting Packages Using the Package Deployment ModelBest Practices for Configuring SSISBest Practices with Package Deployment ModelPackage TemplatesSQL Server ConfigurationsIndirect Configurations Using Environment VariablesOverriding ConfigurationsBest Practices with Project Deployment ModelProject Parameters and Project Connection ManagersSummary
Ways to Run SSIS PackagesPackage LocationsFile SystemMSDB DatabasePackage StoreSSIS CatalogConfiguring PackagesSETConfigFileParameterError DumpsLogging OptionsConsoleLogLoggerVLogRunning Packages in the SSIS CatalogPrepare ExecutionsStarting SSIS Package ExecutionsView ExecutionsExecutions with T-SQLRunning Packages from SQL AgentCreate an SSIS Job StepExecute Packages from the SSIS CatalogRunning Packages via PowerShellCreating and Running SSIS Packages ProgrammaticallySummary
Overview of SSIS Stored Procedures and ViewsIntegration Services CatalogSSIS Catalog PropertiesQuerying the SSIS Catalog PropertiesSetting SSIS Catalog PropertiesSSIS Projects and PackagesDeploy an SSIS Project to the SSIS CatalogLearning About the SSIS Projects Deployed to the SSIS CatalogConfiguring SSIS ProjectsLearning About the Parameters for a ProjectSetting the Value of a ParameterManaging SSIS Projects in the SSIS CatalogDeleting SSIS ProjectsMoving SSIS ProjectsRestoring an SSIS Project to an Earlier VersionRunning SSIS Packages in the SSIS CatalogStarting and Stopping SSIS Package ExecutionProperty OverridesDiscovering Package Execution DetailsSetting the Logging Level for a Package ExecutionAsynchronous vs. Synchronous ExecutionsSSIS EnvironmentsCreating SSIS EnvironmentsCreating SSIS Environment VariablesConfiguring SSIS Projects Using SSIS EnvironmentsCreating Project References to SSIS EnvironmentsConfiguring SSIS Projects Using Reference ValuesPackage Execution Using SSIS EnvironmentsManaging SSIS Environment and Environment VariablesSummary
PowerShell RefresherPowerShell and SQL ServerManaging SSIS with PowerShellSSIS Management Object ModelPowerShell with SSIS Management Object ModelCreating or Dropping an SSIS CatalogCreating and Dropping FoldersDeploying and Managing ProjectsCreate Environments and Environment VariablesCreating Package Executions with Parameters and ReferencesPowerShell and SSIS Using T-SQLAdvantages of Using PowerShell with SSISSummary
Getting Started with SSIS ReportsData PreparationMonitoring SSIS Package ExecutionIntegration Services DashboardAll Executions ReportAll Validations and All Operations ReportsUsing SSIS Reports to Troubleshoot SSIS Package ExecutionUsing the Execution Performance Report to Identify Performance TrendsSummary
The Control Flow EngineOverviewLoadRead XMLDecrypt and Check SignatureCheck Package Format VersionLoad ObjectsApply Configurations and ExpressionsApply ParametersValidateDelaying ValidationConnection Manager ValidationExecuteScheduling AlgorithmExecutable TypesThreadingEventsThe Data Flow EngineOverviewLayout ManagementBuffer ManagerExecution ControlComponent InterfacesOutput TypesExecution PathsExecution PlansExecutionBackpressureBasic BackpressureEnhanced BackpressureEngine TuningBuffer SizeEngineThreadsRunInOptimizedModeSplit Complex ComponentsSummary
SSIS Catalog Deep DiveCreating the SSIS CatalogUnit of Deployment to the SSIS CatalogWhat Is Inside SSISDB?SQL Server Instance Starts UpSSIS Catalog and Logging LevelsUnderstanding the SSIS Package Execution Life CycleStopping SSIS Package ExecutionsUsing the Windows Application Event LogSSIS Catalog Maintenance and SQL Server Agent JobsBackup and Restore of the SSIS CatalogBack Up SSISDBRestore SSISDBSummary
Protect Your PackageControl Package AccessFile SystemMSDB DatabasePackage StorePackage EncryptionSensitive Variables and ParametersPackage SigningSecurity in the SSIS CatalogSecurity OverviewUser and RolesSchemasSecurables and PermissionsManage PermissionsPermission InitializationPermission EnforcementGrant and Revoke PermissionsPermission InheritanceDeny PermissionsDDL TriggerRunning SSIS with SQL AgentRequirementsCreate CredentialsCreate Proxy AccountsCreate SQL Agent JobsSummary
Configure Logging OptionsChoose ContainersSelect EventsAdd Log ProvidersLog ProvidersText FilesSQL ServerSQL Server ProfilerWindows Event LogXML FilesLogging in the SSIS CatalogLogging LevelsEvent LogsEvent Context InformationAdvanced Logging TopicsCustomizing Logging FieldsLogging with dtexec UtilityDeveloping Custom Log ProvidersBuilding Core FunctionalityRegistering the New ProviderSummary
Introduction to SSIS AutomationProgrammatic Generation of SSIS PackagesCreating Packages in BulkApplying Consistent PatternsMetadata-Driven Package ExecutionDynamic Package GenerationHandling Design-Time EventsSamplesCreating, Saving, and Loading a PackageWorking with ProjectsCreating a Data FlowInserting a Task from Another PackageUsing an Execute Package TaskMetadata-Based ExecutionCustom Package RunnerUsing PowerShell with the SSIS Management Object ModelUsing PowerShell with SQL AgentAlternative Solutions and SamplesSamples on CodeplexEzAPIPackage Generation SampleMDDE StudioVulcanThird-Party SolutionsBIDS HelperVarigence MistPragmatic Works BI xPressCozyRocSummary
Getting Started with TroubleshootingData PreparationTroubleshooting Failures of SSIS Package ExecutionsThree Key Steps Toward Troubleshooting Failures of SSIS Package ExecutionsStep S: Finding Out the Status of Package ExecutionStep V: Finding Out the Values Specified for the ParametersStep T: Finding Out the Tasks and Containers That FailedExecution PathFinding the Root Cause of FailureContext of an ErrorTroubleshooting the Execute Package Task and Child Package ExecutionsDiagnosticEx EventsExecute Package Task and Execution PathTroubleshooting SSIS Package Execution Failures Scheduled with SQL AgentUsing CallerInfo to Determine SSIS Package Executions That Are Executed by SQL AgentUsing SQL Agent History Tables to Determine the SSIS Job Steps That FailedSummary
Creating a Performance StrategyOVAL TechniqueMeasuring SSIS PerformanceMeasuring System PerformanceBuffers SpooledOther Useful CountersSSIS Catalog CountersMeasuring Performance of Data Flow TasksInstrumenting the PackageMeasuring Performance of the Source ComponentMeasuring Performance of the Transformation ComponentMeasuring Performance of Destination ComponentsAnalysis of a Data Flow TaskDesigning for PerformanceParallelize Your DesignParallelize Solution ArchitectureParallelizing Using Precedence ConstraintsUsing SQL Server Optimization TechniquesStaging DataReplacing OLE DB Commands with Staging on SQL ServerReplacing Suboptimal Aggregation SSIS OperationsUsing SQL Server PartitioningBulk Loading Your DataBulk Loading Using PartitionsKeeping SSIS Operations in MemoryOptimizing SSIS Lookup CachingLookup and Cache Transforms PrimerCache Mode ComparisonsStoring Cache Misses with Partial Cache ModeSharing a CacheOptimizing SSIS InfrastructureSSIS CatalogChoosing the Right ConnectorsSummary
Performance ProfilingTroubleshooting Performance IssuesData PreparationUnderstanding SSIS Package Execution PerformanceSSIS Package Execution DurationTime Spent at Each Task in the SSIS PackageTime Spent at Each Phase of the Data Flow ComponentElapsed Time for Data Flow Component Phases (Active Time vs. Total Time)Monitoring SSIS Package Execution PerformancePer-Execution Performance CountersInteractive Analysis of Performance DataSummary
Troubleshooting in the Design EnvironmentRow Count ValuesData ViewersData in Error OutputBreakpoints and Debug WindowsTroubleshooting in the Execution EnvironmentExecution Data StatisticsData TapAdd Data Taps by NameAdd Data Taps by GUIDError DumpsSummary

Content preview from Microsoft® SQL Server® 2012 Integration Services

Chapter 4. New SSIS Designer Features

The Integration Services Designer

Visual Studio

The first thing you’ll notice when starting a new SSIS project in SQL Server 2012 is that SQL Server Data Tools (formerly Business Intelligence Development Studio) has been upgraded to Microsoft Visual Studio 2010 SP1. Along with the upgrade comes an updated look and feel (shown in Figure 4-1): You’ll find many small visual improvements, such as an updated color scheme, new icons, and rounded corners on the tasks and transforms.

Figure 4-1. The SQL Server 2012 designer for Integration Services.