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.
| 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 CONTAINERSecond 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? |
 |
|
|
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). |
|
|
|
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 |
|
|
|
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... |
|
|
|
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? |
|
|
|
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 |
|
|
|
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. |
|
|
|
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. |
|
|
|
|
|
|
SSIS Help.