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.Connection")
  Set oRs = CreateObject("ADODB.Recordset")
  Set oCmd = CreateObject("ADODB.Command")

  '** Set DAO connection properties
  oConn.Open "Driver={SQL Server};Server=test;Database=OCDC;user;Pwd=password;"
   
  '** 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
ocdcAsked:
Who is Participating?
 
John_VidmarCommented:
SSIS Execute SQL Task is used to run SQL commands, you supplied a VBScript function.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>(it turns to red color).
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'
0
 
ocdcAuthor Commented:
>(it turns to red color) means, it doesnt run
0
 
ocdcAuthor Commented:
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.
0
 
kevwitData Science Solutions ManagerCommented:
That code won't work in a SQL Task component. You could use it in a Script Component.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.