August 7, 2009 at 11:12 am
Go to Control Panel>>Administrative Tools>>Component Serivces. Expand Component Services>>Computers, right-click on My Computer and Start MS DTC.
August 7, 2009 at 12:14 pm
thanks john, well seems to be that i dont have permission to start msdtc services, i will figure out later on, now i have one more question, on the same data flow, if my package successfully execute within 50 errors then i nedd to get an email with that error, how do i set up an email for an error.
August 7, 2009 at 1:35 pm
To notify you via email if your data flow reaches the error condition, I'd create an OnError Event Handler on the data flow task and use the send mail task to send you an e-mail. You'll most likely need to play around with the expression builder to format a mail message the way you want it.
August 7, 2009 at 2:07 pm
So you mean I need to choose 'on error' option on event handlers tab and Executable would be 'data flow task' to get an email after i get an error with in 50 numbers, because till 50 error my package should suceed.
thanks
August 10, 2009 at 4:23 pm
Yes. If you put a Send Mail task in the OnError event, you'll get an e-mail when the task raises an error.
August 11, 2009 at 11:45 am
John thanks for your response, I ran msdtc services in my computer and was able to rollback the data with transaction option 'required' but the email task didnt work when it raise an error on flat file it dosent send any email and there are no information in execution results also. do i need to do something with expression in send email task at event handler?
but there is another confusion regarding package failure, like I have 5 records on dest table, now when <=50 condition doesnt meet those 5 records should be on that dest table instead it is truncating those 5 records when package fails.
August 11, 2009 at 12:47 pm
So you've got 2 issues here.
1. E-mail notification on error - you said that it did not send an email when your flat file destination encountered an error. What error did it raise on the flat file? Does it send out an error email for any other errors? What about when your error count is too high?
2. Package transactional behaviour not working as expected - when using the required option, any error in the data flow will rollback the work. In the setup that you and I worked on, we are manually raising an error using RAISERROR to force the failure and the rollback. If you have an error elsewhere in the data flow, such as your flat file destination, it will rollback the transaction also. Why would you not want to rollback after an error?
August 11, 2009 at 1:39 pm
John we wil discuss about email later on, lets go to the second issue, i am giving you an example, every day dat will load from source to dest. Now suppose one day there are more than 50 errors, in this senario our package execution should fail, in our example it will fail but on top of that it will truncate all the prior records on the destination table also. We just need to fail the package incase more than 50 errors come, we dont need to truncate the records which were alredy there in dest table .
We were able to solve one issue that was when there were more than 50 errors our package was failing but it was inserting good records on table, now its not happening that, in our situation we are
still unable to rollback, instead we are truncating the dest table. i think this make sense to you.
August 11, 2009 at 1:47 pm
I did not know you had a step in the package to truncate the table. Where is that happening? Inside the data flow or in the control flow?
August 11, 2009 at 1:55 pm
ohh sorry i thougt u knew, i have truncation part on control flow, when the data loads from source to destination, I need to truncate the dest table first and then load the data.
August 11, 2009 at 2:24 pm
OK, then the transaction that you are creating (by setting the Transaction Option to Required) needs to be scoped out to include the Truncate. Instead of setting the Transaction Option at the Data Flow level, you'll need to set it at the package level, or create a container that holds both the Truncate and the Data Flow and set it there.
August 11, 2009 at 3:36 pm
you mean I need to put trancational option 'supported' on data flow and 'required' on control flow.
August 11, 2009 at 3:42 pm
Yes, but where in the control flow will depend on how large you want to scope the transaction. You wouldn't just put it on the Truncate task. You need to put it on a container that contains both the Truncate task and the Data flow. That container could be a Loop, Sequence containter, or the package itself (as the package is nothing more than a container).
August 12, 2009 at 8:08 am
Now what container do I have to use inside contorl flow, as you know I have execute sql task connected with data flow task on control flow. So you mean I need to put both items inside for loop container or squence container?
August 12, 2009 at 9:35 am
Keep in mind that the package itself is a container. If all you have in your control flow are those 2 tasks and you want both of them to run inside a single, atomic transaction, set the Transaction Option property to Required at the package level.
If you have other thinkgs going on in your control flow that you want to keep outside the transaction, you'll need to put the Execute SQL task and Data Flow into a container and set that container to Required.
Viewing 15 posts - 61 through 75 (of 110 total)
You must be logged in to reply to this topic. Login to reply