Comparing results from 2 queries

[Baz_Singh]

Hi,

I am looking for a vbscript that compares the result of 2 sql queries and if the result matches, it succeeds and the rest of the DTS package continues. However if the results do not match then to fail. This will be a standalone script and will be a step in the overall DTS. Any ideas much appreciated.

[Baz_Singh]

So.. a change of requirement.. Rather than comparing two sets of sql, I think it would be easier if i create the sql in a view.. The VBSCRIPT is to then check the value of the single field "Discrepancy" in this view and either be successful if the value of the field is 0 or fall over if it is not 0. Hope someone has some ideas.

[MarkT]

In your first post, what do the 2 queries return?

In your second post have you gotten the view SQL figured out?

[Baz_Singh]

Hey Mark.. going with the 2nd post.

So the sql in the view will return a single value or 1 or 0. 0 indicating all is well and to quit with success and 1 indicating there is a problem and quit with failure.

[MarkT]

So back to the last question, do you have the view working? If so what is the view name and field name?

[Baz_Singh]

view and field is below in the sql statement

SELECT DISCREPANCY FROM dbo.MDS_RecordCount_Vs_FolderCount

[MarkT]

Still not sure what you are after but maybe this is heading in the right direction.

Const adForwardOnly = 0
Const adReadOnly = 1
Const adUseServer = 2

Function Main()
	If Descripancy Then
		Main = DTSTaskExecResult_Failure
	else
		Main = DTSTaskExecResult_Success
	End If
End Function


Function Descripancy()
	Set objConnection = CreateObject("ADODB.Connection")
	Set objRecordset = CreateObject("ADODB.Recordset")

	With objConnection
		.ConnectionString = "" '<TODO>
		.Open
	End With
	
	With objRecordset
		.ActiveConnection = objConnection
		.CursorLocation = adUseServer
		.CursorType = adForwardOnly
		.LockType = adReadOnly
		.Source = "SELECT DISCREPANCY FROM dbo.MDS_RecordCount_Vs_FolderCount"
		.Open
	End With
	
	If objRecordset("DISCREPANCY") = 0 Then
		Descripancy = false
	Else
		Descripancy = true
	End If

	objRecordset.Close
	Set objRecordset = Nothing
	objConnection.Close
	Set objConnection = Nothing
End Function

[Baz_Singh]

I've kinda conjured up the below and it appears to work. Looks roughly similar to yours. Ok, what my goal is to have a vbscript that will look at the value for the field 'discrepancy' in the table dbo.MDS_RecordCount_Vs_FolderCount. Thereafter if the value is '0', the script is to do nothing, and quit. Please note the script will be in an activex task in a DTS. So if the result is 0 the script is successful and the DTS will move onto the next step. If the value is not 0, then the script is to quit with a failure and the DTS will stop there and not continue processing next task.

In what I have below, I have added a email function to be called upon if the script fails. It all appears to work. Now however I need this script to run after a delay of a minute. Reason being the step before this script is writing to a location and lots of other processing is taking place. Therefore when my sql (view) runs in this script it runs before the previous task (although has completed successfully) it is still processing. Hope that makes sense. I've been looking and there appears to be shell sleep method to make it delay for a minute but not sure where to incorporate it.


Dim connEWORK
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdText = &H0001
Const adOpenDynamic = 2
Const adOpenKeyset = 1


Function Main()

Dim SQL
Dim strRecipients
Dim CheckForDiscrepancy

Set connEWORK = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")

connEWORK.Provider = ""
connEWORK.Properties("Data Source").Value = ""
connEWORK.Properties("Initial Catalog").Value =""
connEWORK.Properties("User ID").Value = ""
connEWORK.Properties("Password").Value = ""
connEWORK.Properties("Integrated Security").Value = ""
connEWORK.ConnectionTimeout = 5
connEWORK.commandtimeout = 5

connEWORK.Open

SQL = "SELECT DISCREPANCY FROM dbo.MDS_RecordCount_Vs_FolderCount"

RS.Open SQL, connEWORK, adOpenKeyset, adLockReadOnly, adCmdText
CheckForDiscrepancy = RS.getString

RS.Close

Set RS = Nothing

connEWORK.Close

Set connEWORK = Nothing

If (CheckForDiscrepancy = 0) Then

Main = DTSTaskExecResult_Success

Else

Main = DTSTaskExecResult_Failure

strRecipients = "[email protected]"

Call sendEmail(strRecipients)

End If

End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Name: Function Send_Email

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function sendEmail(strRecipients)

Dim iMsg

Set iMsg = CreateObject("CDO.Message")

With iMsg

.To = "[email protected]"
.From = "[email protected]"
.Subject = "MDS Failure"

' Content of email

.TextBody = "DTS has errored in Step 9, the count of folders created does not match the count of records for the month" & vbCrLf & vbCrLf &_
"DTS to review and troubleshoot is 'MDS-1-Data Transformations and Supplier Reports Creation'" & vbCrLf & vbCrLf &_
"GBR00WRKD1"

.Send

End With

Set iMsg = Nothing

End Function

[MarkT]

I need this script to run after a delay of a minute. Reason being the step before this script is writing to a location and lots of other processing is taking place. Therefore when my sql (view) runs in this script it runs before the previous task (although has completed successfully) it is still processing. Hope that makes sense.

If I understand you, this can be done by setting the workflow of the package. To do this, select the 2 tasks that you need to run in sequence and then click the workflow menu option and select the option you need. By doing this you can set what order your tasks will run.

[Baz_Singh]

yup that is one option, but i believe in workflow 'on completion' will make the next step run even if the preceeding step fails..

[MarkT]

the go with on success