Exam 70-458 – Objective 6 – Load data

Design control flow

• Determine control flow (http://msdn.microsoft.com/en-us/library/ms137681.aspx)
• Determine containers and tasks that are needed
  • Tasks (http://msdn.microsoft.com/en-us/library/ms139892.aspx)
    • Execute SQL, Execute Package, Execute process
    • File System task
    • Send mail task
    • Task grouping
  • Containers (http://msdn.microsoft.com/en-us/library/ms137728.aspx)
    • Sequence (http://msdn.microsoft.com/en-us/library/ms139855.aspx)
    • For loop (http://msdn.microsoft.com/en-us/library/ms139956.aspx)
    • Foreach loop (http://msdn.microsoft.com/en-us/library/ms141724.aspx)
• Determine precedence constraints (http://msdn.microsoft.com/en-us/library/ms141261.aspx)
  • Simple – Tasks execute in parallel by default
  • Constraints and expressions
• Design an SSIS package strategy with rollback, staging, and transaction control (http://msdn.microsoft.com/en-us/library/ms137690.aspx)
  • Required – Container starts transaction unless one exist in parent container
  • Supported – Container joins existing transaction
  • NotSupported – Container doesn’t join or start a transaction
• Decide between one package or multiple packages
• Determine event handlers
• Determine variables (http://msdn.microsoft.com/en-us/library/ms141085.aspx)
• Determine parameters on package and project level
• Determine connection managers and whether they are package or project level
• Determine the need for custom tasks
• Determine how much information you need to log from a package (http://msdn.microsoft.com/en-us/library/ms140246.aspx)
• Determine the need for checkpoints
  • Allow a package to restart where it was last successful
• Determine security needs

Implement package logic by using SSIS variables and parameters

• User variables (http://msdn.microsoft.com/en-us/library/ms140216.aspx)
  • Variable Usage Scenarios
    • Property expressions
    • Data flow expressions
    • Precedence contraint expressions
    • Parmeters and return codes
    • For loop expressions
    • Parent package variable configurations
    • Script task and Script component
• Variable scope, data type 
  • Scope (http://msdn.microsoft.com/en-us/library/ms141670.aspx)
• Implement parameterization of properties using variables (http://msdn.microsoft.com/en-us/library/ms141214.aspx)
• Use variables in precedence constraints
• Refer to SSIS system variables
• Design dynamic SSIS packages
• Package configurations (file or SQL tables) (http://msdn.microsoft.com/en-us/ms141682.aspx)
• Expressions (http://msdn.microsoft.com/en-us/library/ms141214.aspx)
• Package and project parameters
• Project level connection managers
• Implement dynamic package behavior
• Configure packages in SSIS for different environments, package configurations (xmlconfiguration file, SQLServer table, registry entry, parent package variables, environment variable) (http://msdn.microsoft.com/en-us/library/ms141682.aspx)
• Parameters (package and project level)
• Project connection managers
• Property expressions (use expressions for connection managers)

Implement control flow

• Checkpoints
  • Restart Packages by Using Checkpoints (http://msdn.microsoft.com/en-us/ms140226.aspx)
    • At packager level, set CheckpointFileName, CheckpointUsage = IfExist, and SaveCheckPoints = True
    • Set FailPackageOnFialure = True for all containers that you want to restart from
    • Use ForceExecutionResult = True to test checkpoints
    • Using dtexec must specify parameter /CheckPointing on
    • Checkpoint data is not savied for For Loop and Foreach Lopp containers, you must wrap in a Sequence container (http://support.microsoft.com/kb/2624458)
    • Be sure to secure checkpoint file as it could contain sensitive information
• Debug control flow (http://msdn.microsoft.com/en-us/library/ms140274.aspx)
  • Breakpoints
  • Progress Reporting
  • Debug Windows
• Implement the appropriate control flow task to solve a problem
• Data profiling
• Use sequence containers and loop containers
  • Using Sequence Containers (http://www.bidn.com/blogs/TomLannen/bidn-blog/2620/using-sequence-containers-in-ssis)
• Manage transactions in SSIS packages
• Manage parallelism (http://blogs.msdn.com/b/sqlperf/archive/2007/05/11/implement-parallel-execution-in-ssis.aspx)
• Use precedence constraint to control task execution sequence
• Create package templates
• Use the execute package task (http://msdn.microsoft.com/en-us/library/ms137609.aspx)

Implement data load options

• Implement a full and incremental data load strategy
  • Incremental Loads (http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis_design-pattern-incremental-loads.aspx)
• Plan for an incremental update of the relational Data Mart (http://msdn.microsoft.com/en-us/library/bb895287.aspx)

Preparation resources

• Integration Services transactions
• Developing a custom task
• Integration Services (SSIS) parameters