topic RE: Insert CSV to SQL Table, Extending the Query Time out in the Code in Product Forum

Insert CSV to SQL Table, Extending the Query Time out in the Code

ImranMaredia — Thu, 23 Dec 2021 17:10:00 GMT

Hello Everyone,
I have a blueprism code which I downloaded from Internet, This code imports CSV to SQL Table,
It works great, But when I am trying to insert more than 500k rows of data, it gives me execution timeout error.
It works well for any query which is within approx 45 seconds. Please check the exception message I am getting below, I also extended the Query execution limit from SQL Server but still getting this error.

I am also attaching the Code, ScreenShot of my VBO, and I have 4 data items which I am mentioning their names and current values .

Please help me out in the code to increase the Execution time limit. Thank you

Execution Started
Connection Opened
Execution Started
Data Loaded to Collection : Total Records 519200
Connection Closed
SQL Execution Started
SQL Connection Opened
SQL Insertion Started
SQL Exception : Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
SQL Connection Closed

------------------------------
Imran Maredia
------------------------------

RE: Insert CSV to SQL Table, Extending the Query Time out in the Code

ewilson — Thu, 23 Dec 2021 17:23:00 GMT

Hi @ImranMaredia,

The SqlBulkCopy class exposes a property called BulkCopyTimeout. If you set that property to 0 it basically says there is no limit and the bulk copy will wait indefinitely. I'm not recommending that though as if something happens you could end up with a stuck process. You might try breaking up the bulk data a bit. Perhaps divide it into collections of no more than 100K records and then perform the bulk copy 5 times.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Insert CSV to SQL Table, Extending the Query Time out in the Code

ImranMaredia — Thu, 23 Dec 2021 18:07:00 GMT

Thanks Amazing Eric, It worked, It hardly took 1min 20 seconds to insert 500k rows, But I will go with your suggestion
breaking the large file into smaller, But good to know about this Bulkcopy Timeout property, I will set a customized timeout limit now.

This is fantastic and quick reply, I really appreciate it.

------------------------------
Imran Maredia
------------------------------