how to solve this problem in ssis 2005? I'm used script component for validation.
I'm validating each column in script component using VB Script. while import high volume of data more 50 million. If any one column value failed based on my validation script. That time getting following error in ssis (Deadlock detected while trying to lock variables. The locks cannot be acquired after 16 attempts. The locks timed out.)
Public Overrides Sub ValidateColumn_ProcessInputRow(ByVal Row As ValidateColumnBuffer) Try Row.IsSkipped = False 'Declare and set variables Dim Var As IDTSVariables90 = Nothing Dim str As String
Me.VariableDispenser.GetVariables(Var)
Me.VariableDispenser.LockForRead("User::strEligibilityKeyEmptyMsg")
Me.VariableDispenser.LockForRead("User::strMemberIDEmptyMsg")
Me.VariableDispenser.LockForRead("User::strServiceFromDateEmptyMsg")
Me.VariableDispenser.LockForRead("User::strServiceToDateEmptyMsg")
Me.VariableDispenser.LockForRead("User::strDxCode1EmptyMsg")
Me.VariableDispenser.LockForRead("User::strAllowedAmountEmptyMsg")
Me.VariableDispenser.LockForRead("User::strPaidAmountEmptyMsg")
Me.VariableDispenser.LockForRead("User::strPaidDateEmptyMsg")
Me.VariableDispenser.LockForRead("User::strFacilityIDEmptyMsg")
Me.VariableDispenser.LockForRead("User::strServiceTypeEmptyMsg")
Me.VariableDispenser.LockForRead("User::strServiceFromDateInvalidMsg")
Me.VariableDispenser.LockForRead("User::strPaidDateInvalidMsg")
Me.VariableDispenser.LockForRead("User::strServiceToDateInvalidMsg")
'Field length and Expression validation
Row.RowId = intRowId + 1
intRowId = intRowId + 1
Row.IsValidationSuccess = True
'Required field validation
'User::strEligibilityKeyEmptyMsg - Eligibility Key is empty !
If (Row.EligibilityKey_IsNull Or Row.EligibilityKey.Trim().Length = 0) Then
Row.IsSkipped = True
Row.ErrorDesc = Var("User::strEligibilityKeyEmptyMsg").Value.ToString()
Return
End If
'User::strMemberIDEmptyMsg - MemberID is empty !
'If (Row.MemberIDClaimantID_IsNull Or Row.MemberIDClaimantID.Trim().Length = 0) Then
' Row.IsSkipped = True
' Row.ErrorDesc = Var("User::strMemberIDEmptyMsg").Value.ToString()
' Return
'End If
'User::strServiceFromDateEmptyMsg - ServiceFromDate is empty !
If (Row.ServiceFromDate_IsNull Or Row.ServiceFromDate.Trim().Length = 0) Then
Row.IsSkipped = True
Row.ErrorDesc = Var("User::strServiceFromDateEmptyMsg").Value.ToString()
Return
End If
'strServiceFromDateEmptyMSg - Invalid ServiceFromDate !
Dim ServiceFromDate1 As Match = Regex.Match(Row.ServiceFromDate, "^([1][012]|[0]?[1-9])[/]([3][01]|[12]\d|[0]?[1-9])[/]([1-8][0-9][0-9][0-9]|9000)$")
'Dim ServiceFromDate1 As Match = Regex.Match(Row.ServiceFromDate, "^(([1-9])|(0[1-9])|(1[0-2]))\/(([0-9])|([0-2][0-9])|(3[0-1]))\/(([0-9][0-9])|([1-9][0,9][0-9][0-9]))$")
If Not ServiceFromDate1.Success Then
Row.IsSkipped = True
Row.ErrorDesc = "Invalid ServiceFromDate"
Return
End If
'User::strServiceToDateEmptyMsg - ServiceToDate is empty !
If (Row.ServiceToDate_IsNull Or Row.ServiceToDate.Trim().Length = 0) Then
Row.IsSkipped = True
Row.ErrorDesc = Var("User::strServiceToDateEmptyMsg").Value.ToString()
Return
End If
'strServiceToDateEmptyMSg - Invalid ServiceToDate !
Dim ServiceToDate1 As Match = Regex.Match(Row.ServiceToDate, "^([1][012]|[0]?[1-9])[/]([3][01]|[12]\d|[0]?[1-9])[/]([1-8][0-9][0-9][0-9]|9000)$")
'Dim ServiceToDate1 As Match = Regex.Match(Row.ServiceToDate, "^(([1-9])|(0[1-9])|(1[0-2]))\/(([0-9])|([0-2][0-9])|(3[0-1]))\/(([0-9][0-9])|([1-9][0,9][0-9][0-9]))$")
If Not ServiceToDate1.Success Then
Row.IsSkipped = True
Row.ErrorDesc = "Invalid ServiceToDate"
Return
End If
'User::strDxCode1EmptyMsg - DxCode1 is empty !
If (Row.DxCode1_IsNull Or Row.DxCode1.Trim().Length = 0) Then
Row.IsSkipped = True
Row.ErrorDesc = Var("User::strDxCode1EmptyMsg").Value.ToString()
Return
End If
'User::strAllowedAmountEmptyMsg - AllowedAmount is empty !
If (Row.AllowedAmount_IsNull) Then
Row.IsSkipped = True
Row.ErrorDesc = Var("User::strAllowedAmountEmptyMsg").Value.ToString()
Return
End If
'Placeofservice value more than 2 digits!
If Row.PlaceofService.ToString().Length > 2 Then
Row.IsSkipped = True
Row.ErrorDesc = "Placeofservice value more than 2 digits!"
Return
End If
'MemberAmount is empty !
If (Row.MemberAmount_IsNull) Then
Row.MemberAmount = 0
End If
'User::strPaidAmountEmptyMsg - PaidAmount is empty !
If (Row.PaidAmount_IsNull) Then
Row.IsSkipped = True
Row.ErrorDesc = Var("User::strPaidAmountEmptyMsg").Value.ToString()
Return
End If
'User::strPaidDateEmptyMsg - PaidDate is empty !
If (Row.PaidDate_IsNull Or Row.PaidDate.Trim().Length = 0) Then
Row.IsSkipped = True
Row.ErrorDesc = Var("User::strPaidDateEmptyMsg").Value.ToString()
Return
End If
'strPaidDateEmptyMSg - Invalid PaidDate !
Dim PaidDate1 As Match = Regex.Match(Row.PaidDate, "^([1][012]|[0]?[1-9])[/]([3][01]|[12]\d|[0]?[1-9])[/]([1-8][0-9][0-9][0-9]|9000)$")
'Dim ServiceToDate1 As Match = Regex.Match(Row.ServiceToDate, "^(([1-9])|(0[1-9])|(1[0-2]))\/(([0-9])|([0-2][0-9])|(3[0-1]))\/(([0-9][0-9])|([1-9][0,9][0-9][0-9]))$")
If Not PaidDate1.Success Then
Row.IsSkipped = True
Row.ErrorDesc = "Invalid PaidDate"
Return
End If
'User::strFacilityIDEmptyMsg - FacilityID is empty !
If (Row.FacilityID_IsNull Or Row.FacilityID.Trim().Length = 0) Then
Row.IsSkipped = True
Row.ErrorDesc = Var("User::strFacilityIDEmptyMsg").Value.ToString()
Return
End If
'User::strServiceTypeEmptyMsg- ServiceType is empty !
If (Row.ServiceType_IsNull Or Row.ServiceType.Trim().Length = 0) Then
Row.IsSkipped = True
Row.ErrorDesc = Var("User::strServiceTypeEmptyMsg").Value.ToString()
Return
End If
'Invalid service Type!
If (Row.ServiceType.Trim().ToUpper <> "IPS" And Row.ServiceType.Trim().ToUpper <> "IPA" And Row.ServiceType.Trim().ToUpper <> "OPM" And Row.ServiceType.Trim().ToUpper <> "OPS" And Row.ServiceType.Trim().ToUpper <> "ER" And Row.ServiceType.Trim().ToUpper <> "EM" And Row.ServiceType.Trim().ToUpper <> "ANC") Then
Row.IsSkipped = True
Row.ErrorDesc = "Invalid Service Type!"
Return
End If
'In case of any char or special char in integer/money/numeric field, importer must skip those records.
If (Not IsNumeric(Row.AllowedAmount)) Then
Row.IsSkipped = True
Row.ErrorDesc = "Invalid char/speceial char is in Allowed Amount"
Return
End If
'In case of any char or special char in integer/money/numeric field, importer must skip those records.
If (Not IsNumeric(Row.PaidAmount)) Then
Row.IsSkipped = True
Row.ErrorDesc = "Invalid char/speceial char is in Paid Amount"
Return
End If
'In case of any char or special char in integer/money/numeric field, importer must skip those records.
If (Not IsNumeric(Row.MemberAmount)) Then
Row.IsSkipped = True
Row.ErrorDesc = "Invalid char/speceial char is in Member Amount"
Return
End If
'User::strServiceFromDateInvalidMsg - ServiceFromDate is Invalid Format !
If (Not IsDate(Row.ServiceFromDate)) Then
Row.IsSkipped = True
Row.ErrorDesc = Var("User::strServiceFromDateInvalidMsg").Value.ToString()
Return
End If
'User::strPaidDateInvalidMsg - PaidDate is Invalid Format !
If (Not IsDate(Row.PaidDate)) Then
Row.IsSkipped = True
Row.ErrorDesc = Var("User::strPaidDateInvalidMsg").Value.ToString()
Return
End If
'User::strServiceToDateInvalidMsg - ServiceToDate is Invalid Format !
If (Not IsDate(Row.ServiceToDate)) Then
Row.IsSkipped = True
Row.ErrorDesc = Var("User::strServiceToDateInvalidMsg").Value.ToString()
Return
End If
'PaidDate greater then default date !
If (Convert.ToDateTime(Row.PaidDate.Trim()) > Convert.ToDateTime("12/31/9000")) Then
Row.IsSkipped = True
Row.ErrorDesc = "PaidDate greater than default dates !"
Return
End If
Var.Unlock()
Catch ex As Exception
Row.ErrorDesc = ex.Message().ToString()
End Try
End Sub
