Monday, May 9, 2016

Import/Export Wizard Simply Explained

Import/Export Wizard is an utility program you find in SQL Server (almost all versions) that can be used for Exporting or Importing data between SQL Servers; between SQL Server and a number of other data sources; or import/export data between data formats including databases, spreadsheets and text files.

DTSW_00 and DTSW_01.png

Export/Import utility can be launched from a database node in a SQL Server or from command line by typing in DTSWizard at command prompt.
The Welcome screen of the Wizard is displayed as shown.


Let us consider a simple case of copying a database from SQL Server 2012 to SQL Server 2014. I am choosing this pair as I have the two servers on my computer.

In particular, I will be exporting the pubs database in SQL Server 2012 to the named instance of SQL Server 2014.


In the SQL Server Import Export Wizard (DTSW) I launched from command-line earlier, I click Next.
The Choose a Data Source page is displayed as shown.


Click on the drop-down handle for Data source: as shown in the next image. You will see the various data sources handled by this wizard.


We shall use the default data source, in this case the SQL Server Native Client 11.0. Note that the program automatically chose the named instance REGENCYPARK (SQL Server 2012). However, both the SQL Servers are accessible to this program. Since we are exporting from SQL Server 2012 to SQL Server 2014, RegencyPark server is correct. We will export to Everest server.


Since both servers were originally installed to work with Windows Authentication accept the default. There is a <default> Database. Click on the handle and set it to pubs by choosing it in the drop-down. Do not worry if you do not have pubs. It should work with any other choice.

With all the choices made so far, the Choose a Data Source window should appear as shown.


Click Next to display the Choose a Destination screen as shown.


Enter the following on this screen:
Destination: SQL Server Native Client 11.0
Server Name: Hodentek8\EVEREST
Authentication: Windows
Click on the handle for Database. The following drop-down is displayed.


Click on the button New... to open the following Create Database window.


Insert a name at the top. Herein pubs2. Accept all other defaults, and the database files that are going to be created are displayed inside the screen The OK button at the bottom becomes active. Click OK.
 The Choose a Destination window appears as shown.


Click Next> to display the Specify Table Copy or Query as shown.


Accept the default, Copy data from one or more tables or views. Click Next> to display the Select Source Tables and Views with check boxes for each of the tables or views in the pubs database.


Place check mark for the checkbox Source: and all the tables will be selected as shown.


We will not edit the mappings (how the tables should go into the destination). Of course you can click Help to get to the help menu.

Click Next. The Save and Run Package is displayed. We accept the first option (Default) Run immediately. The other option is to save it as a SQL Server Information Services (SSIS) package.


Click Finish>>| (you could also click Next> and look at some more details of what you have chosen to do so far). Complete the Wizard screen is displayed as shown:


Click Finish on this screen. The program enters an execution stage and finally comes up with a screen announcing success and the number of rows transferred etc. as shown.


Click on the Report button to display this drop-down list. You may choose an action you want to take.

Now, go to the EVEREST server in SQL Server Management Studio for version 2014 and verify that pubs2 has been created and it has tables and data.


That's it. Thanks for reviewing this post. 

PowerShell Pro Tools is a Visual Studio Community 2017 extension that you can try free

I see there is at least one extension, PowerShell Pro Tools that you can download. You can find it in the Visual Studio Community 2017...