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. }