Load the data from FTP server to SQL Server without download the file

Most of the developers having taught that why do we need to download the file from FTP or Web Service instead of loading the data to SQL table directly. We have a solution for this scenario which explained in this post with source code. We can load the data from FTP flat file to SQL Server table directly without downloading to local drive and also we can use this C# code in SSIS script task to download the same.

Workflow to load the data from FTP server to SQL table

• Connecting the FTP Server/Web Service using WebClient method.
• Consuming the file data as bytes from server and storing into string variable.
• Reading and Parsing the data from string using String Reader and Text Field Parser.
• Converting each rows and column values into Data Set later Data table.
• Finally moving the data from Data table to SQL table using SQL Bulk Copy Method.

using System;
using System.Data;
using System.Linq;
using System.Net;
using System.Text;
using System.Configuration;
using Microsoft.VisualBasic.FileIO;
using System.IO;
using System.Data.SqlClient;

Past the below mentioned code under the void main method.

string fileName = "Employee";
string delimiter = "|";
string url = "ftp://servername/" + fileName + ".TXT";
DataTable dt = new DataTable();
string SQLConnString = ConfigurationManager.ConnectionStrings["LocalDB"].ConnectionString;

WebClient request = new WebClient();
request.Credentials = new NetworkCredential(@"domainname\username", "password");
try
{
    //Downloading the data from the Server and assign to String value
    byte[] newFileData = request.DownloadData(url);
    string fileString = System.Text.Encoding.UTF8.GetString(newFileData);
    //Converting delimited string value to Dataset
    DataSet DataFeed = ConvertFiletoDataSet(fileName, fileString, delimiter);
    dt = DataFeed.Tables[fileName];

    //Loading the data from data table to SQL Table using SQL Bulk Copy
    using (SqlBulkCopy bulkcopy = new SqlBulkCopy(SQLConnString))
    {
        bulkcopy.DestinationTableName = fileName;
        for (int j = 1; j < dt.Columns.Count; j++)
        {
            bulkcopy.ColumnMappings.Add(dt.Columns[j].Caption.ToString(), dt.Columns[j].Caption.ToString());
        }
        bulkcopy.WriteToServer(dt);
    }
}
catch (WebException ex)
{
    Console.WriteLine(ex.Message.ToString());
}

Paste below mentioned code in outside of void main method.

public static DataSet ConvertFiletoDataSet(string fileName, string filestring, string delimiter)
{
try
{
    DataSet dsResult = new DataSet();
    string rowValue;
    char[] delimit = delimiter.ToCharArray();
    int lineNo = 0;

    dsResult.Tables.Add(fileName);
    string[] rowsData = filestring.Split("\n\r".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
    string[] fileHeader = rowsData[0].Split(delimit);
    dsResult.Tables[fileName].Columns.Add("ROW_ID", typeof(int));

    for(int i = 0; i < fileHeader.Length; i++)
    {
        dsResult.Tables[fileName].Columns.Add(fileHeader[i].ToString());
    }

    //Reading each row from the string
    for (int i = 1; i < rowsData.Length; i++)
    {
        rowValue = rowsData[i].Replace("\n\r", "");
        if (!string.IsNullOrEmpty(rowValue))
        {
            lineNo++;
            DataRow newRow = dsResult.Tables[fileName].NewRow();
            TextFieldParser parser = new TextFieldParser(new StringReader(rowValue));
            parser.HasFieldsEnclosedInQuotes = true;
            parser.SetDelimiters(delimiter);
            string[] items = parser.ReadFields();
            newRow["ROW_ID"] = lineNo;

            for (int j = 0; j < fileHeader.Length; j++)
            {
                var data = string.IsNullOrEmpty(items[j]) ? (object)DBNull.Value : items[j];
                newRow[fileHeader[j].ToString()] = data;
            }
            dsResult.Tables[fileName].Rows.Add(newRow);
        }
    }
    return dsResult;
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message.ToString());
    throw;
}
}

Also see below articles

Monitoring and Performance Tuning Tips for SQL Server database
Implement Best Practice and Performance tuning tips in SSIS and ETL Development
SQL Server/Developer Interview Questions and Answers for Experienced Professional
SQL Server/Developer Interview Questions and Answers for Experienced Professional