I've read some docs and posts about how to set the SourceSQLStatement in an
ActiveX script, but I'm dense and can't get it to work. When I read the
package's generated VB script it looks like the definition of the
transformation (Sub Task_Sub2) is running after my ActiveX script (which is
generated as Sub Task_Sub1), so its resetting the SQL back to what's defined
in the transformation.

My code is simply

Function Main()
Dim oPkg, strSQL, oTask, iresult
set oPkg = DTSGlobalVariables.Parent
strSQL = "SELECT gridbits_view.* FROM gridbits_view WHERE store = 111"

set oPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
oPump.SourceSQLStatement = strSQL
iresult = MsgBox(oPump.SourceSQLStatement)
Main = DTSTaskExecResult_Success
End Function

The UI doesn't allow establishing precedence between a script and a
transformation. What do I need to do to change the order?

thanks.

Never mind, I figured out how to add the script to the workflow property.

You can use workflow

Introduction to Workflow
(http://www.sqldts.com/default.aspx?287)

Global Variables and SQL statements in DTS
(http://www.sqldts.com/default.aspx?205)

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Tom Spitzer/EC Wise" <tspitzer (AT) ecwise_Stripme (DOT) com> wrote