0

I have a requirement to develop a tool .

I am confused to use SSIS or windowsservice.

Please find below the requirement.

I need to fetch data from database.The data will be saved in txt /csv format.

The text file needs to be encrypted with PGP(Pretty Good Privacy).

I will have only public key to use for encryption/decryption.

After PGP encryption the "EmpInfo.txt.pgp" document will be placed on the Secure FTP site using Protocol: SSH/SFTP

The above data feed document will be scheduled monthly once

Could you please let me know which is the best way to do it i.e using SSIS/windows service.

If there is any other way please let me know.

Thyagi
  • 71
  • 1
  • 10
  • 2
    I'd rule out creating a Windows Service - if integrating data encryption and SFTP upload is too irksome in SSIS just create a command line exe and schedule it using the Windows Task Scheduler. Windows Services are a hassle to debug and you don't need to run it permanently which would be the only reason to have a Windows Service. – Filburt Apr 20 '17 at 14:27
  • @Filburt is right. For a start, see http://stackoverflow.com/q/24945709/850848 – Martin Prikryl Apr 21 '17 at 05:54
  • Looks like you are overcomplicating the things. You can take one of the existing libraries, such as our SecureBlackbox or IPWorks. Both offer OpenPGP and SFTP functionality. SecureBlackbox is more flexible, IPWorks has simpler API. – Eugene Mayevski 'Callback Apr 30 '17 at 16:53

1 Answers1

0

SSIS can be used for this task, but you need licences for CozyRoc(third party tool) which provides PGP encryption/decryption (OpenPGP Task). SQL server for deploying the SSIS ispac file.

  1. To fetch data from database to CSV: DataFlowTask can be used, inside which you can specify Source Database (OLE DB Source) and Flat File destination (csv file).
  2. Text file needs to be encrypted with PGP(Pretty Good Privacy): CozyRoc tool OpenPGP Task can be used to encrypt the files.
  3. Document will be placed on the Secure FTP: File Transfer Task (CozyRoc) can be used for this purpose.
  4. Data feed document will be scheduled monthly once: Once the ispac file is deployed on the server you can create a job for the deployed project and job can be configured to run at a particular time.

Note: SFTP task (CozyRoc) is deprecated, alternate for this is File Transfer Task.

observer
  • 316
  • 3
  • 9