Problems in SSIS FTP Task

Have you faced any problems in SSIS FTP Task connectivity? Has the FTP Task stopped working in production and worked locally? Are you able to connect from browser/ftp client tools and not from SSIS?

I did face this problem and so did my colleagues. It took some effort and time in explaining my problem to infrastructure team so that they could help me to identify what could be wrong. I did think that it could be an issue with firewall but wanted to confirm it. It was a simple fix in the end and was interesting to know the reason for failure.

I thought of sharing this as many other people I know faced the same problem and this time I was happy to guide them and save a lot of precious time and effort.

I am sure you are facing the same problem and hence you have landed on this page. So let me first tell you the fix and then explain what is happening.

Solution to this problem is to set up the SSIS FTP Task connection object to connect in Passive mode.

How to do it? – Open the SSIS package and in the “connection managers” double click the FTP connection object. In Options - select “Use passive mode”. Now run the package and it should work without any errors.

The SSIS FTP Task uses the Active mode by default and in a Firewall environment setup this does not work. . The Browser FTP client uses the passive mode by default and hence it works.

What’s happening under the hood? Here are my notes.

o    FTP is purely a TCP based service

o    FTP uses 2 ports for communication:-

§  Command Port (usually Port 21)

§  Data Port (usually Port 20. Depending on the mode, it may or may not use 20)

o    FTP has 2 modes

§  Active mode

§  Passive mode

In Active mode

o    Client contacts FTP Server’s Command Port (21) from a random port (N>1023, say 1026). This is clients command port. It also sends Information on the port it will listen to (for data) (say 1029 command=PORT 1029)

o    FTP server sends an ACK back to port (1026). A connection is established.

o    FTP server initiates a connection (from port 20 its data port) to port 1029 (client. Earlier sent info).

o    FTP server now establishes a connection with this port and sends the data using this port.

Firewall Issue:

If a firewall is set up at client, then a request from FTP in active mode will be treated as some external source trying to connect to client and therefore will reject/block the request. This is because the client just sent the port info that will it will listen to and it did not really open a connection.

In Passive mode

·         Client contacts FTP Server’s Command Port (21) from a random port (N>1023, say 1026). This is clients command port. Client now issues PASV command instead of PORT command.

·         Server then replies with from port 21 to 1026 telling the client the port number it is listening to for data connection.

·         Client then initiates data connection from its data port (say 1029) to servers data port 1024 (instead of data port 20)

·         Server finally sends ACK to client’s data port 1029 from server’s data port 1024.

Reference article: http://slacksite.com/other/ftp.html (really well explained)

Comma seperated values (SQL)

This code generates comma separated values for an entity having multiple values. i.e. Multiple rows into a single row data value (comma seperated).

Found this code very useful for data validations in datawarehouse and thought of sharing this.

Here is the code snippet 

Create Table #TempData (EmployeeName Varchar(50), EmployeeNickName Varchar(50))

Insert Into #TempData Values('Vinuthan','VB') Insert Into #TempData Values('Vinuthan','Vinu') Insert Into #TempData Values('Vinuthan','Newton') Insert Into #TempData Values('Venus','Ven') Insert Into #TempData Values('Venus','Vens')

 Query to get comma seperated values

SELECT DISTINCT EmployeeName,   STUFF   (                 (                                 SELECT ',' + EmployeeNickName  FROM                                  #TempData a WHERE b.EmployeeName = a.EmployeeName  FOR XML PATH('')                 )                 ,1,1,''   ) EmployeeNickName  FROM  #TempData b

Result: