Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 SSIS Help.

Author  Topic 

tooba
Posting Yak Master

224 Posts

Posted - 2014-11-13 : 12:42:36
Hi,

I am having a problem in my SSIS Package. Simple package.

First Step:- Using SEQUENCE CONTAINER
Second Step:- Inside SEQUENCE CONTAINER truncating staging table through SQL Task and using data flow to inserting data from .CSV to staging table and then Updating some field through SQL Task.

Here is the problem. I am using SEQUENCE CONTAINER when I select SEQUENCE CONTAINER PROPERTIES (transaction Option = REQUIRED). Data flow task stay YELLOW forever. If I change SEQUENCE CONTAINER PROPERTIES (transaction Option = Supported ) everything working fine. Is anyone can help me what I am doing wrong?

I know someone want to know why I am using SEQUENCE CONTAINER PROPERTIES (transaction Option = REQUIRED). I want If any task fail everything roll back or nothing happen..

Thanks in advance.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-13 : 12:52:55
SSIS defaults to ISOLATION_LEVEL SERIALIZABLE which is the most restrictive. Your DF task could be waiting on some other transaction to finish. You can set this to a lower level (e.g. READ COMMITTED). It really depends on what is going on in the DF task. Is it more than just FlatFile Source -> OLEDB destination?
Go to Top of Page

tooba
Posting Yak Master

224 Posts

Posted - 2014-11-13 : 12:59:38
The first SQL Task is Truncate Staging Table and in DF reading data from .csv file and insert the data to staging table with some transformation (i.e Data Conversion, Derived Column).
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-13 : 13:09:15
Then, try a lower ISOLATION LEVEL and see if that changes anything, e.d. READ COMMITTED
Go to Top of Page

tooba
Posting Yak Master

224 Posts

Posted - 2014-11-13 : 13:25:51
If I am understand right, you are suggesting, In SEQUENCE CONTAINER PROPERTIES change IsolationLevel to READCommitted, right? If Yes.. I changed this properties and same thing DF is yellow and hanging there...
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-13 : 13:34:25
in the DF task, have any (or all) the rows been written to the destination?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-13 : 14:27:03
More info on SSIS with Transactions:

http://muxtonmumbles.blogspot.ca/2013/11/msdtc-in-ssis-development.html
Go to Top of Page

tooba
Posting Yak Master

224 Posts

Posted - 2014-11-13 : 16:29:42
in the DF task, have any (or all) the rows been written to the destination? = Most of rows are going to destination.
Go to Top of Page

tooba
Posting Yak Master

224 Posts

Posted - 2014-11-13 : 16:30:24
If I remove SEQUENCE CONTAINER TASK everything runs fine without any problem.
Go to Top of Page
   

- Advertisement -