Running the Transfer Engine from a SQL Job

You can use SGL function sp_OACreate to create a TEConnection object. SQL runs ftpte and hides all windows. SQL uses a special instance of ftpte exe, which cannot prompt for user name and password.

Specify a local download path or filename. While optional, it's a good idea to make sure that it is going where you intend. Also, make sure that the local path has the appropriate NTFS permissions to allow the TE to have full control.

For scripted or scheduled tasks use an otherwise unused account for scheduled TE tasks. Once the CuteFTP TE (transfer engine) is started, another instance cannot be created. Windows treats the logged-in user differently than the logged-out-user and DCOM errors may occur. For instance, if a recurring background process has already run before the user logs in, once the user logs in and the background process starts again, a DCOM error will occur.

Example Transfer Script

DECLARE @property varchar(255)

DECLARE @object int

DECLARE @hr int

DECLARE @src varchar(255), @desc varchar(255)

PRINT '---start'

EXEC @hr = sp_OACreate 'CuteFTPPro.TEConnection', @object OUT

EXEC @hr = sp_OASetProperty @object, 'Host', 'ftp.mysite.com'

EXEC @hr = sp_OAGetProperty @object, 'Host', @property OUT

PRINT @property

EXEC @hr = sp_OAGetProperty @object, 'Login', @property OUT

PRINT @property

EXEC @hr = sp_OAGetProperty @object, 'Protocol', @property OUT

PRINT @property

EXEC @hr = sp_OAMethod @object, 'Connect'

EXEC @hr = sp_OAMethod @object, 'download', NULL, '/pub', "e:/eee"

EXEC @hr = sp_OAGetProperty @object, 'Log', @property OUT

PRINT @property

EXEC @hr = sp_OADestroy @object

PRINT '---finish'

Configuration Notes

  • SQL cannot use an already running instance of ftpte exe. The CuteFTP interface cannot communicate with an ftpte instance created by SQL.

  • When a scheduled task runs as user X while user X is logged in, the profile path will be set to that user's directory (C:\Documents and Settings\X\). However, when a scheduled task runs as user X while user X is *not* logged in, the profile path will be set to the default user directory (C:\Documents and Settings\Default User\). Therefore, the desired SSL certificates from C:\Documents and Settings\X\Application Data\Globalscape\CuteFTP\certs.crt should be copied to C:\Documents and Settings\Default User\Application Data\Globalscape\CuteFTP\certs.crt

  • If you use UseProxy, be sure to specify any proxy information. Please refer to Properties for more information.

  • Add a couple of log lines so that it can write to the log whenever something happens successfully. Add it after the connect line, so that you can see how far it gets.
  • To make sure you are connecting long enough to get data, use the GetList method to write a list to a local file.