It is currently 18 Oct 2017 4:14


Post new topic  Reply to topic  [ 3 posts ] 
Author Message
PostPosted: 13 Dec 2007 3:43 
Offline
Nouveau
Nouveau
User avatar

Joined: 03 Dec 2007 12:00
Posts: 8
Location: USA
Is there a quick reference guide for setting up DTS SQL batch type jobs?

Please point me to it if such a section in document exists. Thanks

_________________
Image
willys@wowmail.com
_________________


Top
   
PostPosted: 14 Dec 2007 7:51 
Offline
Nouveau
Nouveau
User avatar

Joined: 06 Feb 2007 12:00
Posts: 6
Hi Willys,
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) :Dts 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
• Source

• 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
• Click :ok

• 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'

Or
E.g : master..xp_cmdshell 'DTSRun /S 10.0.3.63 /u sa /p /N DtsTest'


Top
   
 Post subject:
PostPosted: 15 Dec 2007 8:57 
Offline
Nouveau
Nouveau
User avatar

Joined: 03 Dec 2007 12:00
Posts: 8
Location: USA
r3rajesh, I really appreciate the details!
I see there is a big learning curve/challenge ahead.
This certainly will help us get started!
My email address is willys@wowmail.com
Thanks!

_________________
Image

willys@wowmail.com

_________________


Top
   
Display posts from previous:  Sort by  
Post new topic  Reply to topic  [ 3 posts ] 

All times are UTC+02:00


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB® Forum Software © phpBB Limited
The site created by Franck YOT. Images, logo, content and trademarks about Control-M products are property of BMC Software.
All the comments are property of their posters. Images, logo, content and design are © copyright by Scheduler-Usage.com. All Rights Reserved