How do I "lock" certain tables in the database while my SSIS package is running so it throws a SqlException if someone tries to access any of the tables being loaded?

Hello VT_Bassman: If you are using the Execute SQL Task then you can set it to lock the table(s) using a hint. There are various level of locks, please familiarize yourself by visiting http://msdn.microsoft.com/en-us/library/ms187373.aspx (I assumed you are using SQL Server). If you are using an OLEDb/SQL Server destination in DFT then use the Table Lock option (also default).Arthur My Blog

I am using a Flat File Extract to an OLEDB Destination. What is DFT? Also where is the Table Lock Option?

Ok, DFT = Data Flow Task. I found the table lock option but that is only available if I use the Fast Load data access mode and i found that if I have a duplicate key record the whole load dies. I did not want that to happen. I want to write out the duplicate key record to a DB error file and continue loading so I was forced to use the able or View data access mode.

Will redirecting the duplicate records to a table help you? If yes, you can set error redirection. To prevent duplicates from being attempted inserted you can implement a Lookup Transformation.Arthur My Blog

The load is loading an empty table daily so the Lookup Transformation would have nothing as a reference. I can't believe you can't lock the table in Table or View access mode.

Use SQL Server Destination which does have it at all times!Arthur My Blog

SQL Server Destination only allows Bulk insert which will fail if there are any duplicates and The SQL Server destination has one input. It does not support an error output.

You can disable most of the bulk insert settings including the check for duplicates - BulkInsertCheckConstraints, just use the Show Advanced Editor and go to Control Properties tab.Arthur My Blog

OLEDB Destination has an error output. It can be used to redirect the error rows.Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.

OLEDB Destination is what I am using and I am already redirecting the error rows to an error flat file. The problem is the OLEDB Destination does not allow me to lock the tables it is loading when using Table or View data access mode and if you try to use fast load data access mode the whole load fails if there are duplicates. Maybe I could use a sort transformation prior to the load but I don't see where the sort transform allows you to write the dupes to a file. I need to email this file to a user so they can fix the error records on the Mainframe.

You probably can use a Script Transformation to sift through the records. In this transformation you can spawn the erroneous (dupies) records to the file. Also FuzzyLookup can work! Arthur My Blog

I was able to redirect successfully with the fast load as well using the OLEDB destination. Looks like something is missed.Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.

How did you do it?

Simple and straight. Created OLEDB source and OLEDB destination and populated the destination with duplicates. The destiantion OLEDB is table or view - FastAccess with options Table lock and Check constraints checked. Also the redirect error option is enabled on OLEDB destination. When the redirect error is conected to any other flat file or table , there are twop drop downs where the redirect has to be selected.Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.

Did that and ran a test (with one duplicate record in the middle of the file). The problem is that once the first duplicate is encountered every record after the dupe is written to the error file and no more good records get written to the database table.

One question I would ask is this: do you really want to cause an SqlException rather than just blocking? If so, just curious why... Beyond that, couple options: 1. Load your data to a simple staging table, filtering dupes, and then dump to the real prod. That's probably the "right" way to do it. 2. If you really don't care about users getting exceptions, at the start of the package rename the table, load it, and rename it back when done. That's hack-tastic! Either way, SQL Server generally isn't design to bomb when you try to access a table that's being used. Good luck, sb I program in C#... but I can't bring myself to retire the 10 year old vbSlinger moniker...

I handle all SqlExceptions with Catch blocks that display an alert message and then redirect the user to the home page. If my package is running late (during business hours) and a user tries to access the application while the tables are being loaded I want to show the alert and do the redirect. I aklso need the dupes written to a text file.

If you need the dupes, then I would just write to a staging table and do two dumps: one selecting dupes to a text file and one that dumps to your real prod table. Unless we're talking millions of rows, that should be a very quick operation. If you don't want any blocking at all, and are willing to risk a user error, dump to a secondary staging table. Then, drop the prod table and rename the secondary staging table to the prod table name. If you have a complicated schema/indexes/etc, this could be problematic, but based on what you're doing... it all seems simple. In the end, you are sorta trying to do things SQL isn't really intended to do, which is when we all get in trouble as techies :) I program in C#... but I can't bring myself to retire the 10 year old vbSlinger moniker...

You may want to consider loading to a temporary table, then renaming the table in a transaction to "switch" it into visibility. You may also just want to use transactions - start a transaction before the load, attempt the load and clean up, then commit the transaction. The net result will be that other clients will wait for the transaction to complete. (Assuming they respect transactions using default behaviour.) You can detect duplicates before you write the data - I have outlined several techniques here. Talk to me now on