Remove unwanted double quotes or Special characters from the CSV Files using Script task in SSIS

In the previous post we see how to remove the Text Qualifier from the CSV files and load the file to the database.

Read Here:  Remove double quotes from CSV filein SSIS

https://bageshkumarbagi-msbi.blogspot.com/2021/03/remove-double-quotes-from-csv-file-in.html

Let see if we get unwanted Text Qualifier in the file, in this case, we can’t handle using the above approach.

See the file.  

In this file text qualifier is “” but in the SalesOrderNumber The column we are getting one more text Qualifier * and we need to remove the * and load into the database.

If we use “in this case also we are getting the * in this column. See below.

 

See the preview.  

Still getting the * in the column value.

We can remove these using two ways

·         Using script task

·         Using Derived Column Transformation

Using Script task

We will take the Script task and read the file and replace the * with blank value. Then we will use the DFT and load this file.

To Replacing the * with blank space we are using the below code.

Script task code.

public void Main()                                 {             try             {                 string filepath = @"H:\SSIS1\Source\Sales.csv";                 var fileContents = System.IO.File.ReadAllText(filepath);                 fileContents = fileContents.Replace("*", "");                   System.IO.File.WriteAllText(filepath, fileContents);                 Dts.TaskResult = (int)ScriptResults.Success;             }             catch (Exception ex)             {                 throw ex;             }                                 }

It will correct the file after that we are taking the data flow task to load the file into the database.

   

Our package looks like below

  

Now we are running this package.

See the record in the table.

  

Now executing the package.

Package executed successfully.

 

See the file first of all, it replaces the * with a blank value.

 

After that, we are loading this file using the DFT.

Records in the table. 

Data loaded successfully in the table.

Using Derived Column Transformation

We can also achieve the same using the Derived column transformation. In this transformation, we can replace the unwanted Qualifier with bank value.

First of all, we will use the Text Qualifier from the flat file.  

Now we are taking the derived column transformation to replace the unwanted value with blank space.

  

(DT_STR,30,1252)REPLACE(LTRIM(RTRIM(SalesOrderNumber)),"*","")

Now we are taking the OLEDB destination and doing the mapping.  

Package is ready to run.

See the file data.  

Data in the table. 

Running the package now.

 

See the records in the table.

 

Data loaded successfully.