SSIS Part 2 - Creating SSIS packages using Import & Export wizards

SQL Server Management Studio (SSMS) provides Import and Export Wizards which can be used to transfer data from one source to another. You can choose from a variety of source and destination data source types, select tables to copy or specify your own query to extract data, and save this as an SSIS package. I guess the Import and Export Wizard is a good starting point for learning about SSIS packages so I will walk through the steps to run these wizards:

1. Export Wizard

SSMS provides the Export Wizard task which can be used to copy data from one data source to another. Here I'll go through the Export Wizard to export data from a SQL Server database to an Excel Sheet.

Note: I am taking example of AdventureWorksDW to explain. You can download the AdventureWorksDW sample database from the CodePlex.

Below are the steps to create SSIS Package using Export Wizard

Step1: Launch SSMS and connect to the Database Engine. For demonstration purposes I am using AdventureWorksDW database. Right click on the AdventureWorksDW database in the Object Explorer, select Tasks, and then Export Data… from the context menu to launch the Export Wizard. Click Next to advance past the Welcome dialog (if shown).

Step2: Choose a Data Source. The Choose a Data Source dialog allows you to specify the source of your data. Since we are running the Export wizard, the dialog will be displayed with the values already filled in as shown below:

Step3: Click on Next to choose a destination. The Choose a Destination dialog allows you to specify the destination data source for the data you are exporting. For example we will export our data to Excel so we will use this Excel spreadsheet as the destination. Fill in the dialog as follows:

Step4: Click Next to Specify Table Copy or Query dialog. The Specify Table Copy or Query dialog allows you to choose whether to export data by selecting tables and/or views from the data source or specifying a query to extract data. Select Copy data from one or more tables or views option and click Next to proceed to the Select Source Tables and Views dialog.

Step5: The Select Source Tables and Views dialog allows you to select the tables and views that you want to export. For our demonstration we are going to select the DimGeography table as shown below:

You can click the Preview… button to view the first 100 rows of the data in the data source.

You can click the Edit Mappings… button to review the column mappings from the data source to the data destination. You can click the option to drop and recreate the table in the destination data source; by default this option is unchecked.
You can click the Edit SQL… button to review and/or edit the SQL to create the table in the destination data source. Click OK twice to return to the Select Source Tables and Views dialog, then click Next to proceed to the Save and Execute Package dialog.

Step6: The Save and Execute Package dialog gives you options to perform the export operation and to create an SSIS package and save it to SQL Server or the file system as shown below:

Step7: Click Next to proceed to the Save SSIS Package dialog. The Save SSIS Package is invoked if you chose to save your export operation as an SSIS package on the Save and Execute Package dialog. Fill in the dialog as shown below:

Step8: Click Next to the Complete the Wizard dialog. The Complete the Wizard dialog shows a summary of the options that we have chosen for the export operation. Now click Finish to execute the SSIS package. Once the command is over, you will see the number of rows transferred from Source to Destination.

We are done! You can open the Excel spreadsheet and view the table that was exported. You can also view the package at location D:\SSIS.