Load a flat file with any wild card character like # or $ etc. in SSIS using Script component Transformation

We are getting the CSV File which column and data are separated from 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 this demo we will see who we load this file using script component transformation. Previously we saw how to load such types of files using the Script task.

Let’s see

Here we are taking the Data Flow task and taking Source as a flat-file.

 

Now We are taking the Script component and select as the Transformation.

Now creating the column in the input and output table.

 

Also declare the data type of the column.

Now in the go the script tab and click on the edit script and writing the below code.

public override void Input0_ProcessInputRow(Input0Buffer Row)     {         try         {             string[] val = Row.SalesOrderNumberSalesAmountUnitPriceExtendedAmountTaxAmt.Split('*');             string SalesOrderNumber = val[0].ToString();             Row.SalesOrderNumber = SalesOrderNumber;             decimal SalesAmount = decimal.Parse(val[1].ToString());             Row.SalesAmount = SalesAmount;             decimal UnitPrice = decimal.Parse(val[2].ToString());             Row.UnitPrice = UnitPrice;             decimal ExtendedAmount = decimal.Parse(val[3].ToString());             Row.ExtendedAmount = ExtendedAmount;             decimal TaxAmt = decimal.Parse(val[4].ToString());             Row.TaxAmt = TaxAmt;             DateTime Created_Date = DateTime.Now;             Row.CreatedDate = Created_Date;             string created_by = "Bagesh";             Row.createdby = created_by;         }         catch (Exception ex)         {             throw ex;         }            }

 Build this code and close this window.

Now taking destination as OLEDB.

  

The package is ready to run.

See the records in the table

 

Now running this package.  

Package executed successfully.

Now see the records in the table.

  

Data loaded successfully.