(Fully) lock table during SSIS package import on SQL server

• Antonhx

  Old Hand

  Points: 329

  More actions

  August 7, 2019 at 8:54 am

  #3669392

  Hi all,

  I created an SSIS package to import data from one database to another. The destination table connection manager is set to lock the table. I expected this to lock the destination table for select statements while importing the data. However, when I execute the select statement on the destination table it still gives me a set of results. When I execute this statement multiple times during the import it just gives me an increased dataset for each attempt.

  1. How can I prevent that a select statement can be executed until the import is fully completed?

  Furthermore, the SSIS package excists out of 3 steps: (1) Drop destination table, (2) create destination table, (3) data import.

  2. How can I prevent that a select statement can be executed during steps 1 and 2?

  Thank you in advance.

  Antonhx

  • This topic was modified 5 years, 10 months ago by Antonhx.

• Andrey

  SSC Eights!

  Points: 840

  More actions

  August 7, 2019 at 9:02 am

  #3669395

  Is "Table Lock"  setting in Oledb connection enabled?

• Antonhx

  Old Hand

  Points: 329

  More actions

  August 7, 2019 at 9:06 am

  #3669396

  Yes it is, see attachment.

  Attachments:

  You must be logged in to view attached files.

• Andrey

  SSC Eights!

  Points: 840

  More actions

  August 7, 2019 at 9:23 am

  #3669399

  What do you use to "import data from one database to another" in the package?

   

• Antonhx

  Old Hand

  Points: 329

  More actions

  August 7, 2019 at 9:53 am

  #3669405

  I don't know exactly what you mean, but an ADO.net connection fetches data from a Visual FoxPro database. The data gets converted and is exported to an OLE DB destination (SQL Server). The SSIS package is deployed on the sql server and is recurrently executed by a SQL server agent job.