ADO.Net destination Use bulk insert when possible?

SSIS and Import/Export
1

Hi,

Been trouble shooting for weeks a major performance decline across 100 packages migrating very large tables. Discovered the packages where inserting row-by-row and not bulked any more. The ADO.Net Destination CM has 'Use Bulk Insert when possible' ticked - but has decided not to bulk insert any more.

Does anyone know the criteria that determines whether ADO.Net Destination will BULK INSERT or Row-by-Row? I'm unable to find any articles out there for an explanation what the 'where possible' means!?!?

Thanks in advance

2

I think you need to be sure that the data is sorted in the order of the primary key of the destination table. Then, you have to indicate that it is sorted (and the keys) in the properties of the input data source to the destination. If the transformation does not know that the data is sorted, it assumes it is not and does an RBAR insert.

3

Thanks.

Packages were tested against destination tables with PKs and benchmarked a satisfactory time. Then I converted all tables 10 million rows + to Clustered Column Store Indexes removing the PK. This is when the performance issues must had struck. Makes sense.

Big thanks