Using the SSIS Import and Export Wizard

Now let's do something useful with your newfound skills using the BIDS design environment.

The SSIS Import and Export Wizard can be launched from within the SQL Server Management Studio or from a BIDS Integration Services project. The wizard is simple to use when building basic import or export processes but will also allow you to transform data. Depending on which of the two options you choose, either the data source or data destination page will be filled out.

The wizard allows you to save the output to an SSIS package .dtsx file, which can later be added to an SSIS project. You also have the option to execute this package immediately or to save it and execute it later.

Creating Tables in a New Database

In this exercise, you will follow steps to create a new SSIS project and then replace the default package with one generated from the Import and Export Wizard.

You will export product and sales data from the is2005sbs database. This is a simplified version of the Adventure Works Cycles sample database for SQL Server 2005. It is an operational data store or transactional database system as opposed to a data warehouse or decision support database. The structure of this database is similar to common systems found in many businesses.

You will use the wizard to create a new database called QuickStartODS on the local server, and you will import the structure and contents of five tables from the sample database, whose data will be copied to the new database. The wizard can be launched from a database in SQL Server Management Studio or from an SSIS project in BIDS.

Run the Wizard in BIDS

1. To run the wizard in BIDS, right-click SSIS Packages in Solution Explorer, and then click SSIS Import and Export Wizard.

Review the is2005sbs Database by Using Management Studio

1. Open SQL Server Management Studio (SSMS):

   1. Click Start, and then click SQL Server Management Studio.

   2. In the Connect to Server dialog box, ensure that the Server Type is Database Engine and the Server Name is localhost, and then click Connect.

2. In Object Explorer, expand the Databases folder.

3. Expand the is2005sbs database, and then expand the Tables folder.

   The is2005sbs database is a simplified version of the Adventure Works sample database provided with SQL Server 2005. It is a transactional or operational database schema rather than a data warehouse schema. You will use this to build your first business intelligence (BI) solution. One of the significant differences between the operational database and the data warehouse database is that there is no Time dimension table in the transaction database.

4. Review the following tables:

   • Customer

   • Product

   • ProductCategory

   • ProductSubcategory

   • SalesOrderDetail

   • SalesOrderHeader

   Later in this exercise, you'll create a working copy of these tables from the is2005sbs database.

Create the QuickStart Solution to Contain the QuickStartIS SSIS Project

1. In BIDS, create a new Integration Services project named QuickStartIS by clicking the link to create a project on the BIDS start page (or, from the File menu, click New, and then click Project) and selecting the Integration Services Project template.

2. Change the solution name to QuickStart.

3. Browse to C:\Documents and Settings\<username>\My Documents\Microsoft Press\ is2005sbs\Chap02 and select the check box at the bottom of the New Project dialog box to create a directory for the solution.

   A package is automatically created when you start a new Integration Services project. Because you'll use the wizard to create a new package, you don't need the default package.

4. In SSIS, in Solution Explorer, right-click the  Package.dtsx file and choose Delete.

   In the following exercise, you'll use the Import and Export Wizard to create a package that imports the listed tables into a new destination database.

Import Tables into a New QuickStartODS Database with a New Package

1. Right-click the SSIS Packages folder, and then click SSIS Import and Export Wizard.

2. On the Welcome page, click Next.

3. On the Choose a Data page:

   1. Accept SQL Native Client as the data source.

   2. Accept the default Server Name of (local).

   3. Keep the Use Windows Authentication default authentication.

   4. In the Database drop-down list, change the database to is2005sbs.

4. Click Next.

5. Select SQL Server (local) as the destination.

6. Click New, type QuickStartODS as the database name, and then click OK to create the new database.

7. Click Next.

   The abbreviation ODS means Operational Data Store. This is an empty database in which to place the copied tables that you'll use later. It will not be a true star schema but a copy of the operational database to use for prototyping.

8. In the Specify Table Copy or Query page, select Copy Data From One Or More Tables Or Views, and then click Next.

9. Select the following tables to copy:

   • [is2005sbs].[dbo].[Product]

   • [is2005sbs].[dbo].[ProductCategory]

   • [is2005sbs].[dbo].[ProductSubcategory]

   • [is2005sbs].[dbo].[SalesOrderDetail]

   • [is2005sbs].[dbo].[SalesOrderHeader]

10. Click Next.

    When you finish the wizard, the package is created but not executed. You can see it added to the SSIS Packages folder.

11. Click Finish, and then click Close to complete the wizard.

12. In Solution Explorer, right-click the new package and choose Rename to change the package name to  QuickStartIS.dtsx.

    A message box prompts you to synchronize the package file name and the object that it defines. Click Yes to keep these names the same.

    Note 

    Notice the first two tabs: Control Flow and Data Flow. The Control Flow tab handles discrete sequential tasks, with precedence constraints connecting them. The Data Flow tab handles continuous processes that move streams of data from a source to a destination. The second transformation in a complex data flow can often run concurrently with the first. Multiple pipelines can run in parallel within a single data flow task.