sql - SSIS bulk insert into a single table concurrently -

i have ssis package task load out-of-process application bulk-insert data 1 table. problem multiple out-of-process applications run when multiple files arrive @ same time. result in insertion failure.

can sql server broker service queue inserting data? sql server or ssis have mechanism handle concurrent reliable insertion?

i use ssis' execute process task execute bulk insert linq sql console application

thanks.

it sounds you're getting timeout issues because first run of ssis package locking table , other running copies of package waiting lock released.

there couple of things can confirm this. first, in sql server management studio (ssms), open query window , when situation occurring execute command exec sp_who2. see blkby column in results. column contains spid value blocking selected process. you'll see 1 instance of package blocking other packages.

in ssis designer, in data flow task, edit destination component. there's table lock checkbox. checked, tells process lock table until data load complete.

you have couple of options address this. first, important 1 ssis package must complete loading data before 1 can start? if answer no, can uncheck table lock option in destination component. allow sql server manage simultaneous data loads.

if must let 1 package complete before other packages can run, may want create ssis task checks see if table available loading. if table being loaded, stop ssis package , recheck later. handle in console app.

sql server doesn't have built-in ways , broker service sounds it's more work need.