VB Script ActiveX Task for SQL 2000 DTS Package

Question

I'm trying to load data from an external csv file that needs to be massaged before being inserted into a SQL table. Reading the file is fine, but when I try to loop through the stream and load an array using the SPLIT function I get a "Expected End of Statement" error.

Do While Not txtFile.AtEndOfStream

    strText = txtFile.ReadLine

    Dim dataArray() As String = Split(strText, ",")    -- Here's where it breaks

    ...

    build sql statement to insert using the zero based array

    RS.Open strSQL, dbConn, adOpenKeyset

Loop

txtFile.Close

I've looked at the BOL and MSDN, but I'm still getting the error.

Answer 1

From memory SQL 2k DTS uses vb scripts so no types.

Dim dataArray = split(strText,",")

Answer 2

VBScript does not support typed variables. VBScript does not support assigning a value in the Dim statement. Use:-

Dim dataArray()
dataArray = Split(strText, ",")

However having said that since this is DTS task why aren't you creating a text csv data source in the transfrom rather than manually creating VBScript code to consume the CSV.