Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SFTP, encrypt or compress data files in SSIS using custom components Expand / Collapse
Author
Message
Posted Wednesday, March 12, 2014 8:25 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 11:11 AM
Points: 130, Visits: 937
Comments posted to this topic are about the item SFTP, encrypt or compress data files in SSIS using custom components
Post #1550512
Posted Thursday, March 13, 2014 3:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 9:50 AM
Points: 144, Visits: 281
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
Post #1550591
Posted Thursday, March 13, 2014 5:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 30, 2014 9:27 AM
Points: 139, Visits: 105
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.
Post #1550628
Posted Thursday, March 13, 2014 5:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 9:50 AM
Points: 144, Visits: 281
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.
Post #1550630
Posted Thursday, March 13, 2014 6:29 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 11:11 AM
Points: 130, Visits: 937
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.
Post #1550653
Posted Thursday, March 13, 2014 6:34 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 10, 2014 7:54 AM
Points: 24, Visits: 222
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.
Post #1550656
Posted Thursday, March 13, 2014 6:37 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 11:11 AM
Points: 130, Visits: 937
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.
Post #1550660
Posted Thursday, March 13, 2014 6:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 9:50 AM
Points: 144, Visits: 281
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.
Post #1550662
Posted Thursday, March 13, 2014 6:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 9:50 AM
Points: 144, Visits: 281
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.

[url=http://www.sqlservercentral.com/Contributions/Edit/69931][/url]

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.
Post #1550663
Posted Thursday, March 13, 2014 7:22 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 1:54 PM
Points: 3, Visits: 56
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!
Post #1550687
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse