Ssis

SQL Server Integration Services

Goals

• The Import and Export Wizard

• Creating a Package

• Working with Connection Managers

• Building Data Flows

• Building Control Flows

• Creating Event Handlers

• Saving and Running Packages

SQL Server Integration Services

Microsoft says that SQL Server Integration Services (SSIS) “is a platform for building high performance data integration solutions, including extraction, transformation, and load (ETL) packages for data warehousing.” A simpler way to think of SSIS is that it’s the solution for automating data movements. SSIS provides a way to build packages made up of tasks that can move data around from place to place and alter

it on the way. There are visual designers (hosted within Business Intelligence Development Studio) to help you build these packages as well as an API for programming SSIS objects from other applications.

Here you’ll see how to build and use SSIS packages. First, though, we’ll look at a simpler facet of SSIS: The SQL Server Import and Export Wizard.

If you choose to use the supplied solution files rather than building your own, you may need to edit the properties of the OLE DB Connection Managers within the projects to point to your own test server. You’ll learn more about Connection Managers in the “Working with Connection Managers” section later in this document.

The Import and Export Wizard

Though SSIS is almost infinitely customizable, Microsoft has produced a simple wizard to handle some of the most common ETL tasks: importing data to or exporting data from a SQL Server database. The Import and Export Wizard protects you from the complexity of SSIS while allowing you to move data between any of these data sources:

• SQL Server databases

• Flat files

• Microsoft Access databases

• Microsoft Excel worksheets

• Other OLE DB providers

You can launch...