August 12, 2009 at 9:39 am
so in my case I need to put transaction option required at control flow? since I have execute task connected with data flow task on control flow. After doing this also my package doenst execute when i put the condition <=50. where i am wrong now.
August 12, 2009 at 9:42 am
August 12, 2009 at 9:47 am
its on execute sql task, and when i run a package with transaction option required on control flow, only truncate command will pop up with green and data flow task with yellow and rest of the task on data flow doent do anything and intresting thad debug would stuck, it doesnt give any message whether debug is completed or not.
August 12, 2009 at 11:48 am
John did you came up with any new idea, because on top of that i need to add email task also.
August 12, 2009 at 12:31 pm
What else are you doing in your control flow? Have you tried to create a container, put the truncate command and data flow into it and set the transaction level?
August 12, 2009 at 12:49 pm
here I have attached screnshot for control flow and data flow after creating container and setting container transaction level required and rest of the transaction option would be supported. when I execute the package, package will stuck on debug mode for long time, it doesnt show any message whether the execution is completed or not.
August 12, 2009 at 1:52 pm
August 12, 2009 at 2:09 pm
ya theres a blocking on the db, and while trying to query the dest table, query is just executing,not completed. so i have to stop debugging to come in the original state
August 12, 2009 at 3:01 pm
now weird things start happening, package is executing successfully in both condition i.e <=50 and <=500, since I have 495 bad records, my error rowcount variable condition is<=50, in this condition package was supposed to be failed, it used to failed before, now i dont know what happen it is executing successfully. and this is happenig in both option supported and required in data flow. i ignore the sequence container also.no luck, i dont know where i screwed up again
August 13, 2009 at 11:52 am
Wow, well obviously something has changed. That's the funny thing with programming anything, whether it be in SSIS, T-SQL, or a .net language, the programs will do exactly what you tell them to do.
I would suggest starting at the beginning and walking through your package. Test each task and make sure they are doing what they are supposed to be doing. Use the data viewers to verify the data in the data flow pipeline after each task. Troubleshooting this should not be too difficult.
One you are back to where the package is working like it was, go back to the blocking problem. It may be that since you've included the TRUNCATE command inside the same transaction as the data flow, that the TRUNCATE is holding a table lock on the table, which is preventing the data flow from locking the table to do the inserts. You'll need to diagnose the blocking problem by looking at the blocking processes and figuring out what resources they have blocked and why. Again, not too difficult to do, it just takes a bit of time and patience.
August 13, 2009 at 12:01 pm
any luck john, i am trying my best bu tnot working at all now.
August 13, 2009 at 12:13 pm
thanks for your response, here I have attached my data flow scren when I execute a package with <=50 condition, since I have 495 errors. at <=50 condition, the package was supposed to fail, but it succeeds. i check the data viewer at data flow everywhere, and it shows 495 records got transmitted on flat file. which is our error out put file. and transaction option is supported in both control flow and data flow, i havent used container this time.
August 13, 2009 at 2:40 pm
What you are seeing here is a problem with the conditional split. Those rows should not be getting routed to the flat file. What does the RowCount column value look like in the data viewer directly before the conditional split?
August 13, 2009 at 2:49 pm
Here I have attached a file with data viewer just before the conditional split. and condition is <=50
August 13, 2009 at 2:55 pm
OK, the error row count looks good, now what does your conditional split comonent look like?
Viewing 15 posts - 76 through 90 (of 110 total)
You must be logged in to reply to this topic. Login to reply