List of Figures


Chapter 1: Getting Started

Figure 1-1: SSIS high-performance data pipeline
Figure 1-2: Star schema
Figure 1-3: The ETL system
Figure 1-4: The Data Flow Task
Figure 1-5: Configuring the source
Figure 1-6: Set Query Parameters window
Figure 1-7: Deleting arrows
Figure 1-8: Derived Column Transformation Editor
Figure 1-9: Flat File Connection Manager Editor
Figure 1-10: Changing the Column delimiter
Figure 1-11: Current control flow
Figure 1-12: Success value and expression
Figure 1-13: Solid line changing to dotted line
Figure 1-14: Viewing how many rows transformed through the pipeline
Figure 1-15: Execution of the dtexec.exe utility
Figure 1-16: Sequence container grouping tasks together into a box

Chapter 2: Extending Scripts in SSIS

Figure 2-1: Starting a new project
Figure 2-2: Four default columns showing in the Variables window
Figure 2-3: Script Task Editor
Figure 2-4: Viewing the code in Microsoft Visual Studio for Applications
Figure 2-5: Creating a Visual Basic .NET class library
Figure 2-6: Using the command prompt for the SN utility
Figure 2-7: Using the Signing tab in the properties window
Figure 2-8: Using Visual Studio 2005 to create a strong-name key
Figure 2-9: Launching gacutil.exe from the command prompt
Figure 2-10: Adding a reference
Figure 2-11: Testing the assembly as a Windows project
Figure 2-12: Viewing the error using the Progress tab during execution
Figure 2-13: Creating a new connection manager that points to the AdventureWorks database
Figure 2-14: Creating the Key and IV variables
Figure 2-15: Output columns in the Script Transformation Editor
Figure 2-16: Adding a Flat File Destination component
Figure 2-17: Viewing all EncAddressLine1 values encrypted
Figure 2-18: Input Columns
Figure 2-19: Output fields
Figure 2-20: Creation of two conditions
Figure 2-21: Component wired to handle both conditions
Figure 2-22: BadData.txt file

Chapter 3: Data Extraction

Figure 3-1: Extraction process
Figure 3-2: Creating a new connection
Figure 3-3: Data flow source adapters
Figure 3-4: General tab of the flat file editor
Figure 3-5: FastParse property
Figure 3-6: Main editor of the Data Reader adapter
Figure 3-7: New Excel connection
Figure 3-8: Initial data flow
Figure 3-9: Data Convert transformation changing several of the data types
Figure 3-10: Control flow with a ForEach Loop Container
Figure 3-11: The Expressions property when selected on the Excel Connection Manager connection
Figure 3-12: Selecting the expression @[User::FileName]
Figure 3-13: Data flow to stage data
Figure 3-14: Staging and the transformation logic within the same data flow
Figure 3-15: OLE DB Source Editor with a parameterized query
Figure 3-16: Mapping a vdtLastModifiedDatetime package variable to the first parameter
Figure 3-17: Executed data flow
Figure 3-18: Defining the OLE DB source adapter
Figure 3-19: Editor of an Execute SQL Task
Figure 3-20: LastModifiedDatetime column mapped to the vdtLastModifiedDatetime variable
Figure 3-21: EvaluateAsExpression property viewable in the Properties window
Figure 3-22: Editor with the expression that builds the SQL statement
Figure 3-23: Handling the operation in the data flow
Figure 3-24: Aggregate editor
Figure 3-25: Data flow results
Figure 3-26: Data flow results after immediately running again
Figure 3-27: Completed data flow for the first time the process is run
Figure 3-28: No rows are extracted when the package is run a second time
Figure 3-29: Output of running the package again
Figure 3-30: Modified data flow
Figure 3-31: Conditional Split editor
Figure 3-32: Beginning the data flow that performs the data association and comparison
Figure 3-33: Merge Join editor
Figure 3-34: Conditional Split
Figure 3-35: Extraction data flow with Script component added after the extraction
Figure 3-36: Inputs and Outputs properties page

Chapter 4: Dimension ETL with SSIS

Figure 4-1: Hierarchy within a geography dimension
Figure 4-2: Fact table with its related dimension
Figure 4-3: Comparison of table structures of the product dimension destination
Figure 4-4: Data preparation steps useful in the processing of the product source data
Figure 4-5: Data Conversion transformation
Figure 4-6: Derived Column transformation
Figure 4-7: Columns tab of the Lookup editor
Figure 4-8: Record from a source data set that matches a record in a dimension table
Figure 4-9: Updated dimension table record with the new value
Figure 4-10: Source record for the product dimension, and the matching record in the product dimension table
Figure 4-11: Dimension table with the changes applied
Figure 4-12: Transactional source for a sales type fact table (left) and rows from a related dimension table (right)
Figure 4-13: Resulting dimension table after the load
Figure 4-14: Row in the data source for a dimension (left) and rows from the dimension table
Figure 4-15: Updated dimension table
Figure 4-16: Data flow with the SCD transformation connected to the output of the Union All
Figure 4-17: Mapping between source rows generated for the product dimension and the dimension columns
Figure 4-18: Matching of the dimension changes
Figure 4-19: Fixed and Changing Attribute Options screen
Figure 4-20: Historical Attribute Options screen
Figure 4-21: Options available for inferred members
Figure 4-22: End result of the SCD Wizard
Figure 4-23: Column Mappings tab
Figure 4-24: Derived Column editor
Figure 4-25: Details of the Derived Column transformation
Figure 4-26: Input rows not sent out the outputs
Figure 4-27: SCD Transformation Properties window
Figure 4-28: Row count transformation used to capture the number of unchanged rows
Figure 4-29: OLE DB Destination to a staging table
Figure 4-30: Control flow of the product dimension package
Figure 4-31: Product category data flow
Figure 4-32: Subcategory lookup to pull the surrogate key of the category
Figure 4-33: Subset of data and columns within the Employee dimension table
Figure 4-34: Building the hierarchy for the parent-child relationship
Figure 4-35: Data flow used to process the Employee dimension table
Figure 4-36: Columns tab of the Lookup editor
Figure 4-37: Control flow of a package with an Execute SQL Task followed by a data flow
Figure 4-38: Precedence Constraint Editor
Figure 4-39: Five components of the data flow
Figure 4-40: Derived Column editor
Figure 4-41: Data flow for the customer dimension
Figure 4-42: IsSorted property is set to True on the OLE DB Source Output of the dimension table source
Figure 4-43: Editor window of the Merge Join transformation
Figure 4-44: Conditional Split transformation editor
Figure 4-45: OLE DB Destination adapter editor
Figure 4-46: Control flow of the Customer package

Chapter 5: Fact Table ETL

Figure 5-1: Internet sales fact table
Figure 5-2: Primary sales fact tables within the AdventureWorksDW database
Figure 5-3: Schemas of the finance fact table and the currency rate fact table
Figure 5-4: AdventureWorks transactional tables (left) and table structures involved in the dimensional model (right)
Figure 5-5: Currency rate fact table package
Figure 5-6: Lookup transformation editor
Figure 5-7: Columns tab of the editor
Figure 5-8: Updated data flow with a second Lookup transformation
Figure 5-9: Sales fact table load package
Figure 5-10: Mid-stream picture of the data flow execution
Figure 5-11: Lookup transformation editor for the product dimension lookup
Figure 5-12: Data flow for the sales quota fact table
Figure 5-13: Lookup editor on the Columns tab
Figure 5-14: Executed package with the missing Employee matches sent to a Row Count transformation
Figure 5-15: Options available on the Advanced tab of the Fuzzy Lookup transformation
Figure 5-16: Completed data flow for the dimension lookups and the execution results
Figure 5-17: Updated data flow with a Derived Column transformation
Figure 5-18: Derived Column editor containing the calculation logic
Figure 5-19: Sales quota fact data flow
Figure 5-20: Columns tab of the fact table Lookup transformation
Figure 5-21: Configure Error Output editor of the Lookup transformation
Figure 5-22: Conditional Split editor with three outputs defined
Figure 5-23: Sales quota fact with a Merge Join
Figure 5-24: Merge Join editor configured as a Full outer join type
Figure 5-25: Conditional Split editor
Figure 5-26: Column Mappings tab of the OLE DB Command editor
Figure 5-27: Completed control flow tasks
Figure 5-28: Completed data flow for the sales load package
Figure 5-29: Conditional Split editor containing the condition OnlineOrderFlag == TRUE
Figure 5-32: Merge Join editor
Figure 5-30: OLE DB Source adapter editor
Figure 5-31: Same extraction package with the sales header and sales detail tables separated into different source adapters
Figure 5-33: Three identical files brought together with a Union All transformation
Figure 5-34: Sales fact data flow with inferred member handling of the product dimension
Figure 5-35: Lookup configured with a disabled cache
Figure 5-36: Lookup editor Columns tab with the returned surrogate key added
Figure 5-37: Union All editor
Figure 5-38: Sales fact load data flow focused on the product dimension Lookup
Figure 5-39: Conditional Split editor
Figure 5-40: Advanced tab of the Lookup transformation
Figure 5-41: Mapped parameters

