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

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

  • 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

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

  • 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

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

  • 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.

  • 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.

  • 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?

  • 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]

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

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