ocdc
asked on
SSIS - Execute SQL task
I have the following script that I am using in Execute SQL Task box in SSIS package but I am getting an error.(it turns to red color). Thank you for your help.
Function Main()
Dim oConn, oRs, oCmd
'** Create ADO objects
Set oConn = CreateObject("ADODB.Connec tion")
Set oRs = CreateObject("ADODB.Record set")
Set oCmd = CreateObject("ADODB.Comman d")
'** Set DAO connection properties
oConn.Open "Driver={SQL Server};Server=test;Databa se=OCDC;us er;Pwd=pas sword;"
'** Set the active connection to the target database ADO Object
oCmd.ActiveConnection = oConn
' oCmd.CommandType = adCmdText
'** Prepare the SQL statement for retrieving a unique task id
sSQL = "SELECT Count(*) as NumRecords FROM ActiveDirectoryUsers"
'** Execute the SQL statement and retrieve the data
Set oRs = oConn.Execute(sSQL)
'** Move the data set cursor to the first record
If Not(oRs.EOF and oRs.BOF) Then
oRs.MoveFirst
End If
If oRs.Fields("NumRecords") = 0 Then
Main = DTSTaskExecResult_Failure
Else
Main = DTSTaskExecResult_Success
End If
Function Main()
Dim oConn, oRs, oCmd
'** Create ADO objects
Set oConn = CreateObject("ADODB.Connec
Set oRs = CreateObject("ADODB.Record
Set oCmd = CreateObject("ADODB.Comman
'** Set DAO connection properties
oConn.Open "Driver={SQL Server};Server=test;Databa
'** Set the active connection to the target database ADO Object
oCmd.ActiveConnection = oConn
' oCmd.CommandType = adCmdText
'** Prepare the SQL statement for retrieving a unique task id
sSQL = "SELECT Count(*) as NumRecords FROM ActiveDirectoryUsers"
'** Execute the SQL statement and retrieve the data
Set oRs = oConn.Execute(sSQL)
'** Move the data set cursor to the first record
If Not(oRs.EOF and oRs.BOF) Then
oRs.MoveFirst
End If
If oRs.Fields("NumRecords") = 0 Then
Main = DTSTaskExecResult_Failure
Else
Main = DTSTaskExecResult_Success
End If
ASKER
>(it turns to red color) means, it doesnt run
ASKER
I just need to know what I need to change in the above script to be able to execute it.
P.S. The above script is ActiveX vbs and it doesnt execute on SQl server 2008 r2 after setting it up as a Job to run.
Thank you.
P.S. The above script is ActiveX vbs and it doesnt execute on SQl server 2008 r2 after setting it up as a Job to run.
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That code won't work in a SQL Task component. You could use it in a Script Component.
Define 'it', and post the first error message that appears in the Progress tab when it is executed.
>sSQL = "SELECT Count(*) as NumRecords FROM ActiveDirectoryUsers"
You could also add this in a Data Flow as a source, then have a Row Count after it which reads the row count and sets the return value to a parameter. Then make all the green arrows after that conditional based on 'IF @row_count == 0'