- Exam 70-458 – Objective 1 – Manage Data
- Exam 70-458 – Objective 2 – Implement Security
- Exam 70-458 – Objective 3 – Implement high availability
- Exam 70-458 – Objective 4 – Design and implement a data warehouse
- Exam 70-458 – Objective 5 – Extract and transform data
- Exam 70-458 – Objective 6 – Load data
- Exam 70-458 – Objective 7 – Configure and deploy SSIS solutions
- Exam 70-458 – Objective 8 – Build Data Quality solutions
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)
- Tasks (http://msdn.microsoft.com/en-us/library/ms139892.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 Usage Scenarios
- Variable scope, data type
- 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
- Restart Packages by Using Checkpoints (http://msdn.microsoft.com/en-us/ms140226.aspx)
- 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
- Plan for an incremental update of the relational Data Mart (http://msdn.microsoft.com/en-us/library/bb895287.aspx)
Preparation resources