Load a flat file with any wild card character like # or $ etc. in

 We are getting the CSV File which column and data are separated with any wild card character like !,@,#,$,%,^,&,*,?,\,/  etc.

See the files below.  

In health care domain some time we are getting such type of flies and we need to load this file to the database.

We are loading the flat file or CSV files in the database using the flat file connection manager. But this source has some limitations.

  

Here we are not getting the * delimiter.

For loading such type of file either we need to use Script task or script component transformation.

Let’s in In this demo we will see who we load this file using Script task and the next post we will see how we load this same file using the script component task.

Now taking script task 

We are writing the below code

public void Main()                                 {             try             {                 // TODO: Add your code here                 char[] delimiters = new char[] { '*' };  //CSV file with * seperated                 DataTable dt = new DataTable();                 using (StreamReader reader = new StreamReader("H:\\SSIS1\\Source\\Sales.csv")) //Reading the CSV file                 {                       string[] headers = reader.ReadLine().Split('*'); // Adding the Header into the data table                     foreach (string header in headers)                     {                         dt.Columns.Add(header);                     }                     dt.Columns.Add("Created_Date");  //Adding Created_Date column in the header                     dt.Columns.Add("Created_by");   // Adding created_by column in the header                     while (!reader.EndOfStream)                     {                         string[] rows = reader.ReadLine().Split('*');                         DataRow dr = dt.NewRow();                         for (int i = 0; i < headers.Length; i++)                         {                             dr[i] = rows[i];                         }                         dr["Created_date"] = DateTime.Now; //Adding Created_Date                         dr["created_by"] = "Bagesh";  //Adding Created_by                         dt.Rows.Add(dr);                       }                 }                   //  creating connection to the data base                 string connection = "Data Source=BAGESH-PC\\BAGESHDB;database=Test;integrated Security=SSPI;";                 SqlConnection con = new SqlConnection(connection);                 //create object of SqlBulkCopy which help to insert                  SqlBulkCopy obj = new SqlBulkCopy(con);                   //assign Destination table name                  obj.DestinationTableName = "sales_Stg";                   //Mapping the Source column with destination column.                 //Keep in mind we need to provide the correct mapping otherwise it will throw an error                 obj.ColumnMappings.Add("SalesOrderNumber", "SalesOrderNumber");                 obj.ColumnMappings.Add("SalesAmount", "SalesAmount");                 obj.ColumnMappings.Add("UnitPrice", "UnitPrice");                 obj.ColumnMappings.Add("ExtendedAmount", "ExtendedAmount");                 obj.ColumnMappings.Add("TaxAmt", "TaxAmt");                 obj.ColumnMappings.Add("Created_Date", "Created_Date");                 obj.ColumnMappings.Add("created_by", "created_by");                 //Opining the connection                 con.Open();                 //insert bulk Records into DataBase.                  obj.WriteToServer(dt);                 //Closing the conntion                 con.Close();                 Dts.TaskResult = (int)ScriptResults.Success;             }                 //If any error comes loging that error             catch (Exception ex)             {                 Dts.Events.FireError(0, "Fire Error", "An error occurred: " + ex.Message.ToString(), "", 0);                   Dts.TaskResult = (int)ScriptResults.Failure;             }                                 }

 

Build and close the window.

Our package is ready to run.

Before run see the records in the table.

 

Now loading the file into the database.

 

Package executed successfully.

See the records in the table.  

Data loaded successfully.