?
Solved

MS SQL DTS RESOURCE AND METHOD

Posted on 2012-09-18
13
Medium Priority
?
183 Views
Last Modified: 2012-10-01
Hi All,

I want transfer data between server, not sync data.
I want to use dts for the purpose.

I want to know :

1. The resource, like internet connection (should have public IP for both server or not), kind of connection to use and etc.
2. How many method to achieve this.
3. Things to care of using the method.

Thank you.
0
Comment
Question by:emi_sastra
  • 7
  • 6
13 Comments
 
LVL 16

Expert Comment

by:DcpKing
ID: 38431192
Are you using SQL Server versions 7 or 2000? If you're using a later version then I'd really strongly suggest you use SSIS - it's incredibly much easier! Even to the extent of suggesting that if this isn't a production item that you get a Developer Edition from Amazon ($50 or so!) if you are using an older version of the database.

hth

Mike
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 38432071
Hi DcpKing,

I use Ms SQL 2005 as you can see from the tags.

How much easier SSIS compare to DTS ?. Please provide link or sample.

Thank you.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 38432186
I just wanted to be sure of your version - many things get posted in several places, and SQL Server 2005 can run DTS packages as well as SSIS ones (it uses SSIS to do it).

Find your database with the data, right-click it, select Tasks, and select Export Data. After the splash screen, you enter the source database - it's where you are!. The next screen will want the destination database, and will show you the same (local) server. Change this to your destination server , put in your credentials for that server, and select the destination database.

After that the wizard should guide you through the process of selecting the tables and/or views that you want to specify. You will be able to save the job, and run it - either immediately or at some later date.

What this has done is build you an SSIS job - you should later be able to go and find it and look at it in the BIDS program.

For reference: http://msdn.microsoft.com/en-us/library/ms140052.aspx

One problem that you may run into is that your installation cannot "see" the other server and databases. In this case you may need to export the data to flat files (select this option at the head of the Destination page) and then import the data after manually transferring the files (right-click on destination database, select Tasks, select Import Data).

hth

Mike
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:emi_sastra
ID: 38433860
- One problem that you may run into is that your installation cannot "see" the other server and databases. In this case you may need to export the data to flat files (select this option at the head of the Destination page) and then import the data after manually transferring the files (right-click on destination database, select Tasks, select Import Data).

I don't get this ? If my SQL Management Studio recognize other server and databases then will be not problem at all ?

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 38433932
What if those server is far away and connected not via VPN connection ?
For example local server to web server and vice versa.

Thank you.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 38434059
For moving files between servers (not data between SQL Servers) ? Just use FTP or SFTP. Rerfer here for a commercial version of secure FTP (SFTP) and look on Wikipedia for a definition of SFTP.

If the data has to come from a table and go to the other server as a file, use the same wizard as explained in order to produce a flat file (.csv, for example) and then there's an FTP task in SSIS to use to send it to the other server.  Unless the data comes from a table there's really no need to use SSIS or DTS except for using SQL Server as a convenient scheduling tool.

Mike
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 38435079
-  For moving files between servers (not data between SQL Servers) ?
I mean the same database engine (MS SQL) but servers are located at different locations and connected using public IP but not VPN connection.

Thank you.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 38435170
Do you have any current experience with querying data in a database on one server when running SSMS, for instance, on the other server? If you have succeeded in using SSMS to connect to the remote server and obtain some data then the method I outlined at first should work. If you're tried and failed then either you need a better connection, or else there's some piece of software (like a firewall) preventing you, or else the connection just wont support TCP/IP, although TCP/IP's a fairly forgiving protocol.

Have you actually tried connecting from one system to the other using SSMS yet?
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 38448065
Sorry, just back.

I usually using link server to do it.

Thank you.
0
 
LVL 16

Accepted Solution

by:
DcpKing earned 2000 total points
ID: 38448754
Then honestly I don't see you having any problems. The following assumes that you've never ever touched SSIS before.

Take a look at the video here and the info here if you've never used SSIS before.

Your SSIS package will be exceedingly simple - one box that pulls in data and one box that pushes it out - rather like K&R's "Hello World" program.  Just start BIDS and select  New Project. From the options choose Integration Services Project, give it a name, and let it create for you. Over on the right you'll get what you see in the SSIS_1 screenshot (mine are from SQL Server 2012, but yours will look the same in content - just a little differently coloured.

Across the top of the middle part of the screen you'll see a list of tabs - the two on the left are where you'll work (ControlFlow and DataFlow). So, start by dragging a Data Flow Task from the toolbox on the far left onto the central screen. Ok? Great! We're done with Control Flow already! Clock on the Data Flow tab and it'll tell you you're working with "Data Flow Task" - the name of the task you just put into the Control Flow window.

Here, drag two thing on - an OLE DB Source and an OLE DB Destination. Once there, connect the green line from the Source to the Destination (picture SSIS_2). Next, we have to connect the Source box to where you're getting your data from and the Destination box to where you want it to go to. As I'm working on a laptop here they'll be the same - you have to add your server names or IP addresses where you need to.

So right-click the Source box and select Edit. It will have a drop list (empty) for the name of the OLE DB Connection Manager, and a button called "New". Press that!  The next dialog should try to offer you a list of the existing connections that you've already made, but, of course, you won't have, so press "New" again. Drop down the Server Name droplist and select your source server, or type it in (probably necessary for you, especially if this is the remote server).  Select
your authentication, and also the database name (see SSIS_3) and test it. Now you can OK out of this. As you're here, add one for the other server in exactly the same way.

After that you'll be back at the Source Editor, and you'll have a name for your connection manager. Select the Data Access Mode (just the table itself, or you can use a stored proc. to provide your data. Finally here, select the table the data will come from. Use OK to get out of this and you can do the same for the Destination box.

Once you're happy, you'll find a green triangle in the button bars that will run your package in debug mode, so you'll probably get errors to start with, but we can solve them!
SSIS-1.png
SSIS-2.png
SSIS-3.png
SSIS-4.png
0
 
LVL 1

Author Closing Comment

by:emi_sastra
ID: 38449679
Great help.

Thank you very much for your help.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 38450063
It's what we're here for :)  keep asking if (when!) you run into more bottlenecks. If I missed anything in the description then please post it here on this thread so anyone reading it will benefit.

Mike
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 38450133
I will.

Thank you very much.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question