SSH tunnel and Port Forwarding via SSIS Script Task Plus

[Michael Moore]

We recently had a client deliverable that involved pulling data from a MySQL instance.  This also required us to open an SSH tunnel in order to establish the connection.  This post is to help anyone else out that experiences a similar scenario and gets stuck the way we did.

We have been COZYROC subscribers for a number of years.  A lot of our usage was limited to their awesome object like Zip Task, OpenPGP Task and Secure FTP Task.  Most of our team consists of database developers and DBAs who could be considered a bit old school.  Our familiarity with C# and VB Script is not as strong as it could/should be, but we know enough to be dangerous.  So when this project came around, we tried multiple methods including Putty and the COZYROC SSH Execute Task. 

We hit a wall.  We tried everything we could think of.  Even Google failed in providing an easy little plug-and-play solution...

And then we found this:

http://www.cozyroc.com/script/secure-tunnel-task

Granted, we are a little dense, so at first we couldn't figure out what the heck we were supposed to do with it.  With a little help, we realized that the plug-and-play solution we had been looking for was right in front of our face.

Here are the steps we used to implement the solution that finally worked for us:

Step 1

Downloaded the appropriate script file for our environment and saved it in a common folder on our development server.  Initially, it was saved as a .xml file, so we have to change the extension to .stx11:

Step 2

We added a Script Task Plus to our package.  Under the Advanced tab, we clicked on the (chain)link and pointed it to the .stx11 file we downloaded.

Step 3

Back on the Setup, clicked on dropdown for SSHConnection and created a new connection manager.  We did not enable any proxy or advanced settings.  We simply provided the host IP, port, username and password.  Upon successfully testing the connection, we clicked OK

Step 4

This was the only part that seemed a bit backwards to us, but back on the Setup tab, we had to flip the parameters for Local and Remote address.  What we considered our Local IP went into the Remote Address, and the Client/Vendor IP went in the Local Address.  We assumed that this was because once the tunnel is opened, it sees us as local on the client/vendor side.

Step 5

We created a MySQL Connection manager using the Remote Address value (2.2.2.2:80)

Step 6

We created a dataflow task to pull/extract the data

Fairly easy-peasy