Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
Changing the DataPump Source and Destination Tables
By Darren Green
Version 7.0/2000
Level Intermediate

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.