| Author |
Topic |
|
ostinoh
Yak Posting Veteran
66 Posts |
 Posted - 2011-12-02 : 10:08:24
|
| 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?Thanks,David |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-02 : 10:10:28
|
| i think thats because the transaction which is inside ssis is locking excel down.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
ostinoh
Yak Posting Veteran
66 Posts |
Posted - 2011-12-02 : 10:20:31
|
| Is there any way around that besides not having the transaction in SSIS? We want the job to be able to rollback if it fails.Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-02 : 10:39:38
|
quote:
Originally posted by ostinoh
Is there any way around that besides not having the transaction in SSIS? We want the job to be able to rollback if it fails.Thanks
without enclosing it in transaction you cant implement atomicity ie all or none stateThe closest thing you can do alternatively is to add an onerror event handler for package onerror event and inside that add logic to explicitly do the rollback in case of package error.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-12-02 : 11:03:35
|
| Is excel the source or destinationIf source, take a copy of the excel file and work off that.If destination write to a new file then rename. Might be an issue if other users are working on the file at the same time.If that's all it does then it doesn't need a transaction - just elete the file on failure.Does it have to be an excel file?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
ostinoh
Yak Posting Veteran
66 Posts |
Posted - 2011-12-02 : 11:14:07
|
| I have a Sequence Container that has my Execute Sql Task and Data Flow Task. And outside that box I have Commit Tran and another box Rollback Tran. Should I have those two addtional boxes or can the Sequence Container do the Begin tran and commit or rollback?Thanks,David |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-12-02 : 11:19:59
|
| Sounds like an excel destination.How long does each process take?I wouldn't have thought that the file will be locked until the dataflow starts - you would need to verify that if the sql task takes a long time, if it does lock from the start and the sql task takes time then change it so that the sql task can run independantly and doesn't need to be part of the transaction.Also check whether the rollback actually does rollback the file.I wouldn't have thought putting the transaction inside the sequence container would make any difference. You could always test it.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
ostinoh
Yak Posting Veteran
66 Posts |
Posted - 2011-12-02 : 11:27:01
|
| Yes - Excel is the destination. Our users create a connection to the sql database and pull the data from that table and pivot on it.The whole process takes anywhere from 11-30 minutes start to finish.I would like to include a image of what I have but cannot seem to get that work here. Thanks,David |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-12-02 : 11:43:46
|
| You mean the users are running the package?I thought they were trying to get data from the excel file.Or do they need to access the file and the database - then it could be the dtabase that is blocking.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
ostinoh
Yak Posting Veteran
66 Posts |
Posted - 2011-12-02 : 11:50:51
|
| Sorry if this is confusing. Sometimes my brain thinks my hands know what to type. Here is a small break down.My SSIS package pulls data from table (A) in a production sql server and inserts it into table (B) on another sql server.Our users connect (with AD Security Group) to table (B) via excel and pull that data and pivot whatever they want on it. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-02 : 12:09:15
|
quote:
Originally posted by ostinoh
Sorry if this is confusing. Sometimes my brain thinks my hands know what to type. Here is a small break down.My SSIS package pulls data from table (A) in a production sql server and inserts it into table (B) on another sql server.Our users connect (with AD Security Group) to table (B) via excel and pull that data and pivot whatever they want on it.
oh ok.then putting these tasks in transaction will lock both the tables until transaction is either commited/rolledback------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-12-02 : 12:11:21
|
| Insert into a staging table on the destination server then if that is successful insert from the staguing table to the actual table - no need for transactions (unless it does a delete / truncate first).Will still lock but hopefully for much less time.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
ostinoh
Yak Posting Veteran
66 Posts |
Posted - 2011-12-02 : 12:21:54
|
Thank you both for the responses I not have some things to think about and how we want to do this.Thanks again for the tips.  David |
|
|
|
|
Any Reason SSIS Transaction would lock up?