Chapter 6: Processing Analysis Services Objects with SSIS

Figure 6-1: Employee dimension in the AdventureWorks example SSAS solution
Figure 6-2: Cube designer in BIDS
Figure 6-3: Targeted Mailing mining structure in SSAS
Figure 6-4: Standard Connection Manager editor
Figure 6-5: Analysis Services Processing Task Editor
Figure 6-6: Adding objects to the Object list
Figure 6-7: Processing options for the Product dimension
Figure 6-8: The Dimension key errors tab
Figure 6-9: Analysis Services Execute DDL Task Editor
Figure 6-10: Data flow with source rows from a flat file
Figure 6-11: Connection Manager property page in Dimension Processing destination
Figure 6-12: Mappings page of the Geography Dimension Processing destination
Figure 6-13: Connection Manager tab of the destination
Figure 6-14: Package executing in the designer with the command window open
Figure 6-15: Execute Process showing ASCMD editor settings
Figure 6-16: Host package
Figure 6-17: Execute Process Task on the Expressions property page
Figure 6-18: Control flow of embedding the XMLA inside a variable
Figure 6-19: Control flow with the single Script Task
Figure 6-20: Control flow containing four tasks
Figure 6-21: Mappings tab of the Partition destination
Figure 6-22: Dimension designer in SSAS

Chapter 7: Package Reliability

Figure 7-1: Execute SQL Task creating the snapshot appears first in the control flow
Figure 7-2: Changing the Multiple constraint option to Logical OR
Figure 7-3: Final solution
Figure 7-4: Checking the provider and all the containers in tasks on the left Containers tree
Figure 7-5: Data Flow Task in the Logging tree on the left
Figure 7-6: Sample report
Figure 7-7: Final configuration
Figure 7-8: Successful creation and execution of the package
Figure 7-9: Creating an expression
Figure 7-10: First task that successfully executed the first time being skipped over
Figure 7-11: Result of package execution
Figure 7-12: Sequence Containers
Figure 7-13: Final configuration of Conditional Split
Figure 7-14: Prompt to define which output you want to send to the destination
Figure 7-15: Changing the FileName property
Figure 7-16: Package execution with files written to Orders and Order Details files
Figure 7-17: Adding a Lookup transform
Figure 7-18: Naming the connection in the WMI Connection Manager Editor
Figure 7-19: Final screen
Figure 7-20: Seeing the package succeed

Chapter 8: Deployment

Figure 8-1: Team Version Control Model
Figure 8-2: Add to SourceSafe dialog box
Figure 8-3: New icon to the left of each package, project, and solution in Solution Explorer
Figure 8-4: Check In dialog box
Figure 8-5: Pending Checkins window
Figure 8-6: Environment Options screen
Figure 8-7: Prompt as to whether you want to reuse the existing file or overwrite the file
Figure 8-8: Checking the Value option
Figure 8-9: Setting the Development configuration filter
Figure 8-10: Two package configurations
Figure 8-11: Example schema
Figure 8-12: Setting the ResultSet property to Full result set
Figure 8-13: Setting the Parameter Mappings
Figure 8-14: Setting the Result Set
Figure 8-15: Collection page
Figure 8-16: Specifying the container to use for output
Figure 8-17: Setting ReadOnlyVariables of the script
Figure 8-18: Final solution
Figure 8-19: Changing the CreateDeploymentUtiliity to True
Figure 8-20: Prompt to where you want to deploy the packages
Figure 8-21: Chance to edit the values in the configuration file at deployment time
Figure 8-22: Validated packages

Chapter 9: Managing SSIS

