none
SSIS FTP Task for File Transfer Requirements RRS feed

  • Question

  • I would like to move file from another server to SQL Server with FTP. What are requirements from source server?

    Server must Support FTP? Or could any Windows Server serve as FTP Server?

    What are requirements for destination server, which is SQL and SSIS server? Just SSIS FTP is needed? No FTP installations?


    Kenny_I

    Monday, April 14, 2014 8:44 PM

Answers

  • The source server would need to have an FTP server installed.  If the source server is a windows server, you can use the FTP services provided in IIS.  You can review the setup instructions at http://technet.microsoft.com/en-us/library/cc771012(v=ws.10).aspx

    There are numerous other options for FTP servers, both free of charge and for purchase.  I would recommend performing further research outside the SSIS forums for more information pertaining specifically to FTP services. 

    You will need an installation of SQL Server Integration Services wherever you plan on running the SSIS FTP task.  The final destination of the files being transmitted/received do not necessarily need to be on the server running SSIS.  Please refer to http://technet.microsoft.com/en-us/library/ms137656.aspx for more information about the SSIS FTP task.

    -D

    Tuesday, April 15, 2014 3:36 AM
  • Source: If IIS is installed you already have the FTP server, but may need to configure. Otherwise resort to 3-rd party tools.

    Intermediate step, SSIS: A regular SSIS FTP Task handles such file transfer no problem. Also uploads data to destination - your SQL Server. Typically uses OLEDB.


    Arthur My Blog

    Tuesday, April 15, 2014 5:24 PM
    Moderator
  • Hi Kenny_I,

    If you have already had a FTP server that stores the source files, you don’t need to setup FTP server on either the SSIS server or the destination SQL Server. If you haven’t had a FTP server, you need to setup one to store the source files. The FTP Task in SSIS cannot extract the data from the source files on the FTP server directly, so you need to first download the source files to a shared folder or a local folder on the SSIS server. Then, in a Data Flow Task, you use Excel Source/Flat File Source/OLE DB Source and on to extract data from the source files according to the type of the source files, and use an OLE DB Destination to export the data to SQL Server table. Besides, you may need the Foreach Loop Container to loop through the source files in the staging folder rather than use multiple Source and Destination adapters.

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    Wednesday, April 16, 2014 2:21 AM
    Moderator

All replies

  • The source server would need to have an FTP server installed.  If the source server is a windows server, you can use the FTP services provided in IIS.  You can review the setup instructions at http://technet.microsoft.com/en-us/library/cc771012(v=ws.10).aspx

    There are numerous other options for FTP servers, both free of charge and for purchase.  I would recommend performing further research outside the SSIS forums for more information pertaining specifically to FTP services. 

    You will need an installation of SQL Server Integration Services wherever you plan on running the SSIS FTP task.  The final destination of the files being transmitted/received do not necessarily need to be on the server running SSIS.  Please refer to http://technet.microsoft.com/en-us/library/ms137656.aspx for more information about the SSIS FTP task.

    -D

    Tuesday, April 15, 2014 3:36 AM
  • Here's a blog I've written on performing secured FTP using SSIS. I used free client WinSCP for that

    HTTP://visakhm.blogspot.in/2012/12/implementing-dynamic-secure-ftp-process.html

    
    
    

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    

    • Edited by Visakh16MVP Tuesday, April 15, 2014 4:24 AM
    Tuesday, April 15, 2014 4:21 AM
  • Source: If IIS is installed you already have the FTP server, but may need to configure. Otherwise resort to 3-rd party tools.

    Intermediate step, SSIS: A regular SSIS FTP Task handles such file transfer no problem. Also uploads data to destination - your SQL Server. Typically uses OLEDB.


    Arthur My Blog

    Tuesday, April 15, 2014 5:24 PM
    Moderator
  • Hi Kenny_I,

    If you have already had a FTP server that stores the source files, you don’t need to setup FTP server on either the SSIS server or the destination SQL Server. If you haven’t had a FTP server, you need to setup one to store the source files. The FTP Task in SSIS cannot extract the data from the source files on the FTP server directly, so you need to first download the source files to a shared folder or a local folder on the SSIS server. Then, in a Data Flow Task, you use Excel Source/Flat File Source/OLE DB Source and on to extract data from the source files according to the type of the source files, and use an OLE DB Destination to export the data to SQL Server table. Besides, you may need the Foreach Loop Container to loop through the source files in the staging folder rather than use multiple Source and Destination adapters.

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    Wednesday, April 16, 2014 2:21 AM
    Moderator