none
how to export data from SQL database to Access Database using BizTalk? RRS feed

  • Question

  • I'm new in BizTalk and I am trying to get data from SQL server to my Access database but not in a looping process because the data are very large. I have a procedure that work but because the data are so large, it never able to export the whole table and it take too long to process. If someone can help me so that I could just do an sql script to insert from one table to another. (Example: Insert into DistinationTable select * from SourceTable) but this SQL script only if you are in the same application. for example: SQL server. But when there are 2 different application, I don't know how to do this. Below is my example in C# that export from sql to access. but this insert to Access line by line. I really need to be able to just insert without looping. Thanks for the help in advance.

    public void InsertIntoAccess(String spName, String tblName)

            {

                SqlConnection sqlConn = new SqlConnection(SQLConnString);

                OleDbConnection accConn = new OleDbConnection(AccessDBConnString);

                long i = 0;

                try

                {

                    SqlCommand sqlCmd = new SqlCommand();

                    sqlCmd.CommandTimeout = 900;  // 15 min

                    sqlCmd.CommandType = CommandType.StoredProcedure;

                    sqlCmd.CommandText = spName;

                    sqlCmd.Connection = sqlConn;

                    sqlConn.Open();

                    SqlDataReader reader = sqlCmd.ExecuteReader();

                   

                    accConn.Open();

                   

                    string accessCmd = null;

                        while (reader.Read())

                        {

                            accessCmd = "INSERT INTO " + tblName + " VALUES (";

                            accessCmd += reader.IsDBNull(0) ? "''" : "'" + reader.GetString(0) + "'";

                            accessCmd += ", ";

                            accessCmd += reader.IsDBNull(1) ? "''" : "'" + reader.GetString(1) + "'";

                            accessCmd += ", ";

                            accessCmd += reader.IsDBNull(2) ? "''" : "'" + reader.GetString(2) + "'";

                            accessCmd += ", ";

                            accessCmd += reader.IsDBNull(3) ? "''" : "'" + reader.GetString(3) + "'";

                            accessCmd += ", ";

                            accessCmd += reader.IsDBNull(4) ? "null" : "'" + reader.GetDateTime(4).ToString("dd/MM/yyyy") + "'";

                            accessCmd += ")";

                            OleDbCommand myComm = new OleDbCommand(accessCmd, accConn);

                            myComm.ExecuteNonQuery();

                            i += 1;

                    }

                   

                }

                catch (Exception ex)

                {

                    string Msg = "Failure while Inserting " + tblName + " to ACCESS DB. \r\n";

                    Msg += "Access Conn String: " + AccessDBConnString + "\r\n";

                    Msg += "SQL Conn String: " + SQLConnString + "\r\n";

                    Msg += "Message: " + ex.Message;

                    Msg += "StackTrace: " + ex.StackTrace;

                    System.Diagnostics.EventLog.WriteEntry( tblName + " Insert Failure", Msg, EventLogEntryType.Error);

                }

                finally

                {

                    if (sqlConn.State == ConnectionState.Open)

                        sqlConn.Close();

                    if (accConn.State == ConnectionState.Open)

                        accConn.Close();

                    System.Diagnostics.EventLog.WriteEntry("Insert " + i.ToString() + " of rec -" + tblName, ":Success! " + i.ToString());

                }

     

            }

     

    Friday, March 11, 2011 10:55 PM

Answers

  • Hi,

    Using BizTalk in such kind of scenario is not recommended. You can use data transfer tool such as SSIS or DTS package for this kind of job. BizTalk do not have an adapter for Access database. Please let me know if you have seen it any of the BizTalk version.

    You should use BizTalk when need to implement process flow, a messaging solution, messaging transformation etc. Please let me know if you have any further question.

    Regards,

    Vishnu

     


    Best Regards, Vishnu
    Saturday, March 12, 2011 3:15 PM