Programatically adding a Timeout value for BulkInsertTask?

SQL Server Integration Services

mikewo


I've searched around for a while and can't seem to find out how/where I set the timeout on a bulk insert task programmatically. Here is a snippet of code that works as long as the input file is pretty small....when I attempted to pump 1.3 million rows through the default timeout value was not big enough and a timeout exception occurred.

Package loadPackage = new Package();

Stopwatch timer = new Stopwatch();

timer.Start();

ConnectionManager conMgr = loadPackage.Connections.Add("FLATFILE");

conMgr.ConnectionString = @"C:\BWR.txt";

conMgr.Name = "FileInput";

conMgr = loadPackage.Connections.Add("ADO.NET:ODBC");

conMgr.ConnectionString = @"Data Source=FOO;Initial Catalog=DestinationDb;Integrated Security=True;";

conMgr.Name = "DestinationDB";

TaskHost task = loadPackage.Executables.Add("STOCK:BulkInsertTask") as TaskHost;

BulkInsertTask biTask = (BulkInsertTask)task.InnerObject;

biTask.UseFormatFile = true;

biTask.FormatFile = @"C:\BWR_1.fmt";

biTask.SourceConnection = "FileInput";

biTask.DestinationConnection = "DestinationDB";

biTask.DestinationTableName = "StageBWR";

biTask.BatchSize = 100;

DTSExecResult result = loadPackage.Execute();

timer.Stop();

Console.WriteLine(string.Concat("Process took(ms): ", timer.ElapsedMilliseconds.ToString()));

Console.WriteLine(result.ToString());

if (loadPackage.Errors.Count > 0)

{

foreach (DtsError error in loadPackage.Errors)

{

Console.WriteLine(error.Description);

}

}

Again, the code above works just fine until we hit a timeout. There is no Timeout property on the BulkInsertTask class, nor can I see a way to set it on the Sql Connection. I don't even see them in the Connectionmanager.Properties collection for either the file connection or the database connection.

Am I overlooking the obvious somewhere




Top

Re: Programatically adding a Timeout value for BulkInsertTask?

jaegd


I don't see this timeout behavior you're describing with an OLEDB connection manager connecting to SQL Server 2005.

The bulk insert task always runs until its done, whether it takes seconds or an hour.

So, a couple of things
1. What is interval duration before the timeout occurs
2. Have you tried using an OLEDB connection manager rather than ODBC over ADO.NET
3. What database (including version) are you connecting to and does it have a query governer limit set






Top

Re: Programatically adding a Timeout value for BulkInsertTask?

mikewo

jaegd, thanks for your help.

Answers to your questions:

  1. The timeout was right at the default 30 seconds for an ADO.Net command.
  2. I switched to the OLEDB connection and the timeout issue went away.  This is what fixed my issue.
  3. SQL Server 2005, SP1.  No query governer limit was set.

I had tried the ADO.Net:SQL and the ADO.Net:ODBC connection types and both had exhibited the timeout issue.  When I switched to the OLEDB connection instead the timeout was no longer occuring. 

For those interested here was my throughput:

Rows 1,395,399 read from a text file.  Each row was broken into 23 separate columns as defined by the format file.

When performed with a Batchsize of 100 records the time was 115,824 milliseconds.
When performed with a Batchsize of 1000 records the time was 46,741 milliseconds.
When performed with a Batchsize of 2000 records the time was 49,745 milliseconds.

[EDIT]: These numbers were produced by running on a P4/3GHz workstation with 3.5 GB of RAM.  The input file and format file were on the same box as the Sql Server.  When run against a remote database the network latency increased my times significantly (2 minutes for 1000 row batch size).  Your mileage my vary.






Top