Figure 9-1: Configuring the service to restart in the event of a failure
Figure 9-2: File System store
Figure 9-3: Two instantiations of the FileWatcher package
Figure 9-4: The package you want to execute and where the package is located
Figure 9-5: The Connection Managers page
Figure 9-6: The Execution Options page
Figure 9-7: The Reporting page
Figure 9-8: The Set Values page
Figure 9-9: The Verification page
Figure 9-10: The Command Line page
Figure 9-11: The Package Execution Progress window
Figure 9-12: The Package Roles dialog box
Figure 9-13: The Database Role - New dialog box
Figure 9-14: The General page
Figure 9-15: Log File Viewer
Figure 9-16: The New Credential page
Figure 9-17: The New Proxy Account dialog box
Figure 9-18: Granting server roles, specific logins, or members of given msdb roles rights to your proxy
Figure 9-19: The Environment Variables dialog box
Figure 9-20: Setting the PrecompileScriptIntoBinaryCode to True
Figure 9-21: Setting the Run64BitRuntime to False

Chapter 10: Handling Heterogeneous and Unusual Data

Figure 10-1: Extract from the mainframe
Figure 10-2: CustomerName and OrderID columns
Figure 10-3: The Flat File Connection Manager Editor
Figure 10-4: Unpivot Transformation Editor
Figure 10-5: The Conditional Split Transformation Editor
Figure 10-6: The final package
Figure 10-7: File containing information about multiple tables
Figure 10-8: Columns page of Connection Manager Editor
Figure 10-9: Column 4 properties in Connection Manager Editor
Figure 10-10: Final configuration of the transform
Figure 10-11: Data Conversion – Order in the Data Conversion Transformation Editor
Figure 10-12: Data Conversion – Order Details in the Data Conversion Transformation Editor
Figure 10-13: Configuring the Order Extract destination
Figure 10-14: Configuring the Order Details Extract destination
Figure 10-15: Final configuration in the DB Destination Editor
Figure 10-16: Alignment in the Mappings page
Figure 10-17: The Break Up File Data Flow Task
Figure 10-18: Changing the Data Type, Precision, and Scale
Figure 10-19: Casting variables of data types that don’t match to a string to match the SQL statement
Figure 10-20: Specifying the variable that had the expression on it

Chapter 11: Migrating from DTS to SSIS

Figure 11-1: Data Transformation Services shown in Object Explorer
Figure 11-2: DTSRun.exe wrapper
Figure 11-3: Detail about your DTS package and advanced options
Figure 11-4: Pasting in the command from DTSRunUI.exe
Figure 11-5: Upgrade Advisor
Figure 11-6: Appearance of packages for download
Figure 11-7: Migrating packages one at a time in the Package Migration Wizard
Figure 11-8: List of DTS packages you’re about to convert
Figure 11-9: Packages migrating
Figure 11-10: Errors to be addressed
Figure 11-11: The User::strServerName variable dragged into the Expression box
Figure 11-12: Property Expressions Editor screen filled out
Figure 11-13: Encapsulated business logic in an ActiveX Script inside the Transform Data Task
Figure 11-14: Part of the package that could not be migrated over
Figure 11-15: Viewing the segment of the DTS package that could not be migrated
Figure 11-16: Final data flow
Figure 11-17: Error appearing when you first open the Connection Manager

Chapter 12: Scaling SSIS

Figure 12-1: The SQL Server 2005 Services container
Figure 12-2: Sample SSIS log report
Figure 12-3: Package importing three identical flat files
Figure 12-4: Performance Monitor with counters
Figure 12-5: Performance Monitor counters during execution
Figure 12-6: Data flow containing seven execution trees
Figure 12-7: One execution tree for the entire data flow
Figure 12-8: Applying an 80/20 rule
Figure 12-9: Example data flow with a second execution tree added
Figure 12-10: Simple data flow with the Properties window showing
Figure 12-11: OLE DB Destination Editor
Figure 12-12: Sample data flow
Figure 12-13: SQL Server Profile trace
Figure 12-14: Fast load options
Figure 12-15: Redirecting the batch and then doing a second OLE DB Destination
Figure 12-16: Package storage and execution location
Figure 12-17: Package execution on source server
Figure 12-18: Package execution on destination server
Figure 12-19: Package execution on secondary server
Figure 12-20: Package execution on tertiary server
Figure 12-21: Distributed package execution



Expert SQL Server 2005 Integration Services
Expert SQL Server 2005 Integration Services (Programmer to Programmer)
ISBN: 0470134119
EAN: 2147483647
Year: 2004
Pages: 111

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net