none
Load Data From ODBC to SQL Server RRS feed

  • Question

  • Greetings Developers 

    i trust you are well 

    i was wondering if you could help me i was advised to asked in this forum 

    i have a C# code to connect to ODBC database and Load Data into SQL Server. my issue is, is that when i connect to ODBC and get that data from source i get this error message 

    Message :Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.<br/>
    StackTrace :   at System.Data.DataTable.EnableConstraints()
       at System.Data.DataTable.set_EnforceConstraints(Boolean value)
       at System.Data.DataTable.EndLoadData()
       at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
       at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
       at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
       at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)

    this is my code 

    public void Main()
    {
                // TODO: Add your code here

                try
                {
                    // ODBC Connection
                    OdbcConnection ODBC_DataWarehouse = new OdbcConnection("DSN=ODBC_64bit");
                    OdbcCommand cmd = new OdbcCommand("SELECT * FROM ODBC_Database1 ,ODBC_Database2", ODBC_DataWarehouse);
                    ODBC_DataWarehouse.Open();
                    MessageBox.Show("ODBC- SuccessFully Connected");
                    MessageBox.Show("ODBC- Executing Code");

                    //load into datatable
                    DataTable dt = new DataTable();
                    OdbcDataReader dr = cmd.ExecuteReader();
                    dt.BeginLoadData();
                    dt.Load(dr);
                    dt.Constraints.Clear();
                    dt.EndLoadData();
                    MessageBox.Show(dt.Rows[1][1].ToString());
                        
                    AJS_DataWarehouse.Close();

                    //sql connection
                    using (SqlConnection sqlConnection = new SqlConnection("Server=ServerName;Database=TestingDB;Uid=username;Pwd=password;Trusted_Connection=True;"))
                    {

                        sqlConnection.Open();

                        //open bullk copy connection.

                        using (SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlConnection))
                        {
                            //set destination table name

                            bulkcopy.DestinationTableName = "TestingDB.dbo.Staging_Testing";


                            bulkcopy.WriteToServer(dt);
                            MessageBox.Show("Data Successfully loaded");

                            sqlConnection.Close();

                        }
                    }

                }

                catch (Exception ex)
                {
                    string filePath = @"\\shared\SSIS Solutions\ProcessData\Error.txt";

                    using (System.IO.StreamWriter writer = new System.IO.StreamWriter(filePath, true))
                    {
                        writer.WriteLine("Message :" + ex.Message + "<br/>" + Environment.NewLine + "StackTrace :" + ex.StackTrace +
                           "" + Environment.NewLine + "Date :" + DateTime.Now.ToString());
                        writer.WriteLine(Environment.NewLine + "-----------------------------------------------------------------------------" + Environment.NewLine);
                    }
                }

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

    please may you help me in trying to fix it 

    Thursday, March 22, 2018 8:40 AM

All replies

  • Looks like a constraint issue. In the DataSet set EnforceConstraints = false to read the data. Bottom line is this is not a connection issue but instead a data issue.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, March 22, 2018 11:34 AM
    Moderator
  • thank you, i have managed to fix it, i manually created the datatable with its respective columns and also cleared the rows within the data table and i have managed to get all the data i needed 

    thanks again 

    Ronald 

    Thursday, March 22, 2018 12:10 PM