Getting System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'OfficeOpenXml'. Error message

0

I'm trying to import Excel sheet data into the DB via SSIS script task, I'm continuously getting the above error message.

The Excel file has 3 sheet, and we have separate table for each sheet of that Excel file in the database.

Also I'm not getting any error in the region Namespaces.

Can anyone help, what I'm missing here?

public void Main()
{
        try
        {
            string filePath;

            filePath = Dts.Variables["FilePath1"].Value.ToString();
            
            ReadXLS(filePath);
        }
        catch (Exception e)
        {
            MessageBox.Show(e.ToString());
            Dts.TaskResult = (int)ScriptResults.Success;
        }
}

public void ReadXLS(string FilePath)
{
    FileInfo existingFile = new FileInfo(FilePath);

    using (ExcelPackage package = new ExcelPackage(existingFile))
    {
        string eachVal = "";

        int wsheet = package.Workbook.Worksheets.Count; 

        for (int sheet = 0; sheet<wsheet; sheet++)    
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets[sheet];
            string SheetNames = worksheet.Names.ToString();

            int rowCount = worksheet.Dimension.End.Row; 
            int colCount = worksheet.Dimension.End.Column;  

            for (int row = 2; row <= rowCount; row++)  
            {
                string queryString = "Insert into [Billing_]" + SheetNames + "values(";
                queryString += "(";
                    
                for (int col = 1; col <= colCount; col++)     
                {
                    eachVal = worksheet.Cells[row, col].Value?.ToString().Trim();
                       
                    queryString += "'" + eachVal + "',";
                }
                 
                queryString = queryString.Remove(queryString.Length - 1, 1); 
                queryString += ")"; 

                runQuery(queryString);    
            }
    }

    private void runQuery(string QueryString)
    {
        string connectionString = @"Data Source = ./; Initial Catalog = Testing; Integrated Security = SSPI;";
        
        string commandText = QueryString;

        using (SqlConnection conn = new SqlConnection(connectionString))
        using (SqlCommand cmd = new SqlCommand(commandText, conn))
        {
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }
}
c#
sql-server
excel
visual-studio
ssis
asked on Stack Overflow Mar 22, 2021 by Simma • edited Mar 22, 2021 by marc_s

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0