sql server dts: I get the following error when I try to parse the following ActiveX Script in
a DTS package:

Error Description: Error Code 0
Error Source=Microsoft VBScript Compilation Error
Error Description: Syntax Error
Error on Line 20

(Note that it doesn't state exactly what the syntax error is - it just says
that there is a syntax error. Not too helpful.)

Here is an excerpt of the code. Line 20 (the line that the parser is
failing on) is the one that starts with "Sub UpdateCSRs (sqlCmd, connStr)"

Function Main()
Sql = "SET IDENTITY INSERT ON" & CrLf
Sql = Sql & "DELETE FROM CSRs" & CrLf
Sql = Sql & "INSERT INTO CSRs" & CrLf
Sql = Sql & "(" & DTSGlobalVariables("COLUMN_LIST").Value & ")" & CrLf
Sql = Sql & "SELECT " & DTSGlobalVariables("COLUMN_LIST").Value & CrLf
Sql = Sql & "FROM [192.168.1.36].OrbitTelecom.dbo.CSRs" & CrLf
Sql = "SET IDENTITY INSERT OFF" & CrLf

UpdateCSRs Sql, DTSGlobalVariables("EDN_CONN_STR").Value
UpdateCSRs Sql, DTSGlobalVariables("RES_CONN_STR").Value
UpdateCSRs Sql, DTSGlobalVariables("EDS_CONN_STR").Value
UpdateCSRs Sql, DTSGlobalVariables("EMN_CONN_STR").Value
UpdateCSRs Sql, DTSGlobalVariables("INT_CONN_STR").Value
UpdateCSRs Sql, DTSGlobalVariables("VMD_CONN_STR").Value

Sub UpdateCSRs (sqlCmd, connStr)
Set cn = CreateObject("ADODB.Connection")
cn.ConnectionString = connStr
cn.Open connStr
cn.Execute sqlCmd
cn.Close()
Set cn = Nothing
End Sub
Main = DTSTaskExecResult_Success

One cannot embed a sub within a function.

Move the text from "Sub UpdateCSRs (sqlCmd, connStr)" to "End Sub" to after
the "End Function"
--
Russel Loski, MCSD.Net