DTS (Data Transformation Service) or the Import / Export utility as it is widely known as, is used to transfer objects and data between 2 SQL Server databases residing on 2 different Servers. DTS is a set of graphical tools and programmable objects that let you extract, transform, and consolidate data from disparate sources into single or multiple destinations that can be bundled and saved as a package and executed.
How to use DTS?
You can use the DTS utility by referring to the process mentioned below -
-
Open MS SQL Enterprise Manager.
-
Right click the database whose contents you wish to transfer to a different database on another MS SQL server or onto which you wish import external data.
-
Click All Tasks.
-
Click Import or Export depending upon the type of activity you are performing (If you are trying to update the database on our Server with the copy on your local machine, then you would need to export the data from your local machine).
-
Select the Data Source as Microsoft OLEDB Provider For SQL Server.
-
Enter the IP Address / Name of the Server from where you are exporting the data.
-
Select SQL Authentication as the Auth Mode.
-
Enter the Username and Password to login to the database.
-
Select the database from the drop-down list.
-
Click Next & enter the destination Server IP Address.
-
Enter the Username and Password for the database on the Remote Server.
-
Select the database from the drop-down list and click Next.
-
Select the radio button Copy Objects & Data between SQL Server Databases and click Next.
-
Uncheck Copy all objects & Use default options checkboxes. You would be selecting the options & objects to be copied.
-
Click Select Objects & uncheck the check box for Stored Procedures & User defined Functions.
-
Click Check all and then click OK.
-
Click Options and uncheck the check box for Copy Database Users & Database Roles & Copy Object Level permissions.
-
Click Next and run the package immediately or schedule it for a later run.
-
Click Finish to complete the process.
Tags: Amp Database, Button Copy, Click Import, Click Options, Copy Database, Data Transformation Service, Database Roles, Database Users, Default Options, Destination Server, Disparate Sources, Export Utility, Graphical Tools, Import, Level Permissions, Microsoft, Microsoft Oledb Provider, Ms Sql Server, Open Ms, Open MS SQL Enterprise Manager, Programmable Objects, Radio Button, SQL, Sql Enterprise Manager, Sql Server Databases
Gossimer in the News