SFTP, encrypt or compress data files in SSIS using custom components

Question

SFTP, encrypt or compress data files in SSIS using custom components

Stan Kulp-439977

SSChampion

Points: 10062
More actions
March 12, 2014 at 8:25 pm

#290909

Comments posted to this topic are about the item SFTP, encrypt or compress data files in SSIS using custom components

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

jbuchan SSC-Addicted Points: 422 More actions · Answer 1

I have just completed a project on this myself but I hadn't used those components as I didn't know about them at the time.

I used WinSCP automation

http://winscp.net/eng/docs/library_install

and Bouncy castle decryption runtime

http://www.bouncycastle.org/csharp/

I then had it all in script tasks

Eric Heilman Old Hand Points: 385 More actions · Answer 2

When using custom components like this, how can you deploy the SSIS packages to a SQL Server? Does the custom compenent also need to be installed on the server?

My team has looked into this before, but always hit a roadblock with the server installation side of things.

jbuchan SSC-Addicted Points: 422 More actions · Answer 3

It would need to be installed on the remote server as well.

Assemblies etc, need to be installed to the GAC for the package to be able to access them correctly.

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 4

I have found a pretty reliable way to install assemblies to the GAC.

First, get your hands on a copy of Version 3.5.30729.1 of gacutil.exe.

Next, copy gacutil.exe into the same folder that contains the DLL.

Lastly, open up a command console, move to the folder containing the DLL, and execute the command:

>gacutil -i assembly.dll

This way you don't have to worry about paths or different versions of gacutil.exe that might exist on the server.

However, in this particular case the extensions come with an installer that works quite well. All you have to do is run the installer on the server(s) where you want to deploy them.

boriskey Mr or Mrs. 500 Points: 521 More actions · Answer 5

great post, I bookmarked it for my next project. I was also looking at scripting with winscp but I think this approach is more SSIS friendlier that custom scripting and calling winscp.

on a side note, it is a shame that microsoft still does not have native support for sftp in 2012 version.

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 6

jbuchan (3/13/2014)
I have just completed a project on this myself but I hadn't used those components as I didn't know about them at the time.
I used WinSCP automation
http://winscp.net/eng/docs/library_install
and Bouncy castle decryption runtime
http://www.bouncycastle.org/csharp/
I then had it all in script tasks

I wrote an article a while back on SFTP using puTTY from a script task.

http://www.sqlservercentral.com/Contributions/Edit/69931

It works, but I found its reliability to be wanting. Popping up a command console in the middle of executing an SSIS package just doesn't seem to work as well as it should. I found it was failing about one time out of ten. That's when I started looking for a replacement and stumbled upon this set of extensions.

jbuchan SSC-Addicted Points: 422 More actions · Answer 7

I just installed it via Inno Setup, wrote a quick installer using bits from the web and it worked fine as well. It also made it easier for me to repeat the install/ uninstall process for testing

Stan Kulp-439977 (3/13/2014)
I have found a pretty reliable way to install assemblies to the GAC.
First, get your hands on a copy of Version 3.5.30729.1 of gacutil.exe.
Next, copy gacutil.exe into the same folder that contains the DLL.
Lastly, open up a command console, move to the folder containing the DLL, and execute the command:
>gacutil -i assembly.dll
This way you don't have to worry about paths or different versions of gacutil.exe that might exist on the server.
However, in this particular case the extensions come with an installer that works quite well. All you have to do is run the installer on the server(s) where you want to deploy them.

jbuchan SSC-Addicted Points: 422 More actions · Answer 8

If you are confident in c# the WinSCP automation is quite powerful and stable.

If I didn't have to have had to apply so many business rules I would have used your method though, bookmarked it for future reference.

Stan Kulp-439977 (3/13/2014)
jbuchan (3/13/2014)
I have just completed a project on this myself but I hadn't used those components as I didn't know about them at the time.
I used WinSCP automation
http://winscp.net/eng/docs/library_install
and Bouncy castle decryption runtime
http://www.bouncycastle.org/csharp/
I then had it all in script tasks
I wrote an article a while back on SFTP using puTTY from a script task.
It works, but I found its reliability to be wanting. Popping up a command console in the middle of executing an SSIS package just doesn't seem to work as well as it should. I found it was failing about one time out of ten. That's when I started looking for a replacement and stumbled upon this set of extensions.

yuping.su SSC Rookie Points: 35 More actions · Answer 9

I have been waiting Microsoft to provide sFTP support in SSIS. Apparently, the community responded faster! I have re-blogged this post with full credit and link/reference to this author. Thanks so much! :w00t::-P:-D

artvandelay Grasshopper Points: 13 More actions · Answer 10

Are there any best practices for storing a private key passphrase?

jbuchan SSC-Addicted Points: 422 More actions · Answer 11

artvandelay (3/13/2014)
Are there any best practices for storing a private key passphrase?

Encrypt it!

We store our private key file on a secure location which we can limit by the process of the SQL Agent account. You could store the pass phrase config securely with it also.

lv42daze100 SSC Rookie Points: 31 More actions · Answer 12

Great article and content thank you. I've been scripting this behavior for so long.

However, I am unable to install the SSIS extensions. During installation, I am prompted that the application requires SSIS 2008 R2 and forces exit. My Integration Services version is 10.50.4000.

I recycled all SQL services and rebooted several times to no avail. Any advice on how to resolve this would be greatly appreciated as I could really uses these Tasks.

Thanks.

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 13

lv42daze100 (3/13/2014)
Great article and content thank you. I've been scripting this behavior for so long.
However, I am unable to install the SSIS extensions. During installation, I am prompted that the application requires SSIS 2008 R2 and forced exite. My Integration Services version is 10.50.400.
I recycled all SQL services and rebooted several times to no avail. Any advice on how to resolve this would be greatly appreciated as I could really uses this Tasks.
Thanks.

When I open SQL Server Management Studio and click "Help-About" I see this:

When I do the same in Visual Studio I see this:

Do you see anything substantially different?

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 14

lv42daze100 (3/13/2014)
Great article and content thank you. I've been scripting this behavior for so long.
However, I am unable to install the SSIS extensions. During installation, I am prompted that the application requires SSIS 2008 R2 and forced exite. My Integration Services version is 10.50.400.
I recycled all SQL services and rebooted several times to no avail. Any advice on how to resolve this would be greatly appreciated as I could really uses this Tasks.
Thanks.

If you can't get this one to work, try this other codeplex custom task component:

SSIS SFTP Task Control Flow Component[/url]