SQL Server 2005 Import / Export Wizard - Page 2

January 30, 2006

Once you are done specifying all of the data source information, you need to specify the destination. For my example, I want to place my source data into a SQL Server database table. I will use the "Choose a Destination" pane below to specify where to place my data:

One of the new features here is you now can create a new database on the fly. This is done by clicking on the "New" button. When you do this, the following screen will be displayed:

On this screen, you can specify the properties for your new database. Since I want to create a new database named "Wizard_Example", I specify my database properties on this pane. When done defining the properties of my new database I click on the "OK" button. Doing this takes me back to the "Choose a Destination" window, and also creates my database at the same time. Clicking on the "Next>" button on the "Choose a Destination" pane will display the window below:

On this screen, you can click on the "Edit..." button to modify how the Wizard handles populating the destination table. I my case I am creating a new database table, so I used this button to verify that the table that is being created is correct. After clicking on the "Next>" button the following screen is displayed:

Here you can execute the package immediately and/or save your SSIS package. I will choose to execute and save my package in a File System. Once I check the "Save SSIS Package" checkbox, the grayed out options become active and I am able to select the "File System" radio button. After clicking on the "Next>" button the following window is displayed:

The SQL Server 2005 SSIS tool allows five different package protection levels. Here is the SQL Server 2005 Books Online explanation of each of these protection levels:

When saving my example package I will save it using the default protection level, which is "Encrypt sensitive data with user key". Once I have selected a protection level, the "Save SSIS Package" window is displayed. Here is what I specified on this screen:

After clicking on "Next>" on the above screen the final wizard screen will be displayed. Here is that final screen:

This screen summarizes your packages specifications. If you need to change your package parameters you can still use the "<Back" button to make changes. Once you click on the "Finish" button, the wizard completes and you will not be able to go back and modify your package specification. If you checked the "Execute immediate" button then as the wizard finishes it will start executing your package. In my example, I selected the "execute immediate" option. As my package executes I can see the progress of the different actions. For my simple package, the following screen is the final display after my package has completed. Note a similar screen is displayed while my package was running, only the statuses of each action where different depending on the progress of the package execution.

Conclusion

The SSIS Import/Export wizard in SQL Server 2005 makes it extremely easy to import and export data to and from SQL Server. Microsoft has made numerous improvements over the Import/Export wizard of SQL Server 2000. If you have not had a chance to look into using SSIS within SQL Server 2005, it might be worth using the Import/Export wizard as the first step in learning about SSIS.

» See All Articles by Columnist Gregory A. Larsen