SQL 2000 Data Transfer is a Snap with SQL 2005

Introduction

Data Transformation Services in SQL 2000 was an excellent tool. I am using the past tense because the same service in SQL 2005 has all the good features of DTS and then some. This illustrated tutorial looks at transferring data (table or tables) from SQL 2005 back and forth to SQL 2000 from a server on the network. The basic tool, the SQL Server Import Export Wizard, is used to make this transfer. You will see how easy it is to transfer from one to the other. I recommend readers of this article to review my several articles on DTS on the ASP Free site to appreciate the differences.

• The source locations of the data
• Registering the networked SQL 2000 Server
• Exporting from SQL 2005 to SQL 2000 server
• Modifying the transport
• Exporting from SQL 2000 to SQL 2005 server
• Where is the package?
• Summary

The MS SQL 2000 server is on a Windows XP Professional machine which is connected through a Cisco router to the MS SQL 2005 Server on a machine with Microsoft XP Professional Media Center edition as the operating system. The Windows-integrated authentication is used for SQL 2005, and SQL authentication is used for the SQL 2000 server. A Northwind database table will be exported to the SQL 2000 Server's database from SQL 2005, and the "pubs" database tables are transferred from SQL 2000 to the SQL 2005. The SQL Server Import Export wizard on SQL 2005 Server is used.

For both export operations, the new data provider SQL Native Client is used. SQL NC, short for SQL Native Client, is a new player in the data access arena. It is not a part of the MDAC suite of data access components, of which there are many versions. SQL NC is supported by SQLXMLOleDb 4.0 and is ideally suited to work with SQL 2005.

In order to make life easier, the first task to be accomplished is to register a server, this one on the network. In SQL 2005 you have a new set of windows to contend with for registering the new server. It is important to know the authentication information required. First log on to the SQL 2005 server instance, herein called Hodentekmysorian, where Hodentek is the machine name and mysorian is the SQL 2005 server instance as shown in the next picture. Substitute mentally that "Yen" is same as "" (that's a problem I still need to fix). Click on connect. You will see the server and its objects in the object browser on the left hand side.

Now highlight the server instance to which you got connected and right click to choose Connect again. In the login box click on Browse for more.... This brings up the next Browse for Servers screen where the tabs give access to local and networked servers. Change the tab to Network. Depending on the connection it will take a short time before you see the listing of all network servers. It is assumed that you have started the SQL server service on the networked machines you are attempting to connect.

The connection to server XPHTEK(8.0) is made and the OK button is clicked. This brings us to the login screen for the XPHTEK machine as shown. Choose SQL Authentication, and after entering the User Name and Password click on the Connect button.

With this task completed you are connected to the XPHTEK server which now appears as a new node in your object browser as shown. It's nice to see that you also know to what version of SQL server you are connected. Of course you can expand all the nodes and verify the objects you want to use before using them.

The export operation will consist of exporting a table from Northwind on SQL 2005 to the database called TestRun on SQL 2000. Currently there are a couple of tables in TestRun.

To access the SQL Server Import and Export Wizard, right click on the Northwind database in SQL 2005 and choose Export Data from the drop-down menus as shown in the next picture. In SQL 2000 you had a wizard for this task; the same is true for SQL 2005.

The welcome screen appears when you choose the export data option as shown. Read the list of things it can do before you click next. It also creates SSIS packages, as they are called in SQL 2005. Click on Next.

The wizard wants the source of the data and it comes up with the default, a SQL Native client. HODENTEK/MYSORIAN answers this description. There is only one user database called Northwind, and it comes up as a default as shown. Click on the Next button.

Next the wizard requires the destination. It comes up again with the SQL Native client. The default is used. Also since the XPHTEK requires SQL Login, this option is chosen and the information is entered. Now it is possible to choose the database from the drop-down menu as shown. The TestRun database is chosen. Click on the Next button.

Next you need to choose the source table or tables for transporting. Here the customers table is chosen as shown. Click on the Next button.

In the Save and Execute Package screen of the wizard there are a couple of options, fewer than what was available in SQL 2000 (DTS). The choices made are shown. Click on the Next button.

Now you get a number of options to protect your package, the default being the one shown here. This is again slightly different from the one in DTS (review my article or MS BOL).

When you click Next you get to the next screen, where you enter information as to how you want to authenticate. You may also give a name and a description. The package will be saved with that name.

This completes this task and you are almost finished. You will find a list of actions that will be taken when you click finish.

After you click finish the actions will be performed in the order shown in the previous list. You will see a progress bar (so far so good, error court is zero) and the task will finish in a short while.

Well there was a problem; it is promptly flagged and the processing stops midway. There is a hyperlink to the error message which, when clicked, shows the next screen. You can take a variety of actions, like reading the report, sending an email message, and so forth.

It appears that there already was a table named Customer and this was the reason the transfer of the table did not succeed. Let's start over and get another table, this time named Products, to the SQL 2000 server's database TestRun.

This can be easily done by clicking the <back button a couple of times until you come to the screen shown next. Uncheck the Customers table and check the Products table to be exported.

Clicking on next to the above will bring up the saving of packages dialogue as shown.

Since you want it overwritten, click yes, and you will come to the final screen showing the success of the operation.

If you now look at the TestRun node you will see the Products table in the database. If you don't, just highlight the database and click refresh.

Now click on the Report drop-down menu and send an email about this success to Mike at his company. Outlook Express or the local mail client will pick up the message and deliver it to Mike as shown. I think this is cool. No need to worry about MAPI profile or other things.

All the tables from the "pubs" database on XPHTEK will be exported to the SQL 2005 Server. Some of the steps are not shown because they are the same ones we used before, except that the data now comes from XPHTEK. You need to provide the authentication and the database that you want the tables to come from, in this case "pubs." When you click Next, the destination dialogue shows up as shown.

SQL 2005 server has just one database, Northwind. You want the destination to be a new database called Xpubs. You can create the new database Xpubs by clicking on the New... button.

Here you will be able to set the data file and log file sizes and their growth rate. Clicking Next will create the Xpubs database when the package is run.

Again, some screens may have been skipped, but when it comes to choosing the table to be transferred you may select all of them. This interface looks similar to the one in SQL 2000. Instead of the Transform button you have an Edit button for each of the tables. In this tutorial just the default is accepted.

Clicking next and pursuing the wizard to the end finishes the package.

After running the package the new database Xpubs appears in mysorian's node as shown.

The SSIS management interface is not attached to the server by default, and this is somewhat mysterious to those who worked with SQL 2000. You need to attach the SSIS to the management studio. This you implement as shown in the next picture by clicking on the drop down menu right next to Database Engine and choosing Integration Services.

After this you provide the authentication information which will take you to the next screen. Here you highlight Hodentek after expanding the node, and click OK.

This adds the SSIS management objects to the server nodes as shown. The package you created will be in the MSDB folder of the Stored Packages. You may recognize the name that was given to the package at the time it was created. From here on you can do a variety of things with the package as shown.

Summary

Only a very small part of the integration services as exposed by the SQL Server Import/Export wizard was described. It is shown that the two way transfer of tables is very easy. Although a large number of screen shots were shown all of it can be accomplished in under 15 minutes. The user interface exposed by the wizard is very user-friendly, and several new features described in the tutorial makes it easy to use, providing a very useful and handy tool.