locked
SqlBulkCopy? RRS feed

  • Question

  • Hello again, I've got an exteremly frustrating new problem with WWF Beta 2.

    I'm in the process of converting a simple data import utility that I wrote a few weeks back that uses the new SqlBulkCopy class.  In my existing import utility ( a simple console application ) the bulk copy operation takes an average of about 17 seconds and works like a charm.

    I set about breaking this import utility into several "unit of work" style blocks and was able to get all except the most important one working.

    In the final activity which actually does the bulk import of a text file into a SQL table using SqlBulkCopy.WriteToServer, the follow SQL exception always occurs at about 38 seconds:

    Timeout Expired. The timeout period elapsed prior to the completion of the operation or the server is not responding.

    This happens regardless of the ConnectTimeout value specified in my SqlConnectionStringBuilder.  The code is nearly line-for-line identical to the code used in the existing utility, so all I can figure is that there's some sort of conflict occuring between WWF and SqlBukCopy.

    My hunch is that it's something to do with the unmanaged code that runs beneath SqlBulkCopy.

    Oh and just for reference in both cases the data file being processed and the destination database are identical, and in fact I even manually ran the utility again with the same data file and database several times after I started getting the aforementioned exceptions and in each case the process ran fine and performed the import in about 17seconds.


    Any ideas??

    Thanks!

    Dr. Evil

    Tuesday, April 11, 2006 8:04 PM

Answers

  • Can you post the code for your activity's Execute method?  What is the State of your SqlConnection before calling WriteToServer?  I did not get any Sql exception when I used the following activity:

    using System;

    using System.Workflow.ComponentModel;

    using System.Data;

    using System.Data.SqlClient;

     

    namespace WorkflowConsoleApplication6

    {

        public partial class SqlBulkCopyActivity : Activity

          {

                public SqlBulkCopyActivity()

                {

                      InitializeComponent();

            }

     

            #region Designer generated code

     

            /// <summary>

            /// Required method for Designer support - do not modify

            /// the contents of this method with the code editor.

            /// </summary>

            private void InitializeComponent()

            {

                //

                // SqlBulkCopyActivity

                //

                this.Name = "SqlBulkCopyActivity";

     

            }

     

            #endregion

     

            protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)

            {

                SqlBulkCopy sqlBulkCopy = new SqlBulkCopy("Data Source=localhost;Initial Catalog=Test;Integrated Security=SSPI;");

                sqlBulkCopy.DestinationTableName = "Orders";

                sqlBulkCopy.WriteToServer(CreateOrdersTable());

                return base.Execute(executionContext);

            }

     

            private DataTable CreateOrdersTable()

            {

                // Create a new DataTable.

                DataTable table = new DataTable("Orders");

                // Declare variables for DataColumn and DataRow objects.

                DataColumn column;

                DataRow row;

     

                // Create new DataColumn, set DataType,

                // ColumnName and add to DataTable.    

                column = new DataColumn();

                column.DataType = System.Type.GetType("System.Int32");

                column.ColumnName = "OrderID";

                column.ReadOnly = false;

                column.Unique = true;

                // Add the Column to the DataColumnCollection.

                table.Columns.Add(column);

     

                // Create second column.

                column = new DataColumn();

                column.DataType = System.Type.GetType("System.String");

                column.ColumnName = "SalesPerson";

                column.AutoIncrement = false;

                column.ReadOnly = false;

                column.Unique = false;

                // Add the column to the table.

                table.Columns.Add(column);

     

                // Make the ID column the primary key column.

                DataColumn[] PrimaryKeyColumns = new DataColumn[1];

                PrimaryKeyColumns[0] = table.Columns["OrderID"];

                table.PrimaryKey = PrimaryKeyColumns;

     

                // Create three new DataRow objects and add

                // them to the DataTable

                for (int i = 0; i <= 20; i++)

                {

                    row = table.NewRow();

                    row["OrderID"] = i;

                    row["SalesPerson"] = "SalesPerson " + i;

                    table.Rows.Add(row);

                }

     

                return table;

            }

          }

    }

    Wednesday, April 12, 2006 9:11 PM

