August 6, 2009 at 10:33 am
So, you tested it with your failure condition set to fail (default output) when there are > 50 errors. That worked as expected.
Then, you changed the condition to only fail when the error count became > 500 with the hopes that this condition would not fail as there are only 495 error rows, but this sent the rows down the default path and a failure happened anyways, correct?
I would place a Data Viewer just prior to the Conditional Split so you can check the value of ErrorCount for your data set. Do this and let me know 1) how many rows are in the pipeline; 2) what are the values of the ErrorCount column - they should all be the same!
August 6, 2009 at 11:19 am
Yes John you are right when I tested with 495 errors, remaining 5 good records were supposed to inserted on destination table, but instead the package failed. Now how do i add a data viewer to prior to conditional split and while adding data viwer should i put transaction option on supported or required in data flow? I didnt see any data viewer transformation on data flow.
thanks
August 6, 2009 at 11:22 am
A data viewer is not a transformation. If you double-click on the green arrow prior to the Conditional Split, it will open up the Data Flow Path Editor. There, you can go to the Data Viewers screen and select 'add'. Accept the defaults and press 'ok'. This will stop the SSIS pacakge execution at that point and show you the data in the pipeline. It's a great debugging tool.
August 7, 2009 at 8:27 am
John I was busy with my other ssis task and i am really sorry for the late response, so i created data viewer with both option 'supported' and 'required'. In supperted option I executed package with both condition <= 50 and <=500 and i get same result of 495 rows displayed on both condition. And in required option while running with both condition there are 0 rows displayed.
thanks
August 7, 2009 at 9:42 am
OK, just so we don't have to go back and forth on this 10 more times, can you get a screen shot of the package execution with the data viewer resuts? Also, delete the data viewer and get a screen shot of the pacakge execution with both transaction options.
August 7, 2009 at 9:47 am
here I have attached screnshot with both option and the condition is <=50, the first screnshot is with required option and the 2nd one is with supported option.
thanks
August 7, 2009 at 10:11 am
I walked through my example package that I've set up to play with this and it works as expected for me. When the data flow is set to Required and the error count exceeds the limit, an error is raised and the package rolls back the 'good' rows that were inserted. When the error count limit is not exceeded, no error is raised and the error rows go to the error destination (which would be a flat file in your case).
When the data flow is set to 'Supported' and the error count exceeds the limit, an error is raised and the 'good' rows remain in the table - they are not rolled back. When the error count is not exceeded, the rows remain in the table and the error rows go to the error destination (again, a flat file in your case).
My package is working like we want it to. One thing I noticed from your screen shot is that when you're data flow is set to 'Required', it doesn't appear that any of the rows make it into the error redirect? Can you post another screen shot of the top part of the data flow that I can't see from your last screen shot?
August 7, 2009 at 10:24 am
first one is required and second one is supported and both are exeucted with <=50 condition.
August 7, 2009 at 10:30 am
This data flow is not being executed, what's happening in your Control Flow? Can you give another screen shot of your control flow for the 'Required' option run?
August 7, 2009 at 10:38 am
this screnshot is of control flow with condition<=50 with required option on data flow and supported option in control flow.
thanks
August 7, 2009 at 10:38 am
sorry forget to a attach the file
August 7, 2009 at 10:43 am
From your data flow screen shot, it does not appear that any of the data flow is being executed. Your control flow shows the data flow as failing. What error is your data flow showing?
August 7, 2009 at 10:49 am
Error: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.
August 7, 2009 at 10:58 am
Ah, ha. That is one of the requirements (from the article I posted for you) for running Required transaction option in SSIS. You must have MSDTS service running. That is why your data flow is not being executed. Go back through the article and read up on the requirements of using transaction in SSIS.
August 7, 2009 at 11:02 am
thanks but how do i run msdtc in my local machine, its just client machine with BI installed.
Viewing 15 posts - 46 through 60 (of 110 total)
You must be logged in to reply to this topic. Login to reply