Click here to Skip to main content
14,693,741 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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++;

        }
    }
Posted
Updated 27-Mar-18 2:05am
v3
Comments
#realJSOP 27-Mar-18 5:59am
   
The � character is there because the character that it's trying to represent there couldn't be decoded. Can you show us a portion of the file that's generating that character?
Richard Deeming 27-Mar-18 6:55am
   
Also, check the encoding of the source file - are you absolutely sure it's ISO-8859-8?

1 solution

Don't use the encoder you specified. Windows uses UTF16 by default, so you shouldn't have to specify an encoder unless something is weird in the file.

Just try it and see if it makes a difference.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900