sql server dts:
hi!
I have a DTS package in which I am running validations on claims. If
they pass they get inserted into xstaging table, if they don't they get
placed into a hold table.
If the validation passes I want to check if there threshold >= 3% and
if so I want do not want to insert the records that pass validation
into the xstaging,I just want to end the pass branch there.
If threshold < 3% I want to insert the passed records into the xstaging
table.
I have the following as the activex script:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
'Const DTSSQLStgFlag_UseTrustedConnection = 256
Dim oConn_threshold
Dim oConn_input
Dim sFilename
Dim oConn_threshold_pass
Dim cn
Dim cmd
Dim rs
Dim Package
Set Package = DTSGlobalVariables.Parent
Set cn = Package.Connections("Load_claims_TEST")
set rs = CreateObject("ADODB.Recordset")
set cmd = CreateObject("ADODB.Command")
Set oConn_input = DTSGlobalVariables.Parent.Connections("Text File
(Source)")
sFilename= oConn_input.DataSource
Set oConn_input = Nothing
'execute stored procedure to run threshold
oConn_threshold.Execute =
"ases_mi60_source.dbo.threshold_calculation_claims"
if oConn_threshold.Execute = "SELECT threshold FROM Load_Summary
where threshold >= '3.00' and @p_filename=sub_filename" then
MsgBox "The threshold is" & oConn_threshold & "and failed"
oConn_threshold.Close
Set oConn_threshold = Nothing
Main = DTSTaskExecResult_Failure
Exit Function
else
oConn_threshold_pass.Execute
"ases_mi60_source.dbo.add_xstaging_claims_test"
MsgBox "The threshold is" & oConn_threshold_pass & "and passed"
oConn_threshold_pass.Close
Set oConn_threshold_pass = Nothing
Main = DTSTaskExResult_Success
End if
cn.Close
End Function
I'd appreciate any help on how to achieve this.
Thank you!
T.
