We have to use a VPN to connect to a remote system to download data, then upload it to a local server.

The VPN client software closes all other ports while connected, so none of the local servers can be seen (except for the database on the VPN client computer). The client software also requires the password to be entered manually for every connection. The steps required to complete this process required alternating between the remote system and a local server several times, and DTS did not seem to offer any way to do the logins.

We worked around this by writing a small Visual Basic program to connect and disconnect the VPN sofware, using SENDKEYS to enter the password. This program is invoked at appropriate points in the DTS package by Execute Process tasks.

Where there's a will, there's a way.