You can use an ActiveX Script to change both the source and destination
of the DataPump task.
The first thing you need is the Name of the task. Right-click the
DataPump task and select Workflow Properties. On the options tab it
shows you the step name. The task name is almost guaranteed to be the
same as the step name if you just change "step" to "task". (For SQL 2000
just use disconnected edit)
Now you can use this in the script below:
' Pkg 213 (Changing Source & Destination Tables)
Option Explicit
Function Main()
Dim oPkg, oDataPump
Dim sSourceTable, sDestinationTable
' Derive the new table names
sSourceTable = "DateTable_" & GetDate(Now)
sDestinationTable = sSourceTable
' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
' Set the new values
oDataPump.SourceObjectName = sSourceTable
oDataPump.DestinationObjectName = sDestinationTable
' Clean Up
Set oDataPump = Nothing
Set oPkg = Nothing
Main = DTSTaskExecResult_Success
End Function
Function GetDate(dDate)
Dim sYear, sMonth, sDay
sYear = Year(dDate)
sMonth = Month(dDate)
If sMonth < 10 Then sMonth = "0" & sMonth
sDay = Day(dDate)
If sDay < 10 Then sDay = "0" & sDay
GetDate = sYear & sMonth & sDay
End Function
By default the designer will set the source and destination object names
using the full three part naming convention
([datebase].[owner].[object]), but this is not strictly required. You
could if you wanted read the existing value and just change the table
part.
The structure of the source and destination can obviously be different
but the old source table must be the same structure as the new one, and
the same applies for the destination table.