August 3, 2009 at 4:40 pm
well i am testing two tables with two diffrent data types, source table have nvarchar data type and destination table have int data type, i was told to build a package in such way that if million of rows are copied from surce to dest, incase an error occurs in a single row then it should skip that error and contnue the data processing, but incase if there are more than 50 bad rows evrything should be rolled over. so i was testing the package by creating two tables on source and dest with diffrent data types. i have 500 records on surce table with nvarchar data type and dest table have int data type. only those rows which are compatible with int data type are copied, and there are 5 such records. from your incormation i am check the error ouptu conditon but unable to roll back those 5 records.
August 4, 2009 at 7:04 am
John can you once more help me out to fail over the package with my current requirements. As I mentioned in my earlier email, we wil never know what causes the bad rows, it can be diffrent data type, network error, diffrent platform and so on.
August 4, 2009 at 11:00 am
What you need to do is set up the insert, along with the post-insert error checking logic into a single transaction so if you reach your ceiling of 50 errors, you can rollback the inserts. This link should help with that:
August 4, 2009 at 11:36 am
John thanks for your response, i am going thorugh your link, mean while i have attached my control flow and data flow screnshot, can you please tell me where do i put that insertation logic.
thanks
August 4, 2009 at 11:42 am
Well you insertion logic is already there. It is your OLE DB Destination. If you want your data validations to happen at the database layer and redirect your error rows as your screen shot shows, you'll need to wrap your OLE DB Destination in a transaction with your error handling. That way, the error that you raise in your error handling logic will rollback the insert that happens in the OLE DB Destination.
By the way, I'm referring to everything that happens after the OLE DB Destination as your error handling logic as that is where your are redirecting your insert errors to.
August 4, 2009 at 11:54 am
ya I got you john, now how do i configure error handling in my oledb destination? can you please explain me in detail. greatly appriciated,
August 4, 2009 at 12:14 pm
You've already done it. The error handling is all of the stuff you've setup down stream from the OLE DB Destination. You set the error condition of your OLE DB Destination to redirect error rows so all of the stuff you've been working on is your error handling.
August 4, 2009 at 12:24 pm
do i need to create a transaction on event handler? what i am doing is that, in event handler under executable i choose an option data flow task instead of package and choose the option 'on error'. now i draged 'execute sql task' on the pane, now what logic should i write there to rolllback my data or fail the backage if condition dosent meet? do i am on the right direction? the main thing is that I couldnt find where and how to right the logic to fail the package.
thanks
August 4, 2009 at 12:36 pm
John I am assuming the other method also, you know pretty much about .net code. on event handlers, i am creating a scrip task and try to run this .net code to fail the package. i know i am just guessing and .net code is pretty much wrong. can you veryfy this .net script?(here errorrowcount is a variable that i have created on package)
Option Strict Off
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim errorrowcount As Integer
If errorrowcount > 50 Then
Dts.TaskResult = Dts.Results.Failure
Else
Dts.TaskResult = Dts.Results.Success
End If
End Sub
End Class
August 5, 2009 at 1:37 pm
Sorry for the delay in my response.
You don't need to create an event handler for the OnError event. The article that I posted a link for shows you how to manage transactions in your SSIS package. For what you want to do, you'll want to set the TransactionOption to 'Required' for your data flow. This will treat the entire data flow as one transaction so an error in that data flow should roll back anything that the data flow does.
For your example, this option should force the 5 rows that were 'good' to be rolled back and they will not be inserted into your table.
August 6, 2009 at 9:14 am
john thanks for your response, i changed the transaction level to required at my data flow, but in this case package will fail in both condition either 50. as you know we need to fail a package when <=50 condition doesnt meet. did i do any mistake, i just went to data flow went to properties and changed the transaction level to required.
August 6, 2009 at 9:16 am
So you are saying that the rollback is happening correctly for your error condition when there are more than 50 errors?
Are you also saying that it is rolling back when there are less than 50 errors?
August 6, 2009 at 9:40 am
John let me explain you, as you know i have 5 good records out of 500, and i set a condition <=50, so chaging transaction level to required it will fail the package on condition<=50, now i changed the condition <=500, in this senario 5 records were supposed to be inserted at dest table but the package will fail instead. so its rolling back in both condition.
August 6, 2009 at 9:45 am
But I thought you wanted to fail with errors >= 50 not <= 50? Can you post a new screen shot of your current data flow?
August 6, 2009 at 9:52 am
Yes john i need to fail a package if there are more than 50 errors, means >50 upto 50 package should not fail. here I have attached 2 screnhot it will make you more clearer. and thanks once again for your prompt response.
Viewing 15 posts - 31 through 45 (of 110 total)
You must be logged in to reply to this topic. Login to reply