ActiveBatch Execute SQL Statement vbs Script


option explicit

' Parameters
const CONNECTION_STRING = "${CONNECTION_STRING}"
const SQL_STATEMENT = "${SQL_STATEMENT:b}"
const TIMEOUT= "${SQL_TIMEOUT}"

' ADO Constants
const adCmdText = 1
const adExecuteRecord = 0
const adExecuteNoRecords = &h80
const adStateOpen = 1

'- BEGIN -
dim oBase64, oConnection, oRecord

' Open connection
set oConnection = CreateObject("ADODB.Connection")
call oConnection.Open(CONNECTION_STRING)

' Issue statement
set oBase64 = CreateObject("AbatScripting.Base64")
wscript.echo "Running statement:"
wscript.echo oBase64.Decode(SQL_STATEMENT)
on error resume next
oConnection.CommandTimeout=TIMEOUT

call oConnection.Execute(oBase64.Decode(SQL_STATEMENT), adCmdText + adExecuteNoRecords)

if (oConnection.Errors.Count <> 0) then
on error goto 0
dim isError
isError =DisplayStatementErrors(oConnection)
if isError then
wscript.quit 101
else
wscript.quit 0
end if
end if
on error goto 0

wscript.quit 0
'- END -

'
' DisplayStatementErrors
'
function DisplayStatementErrors(byval oConnection)
dim oError
dim countainsError
countainsError=false
wscript.echo "Statement error:"
for each oError in oConnection.Errors
if Left(oError.Description,7)<> "Warning" then
wscript.echo oError.Description
countainsError=true
end if
next
DisplayStatementErrors=countainsError
end function


Connection String for Ole db mysql connection for SSIS

Data Source=localhost;User ID=userid;Initial Catalog=;Provider=MSDASQL;Persist Security Info=True;