Date: 05/11/05 (SQL Server)    Keywords: database, sql

...on the same VBScript thing I'm trying to come up with in DTS. I'm sure this is another "it's very obvious, you just haven't put the piece together correctly" kind of question.

Okay, to recap fast: I'm importing a text file and shunting each record off into one of a series of possible places for some quick processing (I'm keeping the test cases in the SELECT CASE statement to the simplest possible options, so I can make sure I have a running skeleton before getting into any more complex processing)-- right now, it's simply splitting the line and copying it into a table (REF or ISA, depending). Then I realized...

[snip a bunch of stuff]

	dim MyDestConnREF
	set MyDestConnREF = CreateObject("ADODB.Connection")
	MyDestConnREF.Open = "Provider=SQLOLEDB.1;Data Source=TESLA;Initial Catalog=EDI;userid=sa'password=nothing"
	dim MyDestConnISA
	set MyDestConnISA = CreateObject("ADODB.Connection")
	MyDestConnISA.Open = "Provider=SQLOLEDB.1;Data Source=TESLA;Initial Catalog=EDI;userid=sa'password=nothing"

	select case ucase(trim(left(dtssource("Col001"),3)))
		case "REF"
			redim sArray(4)
			DTSDestination("recordnum") = recordnum
			DTSDestination("subrecordnum") = subrecordnum
			DTSDestination("type")	= sArray(0)
			DTSDestination("refnumqual") = sArray(1)
			DTSDestimation("refnum") = sArray(2)
			DTSDestination("description") = sArray(3)
			DTSDestimation("refid") = sArray(4)
		case "ISA"
			redim sArray(16)

Source: http://www.livejournal.com/community/sqlserver/26495.html