SQL Server 2005 Import / Export Wizard

Monday Jan 30th 2006 by Gregory A. Larsen
Share:

DTS is no more. Instead, with SQL Server 2005 you get a new transformation tool named SQL Server Integrated Services (SSIS). Greg Larsen explains what new features are available and what features have been removed.

The Data Transformation Services (DTS) tool that you are familiar with in SQL Server 2000 has gone through its own transformation in SQL Server 2005. DTS is no more, but instead with SQL Server 2005 you get a new transformation tool named SQL Server Integrated Services (SSIS). SSIS is much improved over the old DTS. As a first look at SQL Server 2005's SSIS transformation tool, let's get a look at the import and export wizard to transfer data to and from SQL Server. I will try to explain what new features are available in this new wizard, as well as what features have been removed.

How to Bring Up the SSIS Import / Export Wizard

There are a number of different ways to start the SSIS Import / Export wizard. You can start it a couple of ways using the SQL Server Business Intelligence Development Studio. Or you can start it using the SQL Server Management Studio. Lastly, you can start it using an executable.

To demonstrate how to start the Import/Export wizard in the Business Intelligence Development Studio I need to start up the studio tool and open an Integrated Services project. Once I have done this, there are two methods to start the Import / Export Wizard. The first method is to click on the "Project" menu, and then select the "SSIS Import and Export Wizard..." from the drop down menu, as shown in the following screen shot:

Click for larger image

Another method to start up the SSIS Import/Export Wizard in the Business Intelligence Development Studio is to right click on the SSIS package folder and then select the "SSIS Import and Export Wizard..." as demonstrated on the following screen shot:

Click for larger image

If you want to start the Import/Export wizard from the SQL Server Management Studio then connect to a Database Engine server type. Once you are connected right click on one of your user databases, select the "Tasks" item, then either click on the "Import Data..." or "Export Data..." menu item. Below is a screen shot of where to locate the "Import..." or "Export Data..." tasks:

Click for larger image

The last way to bring up the SSIS Import and Export wizard is by running a DTSWizard.exe program from the command prompt. If you take the default installation parameters then this executable can be found in the "C:\Program Files\Microsoft SQL Server\90\DTS\Binn directory. Otherwise it will be where ever your installed the Binn directory while installing SQL Server 2005.

Using the Wizard to Migrate Some Data

In order to demonstrate how to migrate some data using the new Import / Export wizard I will first need to develop a situation where I can copy some data. For this example, I will be importing the following data into a new a SQL Server table:

CustomerID

Name

EmailAddress

Phone

1

Kitty Smith

ksmith@abc.com

800-123-5678

2

Frank Jackson

fj@xyz.org

417-891-1234

3

Mark Lopez

MarkL@dummycorp.com

234-555-8901

4

David Bishop

DJB@anywhere.org

345-678-9012

5

Nicole Duran

Nicole.Duran@somecomp.com

765-432-1098

6

Steve Franklin

SxFranklin@nocomp.com

321-456-0897

The flat file will contain the column headers on the first line, followed by the six records. Each column is delimited by a tab.

Regardless of how you start the SSIS Import or Export wizard, the first time, the following screen will be displayed as the Wizard starts:

If you do not want to see this welcome page again, then check the "Do not show this start page again" check box. When you click next on the above screen the following screen is displayed:

On this window, you identify the source of the data you want to import or export. So far, The SQL Server 2005 SSIS Import/Export wizard looks very similar to the DTS wizard provided in SQL Server 2000. One of the exciting new data sources supported by SSIS is an XML document.

Since my source data comes from a flat file, I need to use the "Data Source:" pull down so I can select the "Flat File Source" data source. Once I select the "Flat File" data source the following screen is displayed:

As you can see this screen is a lot different then the old "Text File" data source screen of the SQL Server 2000 DTS Import/Export wizard. Note that once you enter a "File name" all the grayed out options become active. The initial pane displayed is the "General" pane. On this screen, you have the option to specify the locale and code page for your flat file if it is different from your local machine. You also have options to specify how the raw data is formatted. For my example, I will enter "C:\temp\RawData_Imp_Exp_Wizard.txt in the "File name" field and then I will check the "Column names in the first data row" checkbox, since my column headings are in the raw data.

Once I click on the columns option on the left, the following pane is displayed:

On this pane, you are able to change the row and column delimiters. If you click on the advanced item, the following screen is displayed:

If you review all the columns here you will note that each column is defined using the defaults for width, and data type. If you want to change these, you can either type over the default definitions, or can click on the "Suggest Types..." button. When you click on the "Suggest Type..." button, the following screen is displayed:

With this pane, you can set the criteria you would like to use to suggest a data type. The wizard will then sample your data to determine the best data type for the selected column.

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:

Click for larger image

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:

Click for larger image

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:

Click for larger image

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:

Click for larger image

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:

Do not save sensitive

Suppresses sensitive information in the package when it is saved. This protection level does not encrypt, but instead it prevents properties that are marked sensitive from being saved with the package and therefore makes the sensitive data unavailable to other users. If a different user opens the package, the sensitive information is replaced with blanks and the user must provide the sensitive information.

Encrypt sensitive with user key

Encrypts only the sensitive information in the package by using keys based on the current user. Only the same user using the same profile can load the package. If a different user opens the package, the sensitive information is replaced with blanks and the current user must provide new values for the sensitive data. If the user attempts to execute the package, package execution fails. DPAPI is used for this encryption.

Encrypt sensitive with password

Encrypts only the sensitive information in the package by using a password. DPAPI is used for this encryption. Sensitive data is saved as a part of the package, but that data is encrypted by using a password that the current user supplies when the package is created or exported. To open the package in SSIS Designer, the user must provide the package password. If the password is not provided, the package opens without the sensitive data and the current user must provide new values for sensitive data. If the user tries to execute the package without providing the password, package execution fails. For more information about passwords and command line execution, see dtexec Utility.

Encrypt all with user key

Encrypts the whole package by using a key based on the user profile. Only the same user using the same profile can load the package. The package is encrypted by using a key that is based on the user who created or exported the package. Only the user who created or exported the package can open the package in SSIS Designer or run the package by using the dtexec command prompt utility.

Encrypt all with password

Encrypts the whole package by using a password. The package is encrypted by using a password that the user supplies when the package is created or exported. To open the package in SSIS Designer or run the package by using the dtexec command prompt utility, the user must provide the package password. Without the password the user cannot access or run the package.

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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved