How to load the un structured flat files into SQL Server in SSIS

Input file:
"PartNumber"|"PSSBaseUoM"|"PSSBusinessNbr"|"PSSComponentType"|"PSSDetailedDesc"|
"0000"|"P"|""|""|"FOURNITURE DE :"
"0000411420"|"P"|""|"Finished Goods"|"ROND"ELLE DENTELEE"
"0000510040"|"P"|""|"Component"|"INSERT"
"0001"|"P"|""|""|"MATERIEL INFO"RMATIQUE"
"0002"|"P"|""|""|"MAINTENANCE"
"0003"|"P"|""|""|"MOBILIER CONSOMMABLE"
"0004"|"P"|""|""|"OUTILLAGE B.E."
"0005"|"P"|""|""|"CONTROLE"
"002008815"|"P"|""|"Component"|"BOITE "STOCKAGE"
"0044490070"|"P"|""|"Finished Goods"|"COUVERCLE"
"0064137000"|"P"|""|"Finished Goods"|"FLASQUE COMPLET"
"0064140210"|"P"|""|"Component"|"FLAS"QUE"

Output:

PartNumber	PSSBaseUoM	PSSBusinessNbr	PSSComponentType	PSSDetailedDesc
0	P			FOURNITURE DE :
411420	P		Finished Goods	RONDELLE DENTELEE
510040	P		Component	INSERT
1	P			MATERIEL INFORMATIQUE
2	P			MAINTENANCE
3	P			MOBILIER CONSOMMABLE
4	P			OUTILLAGE B.E.
5	P			CONTROLE
2008815	P		Component	BOITE STOCKAGE
44490070	P		Finished Goods	COUVERCLE
64137000	P		Finished Goods	FLASQUE COMPLET
64140210	P		Component	FLASQUE

Solution:

Use the script component as a source and write below C# code to perform the above output.

// C# code

using System;

using System.Data;

using System.IO;    // Added

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

    public override void CreateNewOutputRows()

    {

        int i = 0;

        // Read file (use the FILE connection added in the script component, named myFile)

        using (StreamReader sr = new StreamReader(this.Connections.Connection.ConnectionString, System.Text.Encoding.UTF7)) // Encoding is optional

        {

            String line;

            // Read lines from the file until the end of the file is reached.

            while ((line = sr.ReadLine()) != null)

            {

                if (i != 0)

                {

                    // Split the line into columns

                    string[] columns = line.Split('|');

                    // Add one new row

                    this.Output0Buffer.AddRow();

                    // Fill columns, but check if they exist

                    if (columns.Length > 0)

                    {

                        // Remove the " at the start and end of the string

                        // with a trim or use a substring.

                        //Output0Buffer.PartNumber = columns[0].TrimStart('"').TrimEnd('"');

                        Output0Buffer.PartNumber = columns[0].Contains("\"") ? columns[0].Replace("\"", "") : columns[0];

                    }

                    if (columns.Length > 1)

                    {

                        //Output0Buffer.PSSBaseUoM = columns[1].TrimStart('"').TrimEnd('"');

                        Output0Buffer.PSSBaseUoM = columns[1].Contains("\"") ? columns[1].Replace("\"", "") : columns[1];

                    }

                    if (columns.Length > 2)

                    {

                        //Output0Buffer.PSSBusinessNbr = columns[2].TrimStart('"').TrimEnd('"');

                        Output0Buffer.PSSBusinessNbr = columns[2].Contains("\"") ? columns[2].Replace("\"", "") : columns[2];

                    }

                    if (columns.Length > 3)

                    {

                        //Output0Buffer.PSSComponentType = columns[3].TrimStart('"').TrimEnd('"');

                        Output0Buffer.PSSComponentType = columns[3].Contains("\"") ? columns[3].Replace("\"", "") : columns[3];

                    }

                    if (columns.Length > 4)

                    {

                        // Output0Buffer.PSSDetailedDesc = columns[4].TrimStart('"').TrimEnd('"');

                        Output0Buffer.PSSDetailedDesc = columns[4].Contains("\"") ? columns[4].Replace("\"", "") : columns[4];

                    }

                }

                i++;

            }

        }

    }

}

Finally load the data into your destination.