How To Read CSV Or Text File In C# (DataSet/DataTable)

  1. Create a new SSIS Package.
  2. Add a Control Flow Task named as "Script Task".
  3. Double click on Script Task.
  4. Create 3 variables as given below.

Add the "All User" variable in ReadOnlyVariables and click OK.



Click "Edit Scripts". It will open the C# code, like given below.

Go to "Add References" folder in Project>>Add References>> Microsoft.VisualBasic>>OK.



Copy and paste the given below code.
  1. public void Main() {  
  2.     // TODO: Add your code here  
  3.     string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");  
  4.     try {  
  5.         string SourceFolderPath = Dts.Variables["User::SourceFolderPath"].Value.ToString();  
  6.         string FileExtension = ".csv";  
  7.         string FileDelimiter = Dts.Variables["User::DestinationFolderPath"].Value.ToString();  
  8.         //string TableName = Dts.Variables["User::DestinationTable"].Value.ToString();  
  9.         string ArchiveFolder = Dts.Variables["User::FileDelimited"].Value.ToString();  
  10.         //string ColumnList = "";  
  11.         //Reading file names one by one  
  12.         string SourceDirectory = SourceFolderPath;  
  13.         string[] fileEntries = Directory.GetFiles(SourceDirectory, "*" + FileExtension);  
  14.         DataSet ds = new DataSet();  
  15.         DataTable dt = new DataTable();  
  16.         foreach(string fileName in fileEntries) {  
  17.             SqlConnection myADONETConnection = new SqlConnection();  
  18.             myADONETConnection = (SqlConnection)(Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection);  
  19.             dt = GetDataTabletFromCSVFile(fileName);  
  20.             if (dt.Rows.Count > 0) {  
  21.                 //Write your business logic here.  
  22.             }  
  23.         }  
  24.     } catch (Exception exception) {  
  25.         // Create Log File for Errors  
  26.         using(StreamWriter sw = File.CreateText(Dts.Variables["User::LogFolder"].Value.ToString() + "\\" + "ErrorLog_" + datetime + ".log")) {  
  27.             sw.WriteLine(exception.ToString());  
  28.             Dts.TaskResult = (int) ScriptResults.Failure;  
  29.         }  
  30.     }  
  31. }  
  32. private DataTable GetDataTabletFromCSVFile(string path) {  
  33.     StreamReader MyStreamReader = new StreamReader(path, System.Text.Encoding.Unicode);  
  34.     DataTable csvData = new DataTable();  
  35.     try {  
  36.         using(TextFieldParser csvReader = new TextFieldParser(path)) {  
  37.             csvReader.SetDelimiters(new string[] {  
  38.                 ","  
  39.             });  
  40.             csvReader.HasFieldsEnclosedInQuotes = true;  
  41.             string[] colFields = csvReader.ReadFields();  
  42.             foreach(string column in colFields) {  
  43.                 DataColumn serialno = new DataColumn(column);  
  44.                 serialno.AllowDBNull = true;  
  45.                 csvData.Columns.Add(serialno);  
  46.             }  
  47.             while (!csvReader.EndOfData) {  
  48.                 string[] fieldData = csvReader.ReadFields();  
  49.                 DataRow dr = csvData.NewRow();  
  50.                 //Making empty value as empty  
  51.                 for (int i = 0; i < fieldData.Length; i++) {  
  52.                     if (fieldData[i] == null) fieldData[i] = string.Empty;  
  53.                     dr[i] = fieldData[i];  
  54.                 }  
  55.                 csvData.Rows.Add(dr);  
  56.             }  
  57.         }  
  58.     } catch (Exception ex) {}  
  59.     return csvData;  
  60. }