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.

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.

Loading the Ragged Right file data in the table using Flat file connection Manager in SSIS

 Ragged Right File:

This file is similar to the fixed-width file only the difference is that this format file allows the last line to be variable length.

Some time we are not sure that the length of a column and we are extracting the data in the fixed file in this case we can put the column at the last and store the data in the fixed files.

See the below file.  

See the mapping  

Name: 0-20

Mobile: 21-35

Address: 36- variable Length

Let’s see how to load this file into the table.

Taking DFT and source as a flat-file.

  

Selecting the Format as Ragged Right.

In the Columns we are mapping the column.

 

In the advance we can see the list of columns with the length.

  

The preview we can see the data. 

Now taking Destination as OLEDB and doing the mapping.

 

The package is ready to run.

See the records in the table.

  

Running the package. 

Data loaded successfully.

See the records in the table

  

Data loaded successfully.

Loading the fixed length file data in the table using script component transformation in SSIS

We can load the fixed length file In DFT using the Script component.

In this demo we will see how to load this file in DFT using the script component transformation.

Below the file. 

Mapping of this file

Name: 0-19

Mobile: 19-34

Address: 34-60

Table where we are storing this data. 

Taking DFT and taking Source as Flat file and creating the connection.

  

Now we are taking the Script Component Transformation and selecting as transformation.

  

Click ok.

Now double Click on the script component  

And Select Input Columns.

Here we can select the list of input columns.

There are two types of Usage type  ReadOnly (We can’t modify the input) and other one is Read Write.

Now Come to Inputs and outputs table and need to create the output column here.

  

Here we are renaming the Input column name and creating the output column name.

Now come to the script tab and click on the edit script.

  

Writing the below code in the script component.

We need to write the code in the function Input0_ProcessInputRow

public override void Input0_ProcessInputRow(Input0Buffer Row)     {         /*          * Add your code here          */         string name = Row.NMA.Substring(0, 19).ToString();         Row.Name = name;         string mob = Row.NMA.Substring(19, 15).ToString();         Row.Mob = mob;         string emp_add = Row.NMA.Substring(34, 25).ToString();         Row.EmpAdd = emp_add;     }

 Build and close this window.

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

 

Now our package is ready to run.

Before run see the records in the table. 

Now running the package.   

Package executed successfully.

See the records in the table.

  

Data loaded successfully.