?
Solved

SSIS: download and process ftp files

Posted on 2011-03-04
1
Medium Priority
?
733 Views
Last Modified: 2012-05-11
I'm trying to build a package that will download any files from an ftp site. The process consists of getting the file list in the ftp directory, looping through each one filename in the recordset and processing each file individually.

I've got it to a point where I get the file list, if the file count > 0 then perform a ForEach loop which does a ForEach Variable Enumerator based on a recordset containing the names in the remote directory and maps a object variable that stores the remote file name to be processed in the script. This is where I'm stuck. In the script I can't seem to access the value of the remote filename.

Does anyone done a similar routine and can provide info or links to reference material on how to accomplish this, or how can I extract the remote filename data I need?
0
Comment
Question by:wppiexperts
1 Comment
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 35038858
I don't think you can access a file on a FTP site directly from SSIS or anything else for that matter until you get it on the computer where you need to access it. What I'm tyrying to say is that after you get the filelist from FTP site you should run som FTP script to get the file localy then you could read their content one by one to put them in some SQL table. You should be able to do that via SFTP as nobody wants FTP open on a SQL server box. Is this what you are trying to do?
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question