Post reply

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

  • 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, 4 months ago by  Antonhx.

  • August 7, 2019 at 9:02 am

    #3669395

    Is "Table Lock"  setting in Oledb connection enabled?

  • August 7, 2019 at 9:06 am

    #3669396

    Yes it is, see attachment.

    Attachments:
    You must be logged in to view attached files.

  • August 7, 2019 at 9:23 am

    #3669399

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

     

  • 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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply