Follow

Connect to Text files from an FTP server

We have the script feature called FTP Source. That can be used to extract files from a FTP server. It can be added from the script section in the business unit area.

You will be able to achieve the same by using the CData CSV provider, while pointing to a FTP server. Additionally it can connect to a SFTP data source.

All is described here CData CSV Provider

Set up an FTP Source

The first thing though is to get one of the text/csv files from the FTP server and download it to a specific folder. The folder needs to be located where it will always be available and the developer user and service user needs to have rights to this folder.

Set up a multiple text files data source

The setup could be like this.

FTP_0.PNG

Start by connecting directly to one file, then go to the columns pane and pull in all the fields and set up data types for them. Then change the file location manually so it writes *.GL33-Exp.csv; instead. The post processing choice should be considered. I would use Backup and store the files in another folder after it is done processing.

FTP_1.PNG

Do a deploy / execute of the data source.

Add the FTP Source

Then add a FTP Source. It is created as a script action, but is only available in the Business Unit.

FTP_3.PNG

To set up the FTP Source the server name and a valid user account is needed. What is really important is the area i marked red. You need to know the folder and it should always start with /, the folder you chose in the previous step and the file name as well.

FTP_4.PNG

Try it out by testing the connection. The message below is posted if it is successfully.

FTP_5.PNG

Then set it up as a Pre-Script on the Textfile table we created.

FTP_6.PNG

Other issues to take into account

Sometimes the text files in the FTP server are temporary. To avoid data loss you might need to set the table up to run as a history table. It should be set up with natural keys and then all the other fields as type 1.

FTP_7.PNG

Was this article helpful?
0 out of 0 found this helpful

2 Comments

  • 0
    Avatar
    Hugo Winkelhorst

    What are the prerequisites to get this up and running?

    When trying to deploy the script action I get an error: 

    Could not load file or assembly 'Microsoft.SqlServer.FtpTask, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
    Module: SSIS2016
    System.IO.FileNotFoundException
    at SSISShared.Shared.CreatePackage_Ftp(IFtpSource ftpSource, ILogger logger, ISSISEngine ssisEngine)
    at TimeXtender.DataManager.StepFtpTaskDeploy.DoAbstractStep(StepSetup stepSetup)

    We are using version 19.2.4.64 on a stand alone server (no SSIS or SSAS installed).

  • 0
    Avatar
    Joeri De Valck

    Just to inform others, in order to have the pre/post scripts available on a table, the table cannot be in simple mode

    Edited by Joeri De Valck
Please sign in to leave a comment.