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.
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.
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.
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.
Try it out by testing the connection. The message below is posted if it is successfully.
Then set it up as a Pre-Script on the Textfile table we created.
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.
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=18.104.22.168, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
at SSISShared.Shared.CreatePackage_Ftp(IFtpSource ftpSource, ILogger logger, ISSISEngine ssisEngine)
at TimeXtender.DataManager.StepFtpTaskDeploy.DoAbstractStep(StepSetup stepSetup)
We are using version 22.214.171.124 on a stand alone server (no SSIS or SSAS installed).
Just to inform others, in order to have the pre/post scripts available on a table, the table cannot be in simple mode