Need help in ssis transactions

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Need help in ssis transactions

Need help in ssis transactions

Rate Topic

Display Mode

Topic Options

Author

Message

sathiyan00

Posted Tuesday, October 02, 2012 10:50 PM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 6:34 AM
Points: 60, Visits: 325

Hi all,
In my package i have one sequence container,in that i have 3 data flow task ...each one access data from previous data flows target tables data.....

Sequence containers transaction level: required
all 3 data flow task transaction level:supported
isolation level :serializable

DATAFLOW1.SOURCE:excel_source TARGET:cust_stagging,product_stagging,sales_stagging
DATAFLOW2.SOURCE:cust_stagging,product_stagging TARGET:cust_dim,product_dim
DATAFLOW3.SOURCE:sales_stagging,cust_dim,product_dim TARGET:sales_fact

my flow got struck in dataflow2..because of transaction...without transaction its running fine...but i want to roll back if any dataflow task fails ...so i need transaction...
I think need to change isolation level ...any suggestions.....

Thanks and Regards
Sathiyan.R

Post #1367398

Stewart "Arturius" Campbell

Posted Wednesday, October 03, 2012 1:57 AM

Hall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 3:48 AM
Points: 3,125, Visits: 4,311

First, check that you have control flow from data flow 1 to dataflow 2 to data flow 3.

Then consider, in data flow 2, using WITH(NOLOCK) as part of the select in the data source component (the reason the flows appear to stick is that the relevant destination tables have an exclusive lock placed on them by the previous process, which has not been released once the process has completed). Please note the warnings regarding the use of NOLOCK and READUNCOMMITTED in the above URL.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Post #1367446

Evil Kraig F

Posted Wednesday, October 03, 2012 2:08 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:47 AM
Points: 5,672, Visits: 6,115

Transactions and SSIS are... painful at best. However, make sure that your connections that you're using in your transactions are set to reuse same connection (keep connection... something like that, you'll know it) in the datasource properties. If you don't, you end up with multiple connections from the different objects blocking other components.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1368020

« Prev Topic | Next Topic »

Permissions