SSIS Tutorial Part 98-Get File Names from FTP Server and Save to SQL Server Table in SSIS Package

FTP Task in SSIS Video Scenario:

We have different files on FTP Server, we want to get the names of all the files from FTP Server folder and save to our SQL Server Table for Audit Purpose. By doing that multiple times we can get the snapshot of all the files available on FTP Server Folder.

This can be very useful when we are looking for some total files to be there before we start process etc.

we will learn following items in this video

How to Create an SSIS Package from basics
How to create FTP Connection in SSIS Package
How to Create ADO.Net Connection in SSIS Package to Connect to Database
How to Create variable for FTP Folder Name and pass to Script Task
Write Script by using VB.Net in Script Task to Get the List of Files from a Remote FTP Folder and insert the file names to SQL Server Table.
How to change the value of Variable to loop through different folder to extract file name without make any other change in our SSIS Package

Link to the blog post for this video

Check out step by Step SQL Server Integration Services(SSIS) Tutorial by TechBrothers

[Scott_Nei]

Great explanation. Don't worry about it being too long, we can just speed up the playback if we don't have much time.

If someone needs to be watching a video to learn this topic, they probably need the detailed explanation. Developers who don't need the detailed explanation are probably just reading the blog post and copying the script.

[MarkKFerguson]

Great video. Length is perfect. Thank you!

[enigma_mysterium]

Awesome video! Thanks for putting your time and sharing your knowledge!

[jessiesp]

Really love your videos, explained very well.

[cooljsh]

Grat tutorial, but I have a question. How to do that with OleDB Connection ... Y try many ways but all fails :( I hope you could help me

[avinashg]

Just Awesome Bro, I love it and Subscribed.

[nagagannamaneni]

really love the way you explain each and every point in detail. Please share the links if you have any videos for SSAS too... Thanks in advance

[databasemsbi]

when we should use script task? which languange should we learn to write script ?

[harshaldhas]

Really explained well.Just want to know as an SQL Developer do i have to learn VB or .NET to write scripts

[avinashg]

Hi TechBrothersIT,

Before explaining my issue let me explain my requirement, I want to get all file names with it attributes like size, date-modified..etc from a FTP folder which also has sub-folders in it and insert into SQL table.

In that process, I am facing issues related to processing time, my C# code is able to get the desired output but its taking lot of time to execute completely, I want my task to complete within 5 minutes. I am a just beginner in SSIS, can you please help me in this case.

Below is my current code using to iterate folder in FTP.

foreach (string foldername in folderArray)
{

string finalpath = ftppath + '/' + foldername;


ftpClient.GetListing(out finalfolderArray, out finalfileArray);
foreach (string finalfinalnames in finalfileArray)
{

string finalpaths = foldername + "/" + finalfinalnames;




FtpWebRequest request =
request.Method =
request.Credentials = new NetworkCredential(username, password);
request.Proxy = null;
request.KeepAlive = false;
FtpWebResponse response =

FtpWebRequest request1 =
request1.Method =
request1.Credentials = new NetworkCredential(username, password);
request1.Proxy = null;
request1.KeepAlive = false;
FtpWebResponse response1 =


sqlCmd.CommandText = "Insert into dbo.FOLDERDETAILS(";
sqlCmd.CommandText += "[Foldername], [Filename], [LastModified(SystemTimezone)], [Filesize(bytes)])";
sqlCmd.CommandText += " Values('" + foldername + "', '" + finalfinalnames + "', '" + response1.LastModified + "', '" + response.ContentLength + "')";
sqlCmd.ExecuteNonQuery();
}

}

can you post a video how to set up Transactional replication to FTP?

[rustik]

Is it possible to transfer data from ftp to sql server without downloading the files to local machine??? Thank you

[manumys]

Hi, you have explained well in detail. Thanks for this video
can you able to provide us C# script for this and one more this how to check for one-week files are available in the FTP like can it possible to give date range to check the files in the FTP? and how to update file existence into excel file
can you please help on this?

Hi! Can i insert the inform from txt files on ftp server, without download to local path?. Do a diect dump to sql table but no download the file?