CODEWITHSUNDEEP
c#sql-serverssissqlcommandscript-task
Dinesh Kumar
Dinesh Kumar

Reputation: 1

Syntax error (missing operator) in query expression in C# script task in SSIS

I get this error:

System.Data.OleDb.OleDbException (0x80040E14): Syntax error (missing operator) in query expression ''O'Brien','O'Brien''.

at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at ST_fc2b814c7d4843308992fd44a2168035.ScriptMain.Main()

This is the code I am using this query in C# script:

string queryString = "SELECT * FROM " + SchemaName + "." + TableName + " with(nolock) WHERE mfid IN " + "('"+ MFID + "')" +" AND activeflag = 'Y' AND ga_id NOT IN ('DPN') ORDER BY EmpID, ga_id";

C# script:

try
{
    // Declare Variables
    string MFID = Dts.Variables["User::MFID"].Value.ToString();
    string ExcelPROD =MFID + Dts.Variables["User::ExcelPROD"].Value.ToString();
    string FolderPath = Dts.Variables["User::FolderPath"].Value.ToString()+MFID+"\\";

    OleDbConnection Excel_OLE_Con = new OleDbConnection();
    OleDbCommand Excel_OLE_Cmd = new OleDbCommand();

    //Construct ConnectionString for Excel
    string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + FolderPath + ExcelPROD
                    + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";

    // Drop Excel file if exists
    File.Delete(FolderPath + "\\" + ExcelPROD + ".xlsx");

    // Use ADO.NET Connection from SSIS Package to get data from table
    SqlConnection myADONETConnection = new SqlConnection();
    myADONETConnection = (SqlConnection)(Dts.Connections["PROD"].AcquireConnection(Dts.Transaction) as SqlConnection);

    string SchemaName = "dbo";
    string TableName = "MASTER_DATA_EMPLOYEE";

    // Load Data into DataTable from SQL ServerTable
    // Assumes that connection is a valid SqlConnection object.
    string queryString = "SELECT * FROM " + SchemaName + "." + TableName + " with(nolock) WHERE mfid IN ('" + MFID + "')" + " AND activeflag = 'Y' AND ga_id NOT IN ('DPN') ORDER BY EmpID,ga_id";

    SqlDataAdapter adapter = new SqlDataAdapter(queryString, myADONETConnection);

    DataSet ds = new DataSet();
    adapter.Fill(ds);

    //Get Header Columns
    string TableColumns = "";

    // Get the Column List from Data Table so can create Excel Sheet with Header
    foreach (DataTable table in ds.Tables)
    {
        foreach (DataColumn column in table.Columns)
        {
            TableColumns += column + "],[";
        }
    }

    // Replace most right comma from Columnlist
    TableColumns = ("[" + TableColumns.Replace(",", " Text,").TrimEnd(','));
    TableColumns = TableColumns.Remove(TableColumns.Length - 2);
    //MessageBox.Show(TableColumns);

    //Use OLE DB Connection and Create Excel Sheet
    Excel_OLE_Con.ConnectionString = connstring;
    Excel_OLE_Con.Open();
    Excel_OLE_Cmd.Connection = Excel_OLE_Con;
    Excel_OLE_Cmd.CommandText = "Create table [" + SchemaName + "_" + TableName + "] (" + TableColumns + ")";
    Excel_OLE_Cmd.ExecuteNonQuery();

    //Write Data to Excel Sheet from DataTable dynamically
    foreach (DataTable table in ds.Tables)
    {
        String sqlCommandInsert = "";
        String sqlCommandValue = "";

        foreach (DataColumn dataColumn in table.Columns)
        {
            sqlCommandValue += dataColumn + "],[";
        }

        sqlCommandValue = "[" + sqlCommandValue.TrimEnd(',');
        sqlCommandValue = sqlCommandValue.Remove(sqlCommandValue.Length - 2);
        sqlCommandInsert = "INSERT into [" + SchemaName + "_" + TableName + "] (" + sqlCommandValue + ") VALUES(";

        int columnCount = table.Columns.Count;

        foreach (DataRow row in table.Rows)
        {
            string columnvalues = "";

            for (int i = 0; i < columnCount; i++)
            {
                int index = table.Rows.IndexOf(row);
                columnvalues += "'" + table.Rows[index].ItemArray[i] + "',";
            }

            columnvalues = columnvalues.TrimEnd(',');

            var command = sqlCommandInsert + columnvalues + ")";
            Excel_OLE_Cmd.CommandText = command;
            Excel_OLE_Cmd.ExecuteNonQuery();
        }
    }

    Excel_OLE_Con.Close();
                
    Dts.TaskResult = (int)ScriptResults.Success;
}

Upvotes: -1

Views: 607

Answers (1)

zoher.festo2
zoher.festo2

Reputation: 33

The main issue is that the MFID variable using while building your query contains a quotation mark which is used for string literals in SQL Server.

System.Data.OleDb.OleDbException (0x80040E14): Syntax error (missing operator) in query expression ''O'Brien','O'Brien''.

You can solve this issue by parametrizing your SQL command. Lots of tutorials and step-by-step guides are found online. As an example:

Since you are using the MFID variable within the IN () clause, you can refer to the following answers to solve this issue:

Upvotes: 1

Related Questions