January 25, 2016 at 1:11 pm
I need to refresh a table with new / updated records so I have created a SSIS package for that
In SSIS, I am
Deleting records from the table for a time period and then again inserting records into that table from a datasource for that time period However if the insertion fails then there should be a rollback (no deletion).
So I have used transactions in SSIS, but not sure which lock hints should I use and where?
Thanks
January 25, 2016 at 1:20 pm
btsserv13 (1/25/2016)
I need to refresh a table with new / updated records so I have created a SSIS package for thatIn SSIS, I am
Deleting records from the table for a time period and then again inserting records into that table from a datasource for that time period However if the insertion fails then there should be a rollback (no deletion).
So I have used transactions in SSIS, but not sure which lock hints should I use and where?
Thanks
If you have transactions already there is likely no need to add any lock hints. Hints indicate that you know better than the sql server engine how to manage locking and unless you really understand what you are doing it is generally best to simply avoid them.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 25, 2016 at 1:41 pm
Thanks for the response Sean.
I did try using SSIS transaction and it takes more than 20 X times to execute the package than it would if I use without SSIS transactions. So was not sure if I would need to use lock hints.
January 25, 2016 at 2:01 pm
btsserv13 (1/25/2016)
Thanks for the response Sean.I did try using SSIS transaction and it takes more than 20 X times to execute the package than it would if I use without SSIS transactions. So was not sure if I would need to use lock hints.
That is likely due to the size of the transaction. How many rows are you deleting? Keep in mind that for transactions to work it has to keep this data somewhere so it can do a rollback. If the amount of data is large enough it has to write to disc which can take a lot of time.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply