Re: Setting Table dynamically
From: Jacco Schalkwijk (jacco.please.reply_at_to.newsgroups.mvps.org.invalid)
Date: 12/20/04
- Next message: Hugo Venancio: "Deadlock on a single "programming pattern"..."
- Previous message: Paul Durrant: "Re: 64bit insert delays"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 20 Dec 2004 16:28:33 -0000
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@pacifictabla.com> wrote in message
news:1103555711.552887.100790@z14g2000cwz.googlegroups.com...
> 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
>
- Next message: Hugo Venancio: "Deadlock on a single "programming pattern"..."
- Previous message: Paul Durrant: "Re: 64bit insert delays"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
