Setting DTS SQL batch type jobs involves number of steps to be done in SQL Server DB.Find the documentation below for your use.
Sorry , i could not place screenshots here for you, send ur mail ID so that i cud them !
Data Transformation Services:
Data Transformation Services(DTS)
ts packages’ are used for transforming data between ANY two ODBC compliant tools. For example:
– Between two Access databases vice versa.
– Between Excel and Access vice versa.
– Between Ms sql server and Ms sql server
– Between Oracle and Ms sql server vice versa
– Between Access and a comma delimited file.
Steps to create a DTS packages:
1. Open Enterprise manager expand it you will get list servers you have registered select the server in which server you want create DTS package (in this it is MYSQL server name) Select the Data transformation services
• Select the Data transformation services in the right pane select local package and take right click then select new package you will get next screen
• Here select the connections for source and destination under connection in the left tool box.
• Here we are transforming data from MS sql server to MS sql server
Source : Ms sql serve name : mysql
Database name : test
Table name : test
Destination : Ms sql serve name : mysql
Database name : jass
Table name : test1
We can transform data, table ,procedures,functions and all database objects etc .In this example I am using only Ms sql server to Ms sql server just I am transforming table data
• In connection tab take a mouse left click on Microsoft OLE DB provider for SQL server you will get next screen.
• New connection :Microsoft OLE DB provider for SQL .
• Data source :Microsoft OLE DB provider for SQL.
• Server Name @ GDC :MYSQL
• Select the :use SQL server authentication
• Username: sa
• Password : (nothing)
• Database : test
• Please Do the same for Destination from source heading but select the database name as jass
• Now you have selected source and destination
• By holding Ctrl key select source first with mouse , destination and select the in task tab with mouse transform data task
• it is showing connection between source and destinations and take mouse right click on that
• select the properties
• in source tab select which table data you want transform
• in the destination tab select the destination table name
• In the transformation tab select the select all option then click ok
• Now you have created DTS package successfully
• we can save this DTS package
• below screen showing under package tab
• Save :This option saves in the local sever in msdb database in syspackages table
• Save as: we can on another data base server or like file in the client machine
• Now I am saving as file in the client machine
• Select the Save as in the next screen
• Location tab select the structured storage file
• File name tab select location where you want the file to stored then click OK
Execution DTS package:
• We can execute DTS package from DOS prompt
• We can execute DTS package from SQL Query Analyzer
Execution DTS package from DOS prompt: start->run->cmd->DTSRun /F the path where you have saved the file (DTS package)
EX: C:\Documents and Settings\rajesh>DTSRun /f c:\dts1
Then press Enter <-
• The message you will get if it executed successfully
• Execute DTS package from SQL server Enterpriser manager : To execute dts package From SQL server Enterpriser manager or query analyzer you should save in the local server that is follow below screen
• Take save option you will get next screen
Package name : we can provide any name to package (e.g. DtsTest) click OK
Then close the window we can see you are created Dts package in next screen
• Then click on DtsTest that is you are package take right click see next screen
• Click on Execute package then see next screen
Execute DTS package from SQL query Analyzer: open the query Analyzer then
Give below command
E.g. : exec master..xp_cmdshell 'DTSRun /S 10.0.3.63 /u sa /p /N DtsTest'
E.g : master..xp_cmdshell 'DTSRun /S 10.0.3.63 /u sa /p /N DtsTest'