December 2, 2011 at 8:40 am
Hello -
I have SSIS package that startes with a Transacrion and either commits or rollsback. The job runs every two hours and pulls data like it should sho users can report off of it in excel. The issue I'm having is when that job is running and someone tries to refresh from the table in excel. Excel just hangs and spins and spins until the job is done.
Any thoughts on what I might be missing? Is doing a transaction in SSIS a good practice? I attached a image of what I have so far.
Thanks,
David
December 2, 2011 at 8:50 am
I don't understand this bit: "..pulls data like it should sho users can report off of it in excel." - is there a typo in there somewhere? It doesn't make sense.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 2, 2011 at 8:57 am
Sorry about that...
My SSIS package pulls data from table (A) and inserts it into table (B) on another sql server.
Our users pull that data from table (B) into excel and pivot whatever they want.
The issue I'm having is when the SSIS package is running. Excel seems to lockup or spins and spins if someone tries to refresh Excel from that table (B). Once the ssis job is done all is fine again.
December 2, 2011 at 9:09 am
david.ostrander (12/2/2011)
Sorry about that...My SSIS package pulls data from table (A) and inserts it into table (B) on another sql server.
Our users pull that data from table (B) into excel and pivot whatever they want.
The issue I'm having is when the SSIS package is running. Excel seems to lockup or spins and spins if someone tries to refresh Excel from that table (B). Once the ssis job is done all is fine again.
Sounds like SSIS is locking the destination table while the insert runs, so that querying from Excel does not succeed until the lock is released. Some options:
-- Optimise the process to minimise lock time
-- Use (nolock) to get the data (please don't do this unless you understand the implications)
-- Run the package outside of the times where users will be querying the table
-- Allow Excel users to access a regularly updated copy of the table
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 2, 2011 at 11:05 am
Thank you for the responses. Looks like we will have to do the
Allow Excel users to access a regularly updated copy of the table
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply