SSIS Script Task To FTP Files: Different Behavior on execute in SSMS Job Activity Monitor and manual execute

0
votes

i have a problem while excecute a ssis package with a script task for ftp transfer files. While executed by the SQL Servers Job Agent the tranfered files have a zero byte size and are totaly empty. when i execute the package manualy out of the ssis catalog in sql server 2012 it works fine. also with the account of the SQL Job Agent it works.

Localy in my Visual Studio 2012, the package is also transfer the complete file to the ftp site.

The vb code for FTP is pretty simple:

Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
cm.Properties("ServerName").SetValue(cm, Dts.Variables("User::X_exporttargetserver").Value.ToString)
cm.Properties("ServerUserName").SetValue(cm, Dts.Variables("User::X_exportuser").Value.ToString)
cm.Properties("ServerPort").SetValue(cm, "21")
cm.Properties("ChunkSize").SetValue(cm, "1000")
cm.Properties("Retries").SetValue(cm, "1")
ftp.Connect()
 Dim files(0) As String
 files(0) = Dts.Variables("User::X_exportfilename").Value.ToString
 ftp.SendFiles(files, Dts.Variables("User::X_exporttargetfilename").Value.ToString, True, False)
ftp.Close()

Does anybody know if this is a script task issue or is a problem of the sql agent?

2
sqlsql-servervb.netssisftp

2 Answers

0
votes

Give it a try by setting TimeOut value as,

cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout

You can enable logging so that you can get more insight about data while sql agent is executing job. Just check what's happening during the FTP session using Microsoft Network Monitor

Try it by changing - Creating step in job, choose run as 32-bit mode.

0
votes

i found the solution for my issue. on the UNC path to the local share i was set the permissions for my user and the sql job agent.

by adding permissions of the ssis service account to this share, the package is execute correctly.