I have created an SSIS package for downloading file from FTP server. When I debug the package, everything goes fine. But when I shedule the package as Job step, it sometimes fails to connect. The job is sheduled to repeat every 4 hours and in average every other run fails with Error: 0xC002918F - The Login request was denied . The connection is anonymous and doesn't require password I have tested it for a few hours now and found out some info: When tested in FileZilla, I found out that the server denies second connection trying to download a file. Setting maximal number of concurrent connections to 1 got it working in FileZilla FTP command in command line works fine. Maybe because it closes connection after every successful command In FTP connection manager, I didn't found the option to limit number of connections I have no possibility to see the settings of the FTP server. What confuses me the most is the fact, that some sheduled tasks work fine and some fail. I haven't found any regularity in it. What I have tried so far: Active/pasive mode Limiting retries in FTP connection manager to 1 Shifting the shedule time, so it doesn't collide with any other (older) tasks trying to download from the server Setting the FTP task to ASCII mode (The server says to FileZilla, that it only accepts ASCII characters) No luck so far. Still some attemps success and some not. EDIT: The FTP server is on Simatic device. For this week, i kept shedule for every 1 hour and kept pinging the device every 30 seconds. Still some SSID attempts don't succeed and ping goes through every time :-/

Reputation: 551

SSIS: FTP task - concurrent connections

I have created an SSIS package for downloading file from FTP server. When I debug the package, everything goes fine. But when I shedule the package as Job step, it sometimes fails to connect. The job is sheduled to repeat every 4 hours and in average every other run fails with Error: 0xC002918F - The Login request was denied.

The connection is anonymous and doesn't require password

I have tested it for a few hours now and found out some info:

When tested in FileZilla, I found out that the server denies second connection trying to download a file. Setting maximal number of concurrent connections to 1 got it working in FileZilla
FTP command in command line works fine. Maybe because it closes connection after every successful command
In FTP connection manager, I didn't found the option to limit number of connections

I have no possibility to see the settings of the FTP server. What confuses me the most is the fact, that some sheduled tasks work fine and some fail. I haven't found any regularity in it.

What I have tried so far:

Active/pasive mode
Limiting retries in FTP connection manager to 1
Shifting the shedule time, so it doesn't collide with any other (older) tasks trying to download from the server
Setting the FTP task to ASCII mode (The server says to FileZilla, that it only accepts ASCII characters)

No luck so far. Still some attemps success and some not.

EDIT:

The FTP server is on Simatic device.

For this week, i kept shedule for every 1 hour and kept pinging the device every 30 seconds. Still some SSID attempts don't succeed and ping goes through every time :-/

Upvotes: 12

Answers (5)

Bryan

Reputation: 3451

Formerly the error was known as:

'0xC002918F',-1073573489,'DTS_E_FTPTASK_UNABLETOCONNECTTOSERVER','Unable to connect to FTP server using "__".'

The FTP Task explicitly closes the connection at the end of action execution, so if the connection stays open it must be something with your server.

Set the FTP Connection UsePassiveMode property to True by checking the appropriate checkbox in the FTP connection properties dialog box.
Or http://microsoft-ssis.blogspot.com/2011/08/foreach-ftp-file-enumerator.html

Both CozyRock and PragmaticWorks have fantastic FTP tasks for SSIS.

Guidance listed on Microsoft's website

https://support.microsoft.com/en-us/kb/925880

https://technet.microsoft.com/en-us/library/bb794745.aspx

Aside from firewall/permissions/restrictions Check FTP credentials or to be exact how they saved/retrieved. The rule of thumb in that case is to rely on SQL Server, i.e. to use "Rely on server storage and roles for access control" package protection level. This approach should remove all FTP credentials problems altogether (excluding mistypos of course).

However, This is the default setting applied when using Project Deployment Model to store packages in the SSISDB catalog which is where the package you noted resides.

Do not use XP_CMDSHELL and call external programs without a business necessity as the above poster advises. Enabling CMDSHELL is a significant security risk.

Upvotes: 2

FLICKER

Reputation: 6683

In my experience, sometimes FILE LOCK and DB CONNECTION do not terminate even after ending the package execution.

I suggest you use Script Task and do the upload using C# code and make sure you close the FTP connection at the end.

Upvotes: 1

Troy Witthoeft

Reputation: 2676

Shot in the dark, would you mind trying to update the package and set DelayValidation=True?

Validation routines establish connections to test them. Perhaps the validation routine is the other concurrent connection? Worth a shot?

Upvotes: 0

Mike Honey

Reputation: 15037

I would replace the standard SSIS FTP Task with an Execute Process Task, and call WinSCP.

https://winscp.net/eng/docs/guide_automation

I've found WinSCP is much more reliable and flexible. You can pass it a command line built using expressions to cover all the functionality of the SSIS FTP task, and more.

Upvotes: 4

SinisterPenguin

Reputation: 1618

I think you have to accept just using a workaround in this scenario, maybe set the SQL agent SSIS job step to retry after an X seconds delay (advanced tab of SQL job step). We do this a lot when connecting to flaky cloud API's with SSIS.

Or maybe a for loop container in your package which continues on failure & breaks out on success - maybe try 3 times to connect?

Sorry if this is all obvious to you already.

Upvotes: 3

SSIS: FTP task - concurrent connections

Answers (5)

Related Questions