sql server (microsoft):
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
