Flylib.com
List of Figures
Previous page
Table of content
Next page
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
Previous page
Table of content
Next page
Expert SQL Server 2005 Integration Services (Programmer to Programmer)
ISBN: 0470134119
EAN: 2147483647
Year: 2004
Pages: 111
Authors:
Brian Knight
,
Erik Veerman
BUY ON AMAZON
OpenSSH: A Survival Guide for Secure Shell Handling (Version 1.0)
Step 3.1 Use PuTTY as a Graphical Replacement for telnet and rlogin
Step 3.4 Use PuTTYs Tools to Transfer Files from the Windows Command Line
Step 4.1 Authentication with Public Keys
Step 4.7 Using Public Key Authentication for Automated File Transfers
Step 6.2 Using Port Forwarding Within PuTTY to Read Your E-mail Securely
Image Processing with LabVIEW and IMAQ Vision
Other Image Sources
Frequency Filtering
Morphology Functions
Pixel Value Analysis
Image Focus Quality
SQL Hacks
Joins, Unions, and Views
Hack 36. Calculate the Distance Between GPS Locations
Locking and Performance
Hack 80. Play Six Degrees of Kevin Bacon
Hack 96. Create an Audit Trail
The Complete Cisco VPN Configuration Guide
Overview of VPNs
VPN Implementations
Summary
Easy VPN Server Support for 7.0
Company Profile
Ruby Cookbook (Cookbooks (OReilly))
Graphing Data
Indexing Unstructured Text with SimpleSearch
Grabbing the Contents of a Web Page
Finding the Cost to Ship Packages via UPS or FedEx
Determining Terminal Size
Understanding Digital Signal Processing (2nd Edition)
SIGNAL AMPLITUDE, MAGNITUDE, POWER
DFT RESOLUTION, ZERO PADDING, AND FREQUENCY-DOMAIN SAMPLING
AN INTRODUCTION TO INFINITE IMPULSE RESPONSE FILTERS
WHY CARE ABOUT QUADRATURE SIGNALS?
Section G.5. REAL FSF TRANSFER FUNCTION
flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net
Privacy policy
This website uses cookies. Click
here
to find out more.
Accept cookies