Solved

SSIS - Execute SQL task

Posted on 2014-01-14
5
459 Views
Last Modified: 2016-02-10
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
0
Comment
Question by:ocdc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39780149
>(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
 

Author Comment

by:ocdc
ID: 39780163
>(it turns to red color) means, it doesnt run
0
 

Author Comment

by:ocdc
ID: 39780166
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
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 500 total points
ID: 39780208
SSIS Execute SQL Task is used to run SQL commands, you supplied a VBScript function.
0
 
LVL 1

Expert Comment

by:kevwit
ID: 39822651
That code won't work in a SQL Task component. You could use it in a Script Component.
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question