How to mention NOLOCK during SSIS import and export wizard

Hello experts,

I wanted to copy around 20 tables from one db to another using import and expoert wizard but without locking the source tables. Is there any option inside the wizard where in i can check that option or any other alternative?
Thanks
parpaaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

alainbrydenCommented:
Copying entire tables can't not lock the source because of race conditions that could occur. If the table contents were copied over, and a new entry was made after the copy, and then the index was copied over, where the index has a reference to the new entry which wasn't copied over, then the copied table and index is corrupt. This is the only obvious example I can readily think of as to why the source tables need to be locked for the copy.

--
Alain
0
rgc6789Commented:
You could select * Into MigrateTable1 From Table1 for each of the 20 or so tables and then use the Import/Export, or you can link the db's and do the same as above directly from one to the other.
0
parpaaAuthor Commented:
@alain - in my case i am copying whole table so u mean to say there wouldn't be any lock. i am testing it now and so far there is no lock and it still has to transfer around 100k rows
0
alainbrydenCommented:
Well you said you were testing, did the transfer work without locking or no? Is there any issue you're having in accomplishing some task?

--
Alain
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
parpaaAuthor Commented:
Yes it worked, sorry for the late response, been busy..
Thanks!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.