Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 30, 2006

SQL Server 2005 Import / Export Wizard

By Gregory A. Larsen

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:

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date