Hi:

I have a DTS package that first brings in data from another database
into SQL Server. In the source database, the database tables are like
this:

TableName20041014

for example. At any one time, 10 of these tables are in the source
database, every day the oldest one is deleted, i.e.:

TableName20041201
TableName20041202
....(7 more tables)...
TableName20041210

So I am using a scripting object in SQL Server to dynamically set the
table name. The code is at the end of the message (actually it gets the
same table name from two different sources you'll notice it points to 2
data pumps, that's not important though). This code works fine.

However, I originally have to set the data connection to some random
table, which will get replaced at runtime by this script. I'm wondering
if that is a problem when that 'dummy' table gets deleted from the
source database after 10 days or so. I know that if I opened up the
Data Pump task and made changes and resaved, it won't let me if the
dummy table no longer exists. When this is running in production, will
that be a problem? Or because the script object runs first it won't
matter, unless I open up the Data Pump task and make changes? If it is
a problem, what can I do? I have only read rights to the source data--I
cannot create a "placeholder" table with the same structure that never
gets deleted.

Thanks, let me know if you need more information,
Kayda


Visual Basic ActiveX Script
'************************************************* ***********************
' Pkg 213 (Changing Source Table)
Option Explicit

Function Main()
Dim oPkg, oDataPump, oDataPump2
Dim sSourceTable

' Derive the new table names
sSourceTable = "sourcedb.dbo.TableName" & GetDate(Now-5)


' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
Set oDataPump2 = oPkg.Tasks("DTSTask_DTSDataPumpTask_3").CustomTask

' Set the new values
oDataPump.SourceObjectName = sSourceTable
oDataPump2.SourceObjectName = sSourceTable

' Clean Up
Set oDataPump = Nothing
Set oDataPump2 = 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

As long as the ActiveX Script task runs before the Data Pump task you will
be fine. You can use Disconnected Edit (right-click in the DTS Designer
window) to edit the values for tasks, connection and steps in DTS. As this
is just plain text editing, it has the advantage (and disadvantage) that the
values you change won't be checked against the databases and servers your
DTS Package connects to. This allows you to make changes to non-existing
tables as you have described. Of course the risk is that if you make a typo,
it won't be caught until you run the package.

--
Jacco Schalkwijk
SQL Server MVP


<shop (AT) pacifictabla (DOT) com> wrote