Using SSIS I need to retrieve data from a server outside my network/domain.
I can only get to this server through a VPN.
I created 2 packages:
- StartVPN - using some VB this package starts the VPN. Works great. :)
- Import Files - This package is called from StartVPN and should import some data.
When I run package 2 directly with the VPN already started this package runs great.
When I run package 2 from package 1 without the task that starts the VPN but with the VPN manually started this package runs great.
However, if I call this package from package 1 it fails with the error:
The AcquireConnection method call to the connection manager "MyConnection" failed with error code 0xC0202009.
It does not matter if the VPN was already started or not.
How can I runn package 2 with the VPN only running during execution of the package?
I solved it!
I needed to add a wait between package1 (starting the VPN) and package2 (doing the import)
After setting up the VPN, package1 was made to wait 5 secs before continuing. Now everything works swell :)
SO: Package 1 containg a VB scriptask for starting up the (existing) VPN:
Dim VPNConnectionName As String = "MyVPN"
Dim VPNlogin As String = "MyUser"
Dim VPNPassword As String = "MyPass"
Shell("RASDIAL " & Chr(34) & VPNConnectionName & Chr(34) & " " & VPNlogin & " " & VPNPassword, vbNormalFocus)
'
System.Threading.Thread.Sleep(5000)
Dts.TaskResult = ScriptResults.Success
Then from package1 call package 2 for the actual import
And a VB scripttask for closing the VPN:
Dim VPNConnectionName As String = "MyConnection"
Shell("RASDIAL " & Chr(34) & VPNConnectionName & Chr(34) & " /DISCONNECT", vbNormalFocus)
'
Dts.TaskResult = ScriptResults.Success