Number of query values and destination fields are not the same

Villa 231 Reputation points
2024-09-19T18:56:39.9633333+00:00

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;
        }
    }
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,600 questions
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
540 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
11,054 questions
{count} votes

Accepted answer
  1. flyer-me 100 Reputation points
    2024-09-20T03:38:51.8466667+00:00

    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:

    example

    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).

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.