Hi,
you can run an Active X script prior to the execution of the data pump to change the table name.
Example:
'**********************************************************************
' Visual Basic ActiveX Script
'
' Replace the Placeholder ##Supplier## in the "WHERE" clause in the data pump task
'************************************************************************
Function Main()
' Get the root parent reference to the DTS package
Set oPkg = DTSGlobalVariables.Parent
' Get reference to data pump task1
Set oExecSQL =oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
'Retrieve current SQL statement (with placeholders ##Supplier##) and save to lokal Working Variable
mySQLStatement = oExecSQL.SourceSQLStatement
' Copy the local working variable to Global for later restore use
DTSGlobalVariables("original_my_SQL_DPT_1").Value= mySQLStatement
' Start the find and replace operations to substitue the "placeholder" in the SQL statement
mySQLStatement = Replace( mySQLStatement, "'##Supplier##" , DTSGlobalVariables("Supplier_Number").Value & ")" , 1 , -1, 1 )
' Assign SQL Statement to Exec SQL Task
oExecSQL.SourceSQLStatement = mySQLStatement
' Clean Up
Set oPkg = Nothing
Set oExecSQL = Nothing
Set mySQLStatement = Nothing
Main = DTSTaskExecResult_Success
End Function
The global variable "Supplier_Number" has to be created e.g. when you read from the log table.
You also can use the stored procedure sp_executesql to modify an SQL statement prior to its execution.
Hope this helps.
Matthias