How to deal with special characters when using textfieldparser in SSIS script task

See more:

C#

SSIS

I have an SSIS package to deal with multiple files, multiple types of delimiters, different column names, no of columns different etc.. Now, I have an issue while loading special characters into db from csv. My target table has only nvarchar fields. If I include N'' prior to the data it shows some different character than the original data. Otherwise, it shows ?. As I'am expecting files with different delimiters, sometimes text qualifier, sometimes not, code page issue etc., using script task to load data.

Source data:
testdata - testdata - testdata

When N'':
testdata � testdata � testdata

When using encoding:

testdata testdata testdata

What I have tried:

public void Main()
{
    // TODO: Add your code here

    string delimiter = Dts.Variables["User::var_Delimiter"].Value.ToString();
    string TableName = Dts.Variables["$Package::Param_Tbl_Name"].Value.ToString();
    string filePath = Dts.Variables["User::var_File_Path"].Value.ToString();
    string fileName = Dts.Variables["User::var_File_Name"].Value.ToString();
    string srcfolder = Dts.Variables["$Package::Param_Src_Folder"].Value.ToString();
    string tbl = TableName.Replace("[", "");
    tbl = tbl.Replace("]", "");
    tbl = tbl.Replace("dbo.", "");
    string fieldname_file = "";
    string fieldname_tbl = "";
    string fieldnames = "";
    string query = "";
    int counter = 0;
    string line = "";
    int maxcol = 0;
    int maxcol1 = 0;
    
    SqlConnection myADONETConnection = new SqlConnection();
    myADONETConnection = (SqlConnection)Dts.Connections["ADOAUDIT"].AcquireConnection(Dts.Transaction) as SqlConnection);
    SqlCommand sqlCommand = null;

    using (TextFieldParser parser = new TextFieldParser(filePath, System.Text.Encoding.GetEncoding("iso-8859-8"));))
    {
        parser.TextFieldType = FieldType.Delimited;
        parser.SetDelimiters(delimiter);
        parser.HasFieldsEnclosedInQuotes = true;
                    
        while (!parser.EndOfData)
        {
            string[] fieldrow = parser.ReadFields();

            if (counter == 0)
            {
                for (int i = 0; i < fieldrow.Length; i++)
                {
                    fieldname_file = fieldrow[i];
                    if (!String.IsNullOrEmpty(fieldname_file))
                    {
                        fieldname_file = fieldname_file.Substring(0, 6);
                        query = "select name from sys.columns " +
                                "where object_id = (select object_id from sys.objects where name = '" + tbl + "')" +
                                "and name like '%" + fieldname_file + "%'";
                        sqlCommand = new SqlCommand(query, myADONETConnection);
                                    fieldname_tbl = (string)sqlCommand.ExecuteScalar();

                        if (!String.IsNullOrEmpty(fieldname_tbl))
                        {
                            fieldnames = fieldnames + "[" + fieldname_tbl.Replace("'", "''") + "],";
                            maxcol = i;
                        }
                    }
                }

                query = "select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS" +
                        " where table_name ='" + tbl + "' " +
                        "and column_name like '%file%name%'";
                        sqlCommand = new SqlCommand(query, myADONETConnection);
                        fieldname_tbl = (string)sqlCommand.ExecuteScalar();

                //fieldnames = fieldnames + "[FileName]";
                fieldnames = fieldnames + "["+fieldname_tbl+"]";
            }
            else
            {
                query = "";
                line = "";
                if (fieldrow.Length == maxcol + 1)
                {
                    for (int j = 0; j < fieldrow.Length; j++)
                    {
                        line = line + "N'" + fieldrow[j].Replace("'", "''") + "',";
                    }
                }
                else
                {
                    for (int j = 0; j < fieldrow.Length - (fieldrow.Length - (maxcol + 1)); j++)
                    {
                        line = line + "N'" + fieldrow[j].Replace("'", "''") + "',";
                        maxcol1 = j;
                    }
                }

                line = line + "'" + fileName + "'";
                query = "insert into " + tbl + "(" + fieldnames + ") values(" + line + ")";
                sqlCommand = new SqlCommand(query, myADONETConnection);
                sqlCommand.ExecuteNonQuery();
            }

            counter++;

        }
    }