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:
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:
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:
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:
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.