• Extract from the source
• Transform the data
• Load to the destination

Data transformation involves:

• Changing the format of data
• Aggregating data
• Making data consistent
• Validating data

• Bulk Copy Program
• BULK INSERT
• XML Bulk Load
• Replication
• SQL Server Integration Services

• Transfer and transform data
  • SSIS contains a data-flow engine to transfer and transform data to and from varied data sources
• Tools and wizards
  • SSIS contains graphical tools and wizards for creating an extraction, transform, and loading system
• Terminology
  • Packages are the units of work that you execute
  • Tasks do the work in packages
  • Containers are objects that provide strucuture to packages

• Control flow
  • Containers
  • Tasks
  • Precedence constraints
• Data flow
  • Sources
  • Transformations
  • Destinations

• Wizards
• SSIS Designer
• Command-prompt utilities

• Data sources
  • A real-time reference to a data store connection
• Data source views
  • Provide a subset of data from a data source
• Types of data sources

• A logical representation of a connection
• Connection managers are available that connect to a variety of data sources and servers

• Tasks
  • Tasks do the work in packages. The most important task is the data flow task
• Precedence constraints
  • Precedence constraints join tasks together
• Containers
  • Containsers allow you to group together, or loop through, the tasks

• Source
  • Connection to the data store that you load data from
• Transformation
  • Can be a simple column mapping or a complex modification and cleaning operation
• Destination
  • Connection to the data store that you load data into

• Define package configurations
  • Enable you to update the values of properties at run time
• Build the project
  • Configure the deployment utility
  • Build the project
• Deploy the build
  • Copy the build folder to the new system
  • Run the manifest

• Data tasks
• Database object transfer tasks
• File and network protocol tasks
• Script and program execution tasks
• WMI tasks
• Maintenance plan tasks

• Data format transformations
• Sorting and grouping transformations
• Column transformations
• Lookup transformations
• Custom processing transformations
• Split, merge, and join transformations
• Audit transformations

• Use events to
  • Perform additional processing as a package executes
  • Capture exceptions and errors
• Implementing an event handler
  1. Select the package, container, or task
  2. Select the event type
  3. Define the event handler workflow

• Logging and events
• Log providers
• Configuring logging
• Enabling logging for a container or task