0

I have inherited some SSIS Packages written against SQL Server 2008. Among these is one offending package that is fetching files from a small device via FTP. It is creating a new connection for each file. Which overwhelms the device quickly.

In efforts to fix this I found multiple references on using the RetainSameConnection property. However I have gone through the properties and it is not available to set. I have not been able to find how to via searching either. Or if it is available in SQLServer 2008

So my question is simply, How can I get at that property? Or what version does it become available if not in SQL Server 2008?

FTP Connection

Kirks
  • 3
  • 3

2 Answers2

0

So to answer the central question here: the RetainSameConnection property isn't available for the FTP Connection Manager.

Without diving into why the device is being overwhelmed by FTP requests (requests are pretty cheap in both I/O and CPU, after all), one suggestion would be to use an Execute Process task that calls a command-line FTP client (such as Window's native ftp) and uses a batch file to pass in the arguments to send across to the FTP server to (for example) copy an entire remote directory down to a local directory.

Kyle Hale
  • 7,912
  • 1
  • 37
  • 58
  • As I suspected.. Thanks for the alternative path, I'll try that to pull the files. As for the why the device is overwhelemed it is a small ARM based data acquisition device (AcquiSuite A8812) and the current process is making 3-6 connections /sec. when it fires – Kirks Mar 05 '13 at 01:40
0

Standard:

Standard FTP Connection does not have RetainSameConnection option.

Alternative option 1:

You could use Script Task to transfer files your local folder and FTP website. Here are couple of SO question that relate to FTP transfer through Script Task.

How to avoid SSIS FTP task from failing when there are no files to download?

How to write stored procedure output directly to a file on an FTP without using local or temp files?

Alternative option 2:

You could make use of the SFTP Task sold by third party vendor CozyRoc. Personally, I have not used the component but it does come with the RetainSameConnection property. Read the section Data streaming at the end of the page where they state this option.

Community
  • 1
  • 1
  • I just read about that CozyRoc solution. Interesting, but the cost.. As Kyle pointed out I'm going to try the script path solution. – Kirks Mar 05 '13 at 01:41