Number of query values and destination fields are not the same

Question

Hi All,

I received the TXT files from my vendor every morning.

My objective is to convert this TXT file into Excel (.xlsx) file for my internal customer.

I would appreciate it if you can point me in the right direction to resolve the issue.

Thank you;

I am using Visual Studio 2022, Script Task within SSIS, I received the following error message:

System.Data.OleDb.OleDbException (0x80040E14): Number of query values and destination fields are not the same.

#region Help: Introduction to the script task

/* The Script Task allows you to perform virtually any operation that can be accomplished in

• a .Net application within the context of an Integration Services control flow.
• Expand the other regions which have "Help" prefixes for examples of specific ways to use
• Integration Services features within this script task. */

#endregion

#region Namespaces

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

using System.IO;

using System.Data.OleDb;

#endregion

namespace ST_83b752633ba143a8b1933e51fbcbb5dc

{

/// <summary>

/// ScriptMain is the entry point class of the script.  Do not change the name, attributes,

/// or parent of this class.

/// </summary>

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]

public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

{

    #region Help:  Using Integration Services variables and parameters in a script

    /* To use a variable in this script, first ensure that the variable has been added to 

     * either the list contained in the ReadOnlyVariables property or the list contained in 

     * the ReadWriteVariables property of this script task, according to whether or not your

     * code needs to write to the variable.  To add the variable, save this script, close this instance of

     * Visual Studio, and update the ReadOnlyVariables and 

     * ReadWriteVariables properties in the Script Transformation Editor window.

     * To use a parameter in this script, follow the same steps. Parameters are always read-only.

     * 

     * Example of reading from a variable:

     *  DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;

     * 

     * Example of writing to a variable:

     *  Dts.Variables["User::myStringVariable"].Value = "new value";

     * 

     * Example of reading from a package parameter:

     *  int batchId = (int) Dts.Variables["$Package::batchId"].Value;

     *  

     * Example of reading from a project parameter:

     *  int batchId = (int) Dts.Variables["$Project::batchId"].Value;

     * 

     * Example of reading from a sensitive project parameter:

     *  int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();

     * */

    #endregion

    #region Help:  Firing Integration Services events from a script

    /* This script task can fire events for logging purposes.

     * 

     * Example of firing an error event:

     *  Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);

     * 

     * Example of firing an information event:

     *  Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)

     * 

     * Example of firing a warning event:

     *  Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);

     * */

    #endregion

    #region Help:  Using Integration Services connection managers in a script

    /* Some types of connection managers can be used in this script task.  See the topic 

     * "Working with Connection Managers Programatically" for details.

     * 

     * Example of using an ADO.Net connection manager:

     *  object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);

     *  SqlConnection myADONETConnection = (SqlConnection)rawConnection;

     *  //Use the connection in some code here, then release the connection

     *  Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);

     *

     * Example of using a File connection manager

     *  object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);

     *  string filePath = (string)rawConnection;

     *  //Use the connection in some code here, then release the connection

     *  Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);

     * */

    #endregion

    /// <summary>

    /// This method is called when this script task executes in the control flow.

    /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

    /// To open Help, press F1.

    /// </summary>

    public void Main()

    {

        // 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 

                //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.12.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("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;

            }

        }

    }

    #region ScriptResults declaration

    /// <summary>

    /// This enum provides a convenient shorthand within the scope of this class for setting the

    /// result of the script.

    /// 

    /// This code was generated automatically.

    /// </summary>

    enum ScriptResults

    {

        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,

        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

    };

    #endregion

}
```}