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

I've asked this question before in a vaguely similar form. Still beating my head against the problem when I get a few minutes free.

Is it possible to:

1. Set the destination table in a transform data task, and
2. Set up the transformations in a transform data task

...in an ActiveX Script task in the same DTS package (SQL Server 2000)? I've read a few extremely vague forum posts on a few disparate boards that make me believe this is possible. When I try the first, though, I always seem to modify the database name the package is pointing to, rather than the table within the database.

Function Main()

	dim pkg
	dim conTextFile 
	dim conSQLDest
	dim stpEnterLoop
	dim stpFinished

	dim intLocation1
	dim intLocation2
	intLocation1 = instrrev(DTSGlobalVariables("gv_FileFullName").Value, "\")
	intLocation2 = instr(DTSGlobalVariables("gv_FileFullName").Value, ".")

	DTSGlobalVariables("gv_FileTableName") = Mid(DTSGlobalVariables("gv_FileFullName").Value, intLocation1+1, intLocation2-intLocation1-1)
	msgbox "FileTableName: " & DTSGlobalVariables("gv_FileTableName").Value

	set pkg = DTSGlobalVariables.Parent
	set stpEnterLoop = pkg.Steps("DTSStep_DTSDataPumpTask_1")
	set stpFinished = pkg.Steps("DTSStep_DTSActiveScriptTask_5")
	set conTextFile = pkg.Connections("Text File (Source)")
	set conSQLDest = pkg.Connections("SQLServerDestination")

	' We want to continue with the loop only of there are more
	' than 1 text file in the directory.  If the function ShouldILoop
	' returns true then we disable the step that takes us out of the package
	' and continue processing

	if ShouldILoop = True then
		stpEnterLoop.DisableStep = False
		stpFinished.DisableStep = True
		conTextFile.DataSource = DTSGlobalVariables("gv_FileFullName").Value
		conSQLDest.DataSource = "[XTend_Load].[dbo].[" & DTSGlobalVariables("gv_FileTableName").Value &"_JU]"
		Msgbox "Destination: " & conSQLDest.DataSource
		stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting
	else
		stpEnterLoop.DisableStep =True
		stpFinished.DisableStep = False
		stpFinished.ExecutionStatus = DTSStepExecStat_Waiting
	End if

	Main = DTSTaskExecResult_Success
End Function

Source: http://community.livejournal.com/sqlserver/55376.html