Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  A better solution?

Author  Topic  krsk Starting Member 21 Posts Posted - 2003-03-12 : 07:44:49 Hi :-) I wish to get a specific variable (a date so I can check if the data is ready for load) from a DB2 table. The DB2 tablename changes so I need it to be dynamic. I've solved it like this, through a ActiveX script. My question is: Is there a better way to do this in DTS..simpler, faster etc. Function Main() ' Declare local variables Dim thisPackage Dim sqlStep Dim sqlTask Dim sqlTaskCust Dim srcTable Dim SQLStatement Dim connID ' Set existing connectionID (can be found in the Disconnected Edit property) connID = 5 ' Define dynamic SQL statement srcTable = DTSGlobalVariables("srcTable").Value SQLStatement = "SELECT PERIODE FROM " & srcTable & " Fetch first 1 rows only" ' Set pointer to Package object Set thisPackage = DTSGlobalVariables.Parent ' Make sure that the new steps and tasks can be created (not exists already) For Each sqlTask in thisPackage.Tasks IF sqlTask.Name = "DTSTask_CheckBALIDB2data" THEN thisPackage.Tasks.Remove ("DTSTask_CheckBALIDB2data") END IF Next For Each sqlStep in thisPackage.Steps IF sqlStep.Name = "DTSStep_CheckBALIDB2data" THEN thisPackage.Steps.Remove ("DTSStep_CheckBALIDB2data") END IF Next ' Create new step to hold Task Set sqlStep = thisPackage.Steps.New ' Give the step a name sqlStep.Name = "DTSStep_CheckBALIDB2data" ' Create new Task Set sqlTask = thisPackage.Tasks.New("DTSExecuteSQLTask") ' Give the task a name sqlTask.Name = "DTSTask_CheckBALIDB2data" ' Get the Customobject in Task (see object model for details) Set sqlTaskCust = sqlTask.CustomTask ' Tell the Task what connection is should use sqlTaskCust.ConnectionID = connID ' Tell the Task what SQL statement it should run sqlTaskCust.SQLStatement = SQLStatement ' Tell the task in which globalvariable it should store the SQL output sqlTaskCust.OutputGlobalVariableNames = ("srcTablePeriode") ' Connect Step and Task sqlStep.TaskName = sqlTaskCust.Name ' Add the step and task to the package thisPackage.Steps.Add sqlStep thisPackage.Tasks.Add sqlTask ' Execute the step thisPackage.Steps("DTSStep_CheckBALIDB2data").Execute ' Remove the step and task from the package so it is ready to run again thisPackage.Tasks.Remove ("DTSTask_CheckBALIDB2data") thisPackage.Steps.Remove ("DTSStep_CheckBALIDB2data") ' Clean up Set thisPackage = Nothing Set sqlStep = Nothing Set sqlTask = Nothing Set sqlTaskCust = Nothing Set srcTable = Nothing Set SQLStatement = Nothing Set connID = Nothing Main = DTSTaskExecResult_Success End Function /Kristian :-)