Number of query values and destination fields are not the same

Question

Hello friends,

I received text (.txt) files from our external vendor every morning.

My objective is to convert this text files to excel (.xlsx) per our customer's request.

I am using SSIS Package, and I am using Script Task to do my conversion from text files to excel.

I am getting this error message "Number of query values and destination fields are not the same".

I am not sure what I did wrong in my Script Task. Thank you in advance for any help you can provide.

Here is my code in my Script Task:

{
    // TODO: Add your code here
    try
    {
        //Declare Variables
        string SourceFolderPath = Dts.Variables["User::SourceFolderPath"].Value.ToString();
        string DestinationFolderPath = Dts.Variables["User::DestinationFolderPath"].Value.ToString();
        string FileExtension = Dts.Variables["User::FileExtension"].Value.ToString();
        string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();
        string CreateTableStatement = "";
        string ColumnList = "";
        //Reading file names one by one
        string SourceDirectory = SourceFolderPath;
        string[] fileEntries = Directory.GetFiles(SourceDirectory, "*" + FileExtension);
        foreach (string fileName in fileEntries)
        {
            // do something with fileName
            //MessageBox.Show(fileName);
            //MessageBox.Show("create table statement here" + CreateTableStatement.ToString());
            //Read first line(Header) and prepare Create Statement for Excel Sheet
            System.IO.StreamReader file = new System.IO.StreamReader(fileName);
            string filenameonly = (((fileName.Replace(SourceDirectory, "")).Replace(FileExtension, "")).Replace("\\", ""));
            CreateTableStatement = (" Create Table [" + filenameonly + "] ([" + file.ReadLine().Replace(FileDelimiter, "] Text,[")) + "] Text)";
            file.Close();
            //Construct ConnectionString for Excel
            //Provider=Microsoft.ACE.OLEDB.16.0 is the excel driver
            //Provider=Extended Properties=\"Excel 12.0 is the excel version to use 
            MessageBox.Show("After Connection String");
            string connstring = "Provider=Microsoft.ACE.OLEDB.16.0;" + "Data Source=" + DestinationFolderPath + "\\" + filenameonly
                + ".xlsx;" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
            //string connstring = "Provider=Microsoft.ACE.OLEDB.16.0;" + "Data Source=" + DestinationFolderPath + "\\" + filenameonly
            //    +  + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
            MessageBox.Show("After Connection String" + connstring.ToString());
            OleDbConnection Excel_OLE_Con = new OleDbConnection();
            OleDbCommand Excel_OLE_Cmd = new OleDbCommand();
            //drop Excel file if exists
            MessageBox.Show("About to drop Excel File if exists");
            File.Delete(DestinationFolderPath + "\\" + filenameonly + ".xlsx");
            //MessageBox.Show("After drop Excel File if exists");
            Excel_OLE_Con.ConnectionString = connstring;
            //MessageBox.Show("Did it fail here");
            Excel_OLE_Con.Open();
            //MessageBox.Show("Did it fail after Open the excel file");
            Excel_OLE_Cmd.Connection = Excel_OLE_Con;
            //MessageBox.Show("Drop Excel File successfully");
            //Use OLE DB Connection and Create Excel Sheet
            Excel_OLE_Cmd.CommandText = CreateTableStatement;
            //MessageBox.Show("Create table successfully");
            Excel_OLE_Cmd.ExecuteNonQuery();
            //Writing Data of File to Excel Sheet in Excel File
            int counter = 0;
            string line;
            System.IO.StreamReader SourceFile =
            new System.IO.StreamReader(fileName);
            while ((line = SourceFile.ReadLine()) != null)
            {
                if (counter == 0)
                {
                    MessageBox.Show("Did it come here");
                    ColumnList = "[" + line.Replace(FileDelimiter, "],[") + "]";
                }
                else
                {
                    string query = "Insert into [" + filenameonly + "] (" + ColumnList + ") VALUES('" + line.Replace(FileDelimiter, "','") + "')";
                     MessageBox.Show(query.ToString());
                    var command = query;
                    Excel_OLE_Cmd.CommandText = command;
                    Excel_OLE_Cmd.ExecuteNonQuery();
                }
                counter++;
            }
            Excel_OLE_Con.Close();
            SourceFile.Close();
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
    catch (Exception exception)
    {
        // Create Log File for Errors
        using (StreamWriter sw = File.CreateText(Dts.Variables["User::DestinationFolderPath"].Value.ToString()
            + "\\" + "ErrorLog_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".log"))
        {
            sw.WriteLine(exception.ToString());
            Dts.TaskResult = (int)ScriptResults.Failure;
        }
    }

Answer 1

Does your code only make errors when processing a subset of files or does it all fail?

I'm speculating on a possible cause of the error, when the number of fields in a row of the TXT file is not the same as the first line:

The length of 'line. Replace(FileDelimiter, "','")' and 'ColumnList' is not equal.

eample: ColumnList = "[A, B, C]"; line. Replace(FileDelimiter, "','") = "1, 2, 3, 4" or "1, 2"

I don't know how you would handle such TXT content, but if you assume that the header order for each row is the same, It can be handled like this:

When the data is short, an empty string is added.

When the data is long, remove the redundant strings

（This is a code reference, you may need to rewrite the appropriate code for your language version）

string dataStr = line.Replace(FileDelimiter, "','");

string[] columnNames = ColumnList.Split(new string[] { "],[", "[", "]" }, StringSplitOptions.RemoveEmptyEntries);

string[] dataValues = dataStr.Split(',');

if (columnNames.Length != dataValues.Length)
{
    if (dataValues.Length < columnNames.Length)
    {
        Array.Resize(ref dataValues, columnNames.Length);
        for (int i = dataValues.Length - columnNames.Length; i < columnNames.Length; i++)
        {
            dataValues[i] = "";
        }
    }
    else if (dataValues.Length > columnNames.Length)
    {
        Array.Resize(ref dataValues, columnNames.Length);
    }
}

string query = "Insert into [" + filenameonly + "] (" + ColumnList + ") VALUES('" + string.Join("','", dataValues) + "')";
Excel_OLE_Cmd.CommandText = query;
Excel_OLE_Cmd.ExecuteNonQuery();

You may need to check with your users to see if you can handle potentially non-compliant TXT data files in this way (especially if we have removed redundant data).