Greetings Everyone,

I am a newbie to DTS and have a little tricky requirement (at least for me)
and I need solution for the same. Here's my situation....

=> I have 2 diff. databases (Oracle & SQL Server DB) residing on 2 diff.
servers at 2 diff. physical location.

=> Need to schedule a DTS package (which will run every night) to import
data from Oracle DB into SQL DB.

=> Need to import data using DTS over VPN (Virtual Private Network). FYI,
the VPN connection has to be established before execution of DTS package
begins and this process (of establishing VPN connection) has to be
automated.

=> Once the data has been imported from Oracle DB into SQL DB, I need to
disconnect the VPN connection explicitly.

Now for me the tricky part is automating process of establishing VPN
connection before the DTS package is executed and then once the import
process is over, it should disconnect VPN connection. Now my questions is :

Is it possible to automate process of establishing VPN connection before the
scheduler executes DTS package? If so then how do I do it?

Any help / ideas / suggestions will be appreciated.

Thanks,
Urmil

Hello Urmil

You probably could if you used the execute process task in DTS. Two assumptions I make is that connection can be made to the VPN using the exe file of the software used to make the connection and that you can save the password used to make the connection. If you are to use a secure id card, you cannot perform this operation...obviously...just thought I'd mention

hth


----- Urmil Muni wrote: ----

Greetings Everyone

I am a newbie to DTS and have a little tricky requirement (at least for me
and I need solution for the same. Here's my situation...

=> I have 2 diff. databases (Oracle & SQL Server DB) residing on 2 diff
servers at 2 diff. physical location

=> Need to schedule a DTS package (which will run every night) to impor
data from Oracle DB into SQL DB

=> Need to import data using DTS over VPN (Virtual Private Network). FYI
the VPN connection has to be established before execution of DTS packag
begins and this process (of establishing VPN connection) has to b
automated

=> Once the data has been imported from Oracle DB into SQL DB, I need t
disconnect the VPN connection explicitly

Now for me the tricky part is automating process of establishing VP
connection before the DTS package is executed and then once the impor
process is over, it should disconnect VPN connection. Now my questions is

Is it possible to automate process of establishing VPN connection before th
scheduler executes DTS package? If so then how do I do it

Any help / ideas / suggestions will be appreciated

Thanks
Urmi

Well like I said... I am a newbie to DTS (as well as VPN) so am not sure if
whether a VPN client for this was required or not. But from yr reply it
seems that I do need to have that installed. Just fyi, as of now I don't
have any VPN client app installed on my machine. Can you suggest any good
s/w for that and are there any good freeware VPN clients avaliable ?



"K" <anonymous (AT) discussions (DOT) microsoft.com> wrote

file of the software used to make the connection and that you can save the
password used to make the connection. If you are to use a secure id card,
you cannot perform this operation...obviously...just thought I'd mention.

Windows Server has limited VPN capabilities available with the RAS
component, which does not require any additional client component built
in, but you would better seeking advice on a VPN solution from a
security solution provider. You could of course try one of the Windows
networking or security newsgroups as well.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Darren Green <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

I have been able to create a DTS package which establishes VPN
connection and runs fine when executed explicitly thru SEM (SQL
Enterprise Manager). But when scheduled, it doesn't work. What do I
need to do to make this work? Am I missing something here?

Yr prompt reply will be highly appreciated.

Thanks in advance.

Urmil

In message <e83bb2b7.0405190358.50c50a36 (AT) posting (DOT) google.com>, Um Junk
<umjunk (AT) hotmail (DOT) com> writes
When scheduled you often change the execution location (Development
workstation to Server), and also the security context (logged on user -
you to SQL Server agent service account).

http://support.microsoft.com/default...074&Product=sq
l2k

http://support.microsoft.com/default...982&Product=sq
l2k

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org