All replies

  • Can you post the code for your activity's Execute method?  What is the State of your SqlConnection before calling WriteToServer?  I did not get any Sql exception when I used the following activity:

    using System;

    using System.Workflow.ComponentModel;

    using System.Data;

    using System.Data.SqlClient;

     

    namespace WorkflowConsoleApplication6

    {

        public partial class SqlBulkCopyActivity : Activity

          {

                public SqlBulkCopyActivity()

                {

                      InitializeComponent();

            }

     

            #region Designer generated code

     

            /// <summary>

            /// Required method for Designer support - do not modify

            /// the contents of this method with the code editor.

            /// </summary>

            private void InitializeComponent()

            {

                //

                // SqlBulkCopyActivity

                //

                this.Name = "SqlBulkCopyActivity";

     

            }

     

            #endregion

     

            protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)

            {

                SqlBulkCopy sqlBulkCopy = new SqlBulkCopy("Data Source=localhost;Initial Catalog=Test;Integrated Security=SSPI;");

                sqlBulkCopy.DestinationTableName = "Orders";

                sqlBulkCopy.WriteToServer(CreateOrdersTable());

                return base.Execute(executionContext);

            }

     

            private DataTable CreateOrdersTable()

            {

                // Create a new DataTable.

                DataTable table = new DataTable("Orders");

                // Declare variables for DataColumn and DataRow objects.

                DataColumn column;

                DataRow row;

     

                // Create new DataColumn, set DataType,

                // ColumnName and add to DataTable.    

                column = new DataColumn();

                column.DataType = System.Type.GetType("System.Int32");

                column.ColumnName = "OrderID";

                column.ReadOnly = false;

                column.Unique = true;

                // Add the Column to the DataColumnCollection.

                table.Columns.Add(column);

     

                // Create second column.

                column = new DataColumn();

                column.DataType = System.Type.GetType("System.String");

                column.ColumnName = "SalesPerson";

                column.AutoIncrement = false;

                column.ReadOnly = false;

                column.Unique = false;

                // Add the column to the table.

                table.Columns.Add(column);

     

                // Make the ID column the primary key column.

                DataColumn[] PrimaryKeyColumns = new DataColumn[1];

                PrimaryKeyColumns[0] = table.Columns["OrderID"];

                table.PrimaryKey = PrimaryKeyColumns;

     

                // Create three new DataRow objects and add

                // them to the DataTable

                for (int i = 0; i <= 20; i++)

                {

                    row = table.NewRow();

                    row["OrderID"] = i;

                    row["SalesPerson"] = "SalesPerson " + i;

                    table.Rows.Add(row);

                }

     

                return table;

            }

          }

    }

    Wednesday, April 12, 2006 9:11 PM
  • Tom,

    I call to a private method to execute the SqlBulkCopy from my Execute activity, the code in question is as follows:

                    OdbcConnectionStringBuilder oCSB = new OdbcConnectionStringBuilder();
                    oCSB.Driver = "Microsoft Text Driver (*.txt; *.csv)";
                    oCSB.Add("DBQ", this.SourceDirectory);
                    oCSB.Add("Extensions", "asc,csv,tab,txt");

                    OdbcConnection oConn = new OdbcConnection(oCSB.ConnectionString);

                    oConn.Open();

                    // build our command string.
                    OdbcCommand oCmd = new OdbcCommand(String.Format("SELECT * FROM {0}", this.SourceFilename), oConn);
                    OdbcDataReader oDR = oCmd.ExecuteReader();

                    // build SQL connection string...
                    SqlConnectionStringBuilder sCSB = new SqlConnectionStringBuilder();
                    sCSB.DataSource = this.SqlServer;
                    sCSB.InitialCatalog = this.Database;
                    sCSB.UserID = this.DBUser;
                    sCSB.Password = this.DBPassword;

                    // create the SqlBulkCopy object
                    SqlBulkCopy sBC = new SqlBulkCopy(sCSB.ConnectionString);

                    // set the destination table name
                    sBC.DestinationTableName = this.DataTable;

                    // write the data
                    sBC.WriteToServer(oDR);

    The exception occurs in the sBC.WriteToServer() line of the code.  The source text file (the Odbc source) is available and working (tested this by reading the contents of the datareader to the console.) and the connection string is exactly the same as is used in another activity that simply performs a ExecuteNonQuery for procedures that take no params which works just fine.  So I'm at a loss.  I'm going to try building a new project using your code and see what happens...

    Thanks!

    Dr. Evil
    Thursday, April 13, 2006 12:55 PM
  • Tom,

    Did some additional experimentation and replaced the DataReader being passed to SBC with a pre-filled DataTable as you used in your example and now it works perfectly.  Why it works one way and not the other is beyond me, but hell this'll make the bosses day!

    Thanks for your help on this issue!

    Dr. Evil
    Thursday, April 13, 2006 1:08 PM