September 19, 2005 at 2:24 pm
A newbie question, I know, but I'm wondering if somebody would please explain to me how to connect to a local SQL Server database from a remote SQL Server database in order to import data.
First, I'd like to use the DTS import wizard for a one time import of data into my lookup tables. Then I intend to create a DTS package to be executed for scheduled imports. Unfortunately, I don't know how to connect to the local database from the remote database (I'm connected to the remote database using Remote Desktops).
I'd appreciate any help or resources you can provide. Thanks in advance for your time.
September 19, 2005 at 2:37 pm
I'm sorry I don't have time to detail all about connections here. In the meantime, check out http://www.sqldts.com. Under Tutorials they have a good tutorial called Building a Package in the DTS Designer (http://www.sqldts.com/default.aspx?278).
Overall, SQLDTS.com is very good - lots of useful info.
September 20, 2005 at 1:34 am
I'll add a bit more because this did confuse me when I started with DTS. The simple thing to do is create a new package with the designer, and add a connection object for each database. For SQL, use the OLE DB connection object - it's easy to set up if you don't mind hardcoding the connection details (portability is a whole new ball game).
Then click on the Transform Data Task in the tool box, click on the source connection and then on the destination. The transform data task should automatically match fields if their names match. This won't get you a very portable or efficient transformation but it will get you started down an interesting road.
BTW the bit I found really hard to grasp that first time was that the transform task looks superficially like workflow (and can have workflow properties) but does lots more on the way. Connections look like tasks but aren't, but from the look of the designer you can have workflow from a connection to another task. Actually it's workflow from the transformation. Once I got over this, productivity rose to "monkey with typewriter" level.
HTH
Bill.
September 20, 2005 at 8:13 am
Hi,
The wizard should prompt you for the source and destination database connection properties. If you can successfully connect to your local server, the source, that should be saved with the package.
September 20, 2005 at 8:29 am
Thanks a lot for the replies. However, I'm afraid I didn't do a good job of asking my question. I apologize for that.
I do know how to create a connection with DTS. My specific problem is creating a connection to a database located on a remote server (one that's not part of the same network where I'm building the DTS package). Maybe I'm asking for the impossible???
I guess another way of asking this question is is it possible to register an instance of SQL Server that's located on a remote server and is not networked to the instance of SQL Server that I'm trying to import to?
Thanks again for any help!
September 20, 2005 at 8:59 am
If you can't ping it, I doubt you can get DTS to connect. If you can connect to it via Query Analyzer from your local machine, then you should be able to set up a connection.
September 20, 2005 at 10:03 am
In the source dialog of the import wizard, it will allow you to type the IP address of the server.
September 20, 2005 at 10:21 am
Sadly, I cannot ping at this time. However, our network engineer is on the case. Soon, my problem will be resolved.
Thanks again for the help.
September 20, 2005 at 10:24 am
Actually, I'd like to answer my own question for others that might read this later. I'm told that we'll have to add the remote IP to the firewall to allow access, as well as open up port 1433 to allow access to the database. Once this is complete, if all goes well, I should be able to register the remote server.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply