I have a package in which I have several Data Pump tasks to copy data from a
tables in a Pervasive Database to an SQL Database.

I use a dynamic propertice task to change the destination connection (server
name and database name). Dynamicall changing the connection works fine for
other tasks eg Execute Sql Tasks.

However because the Destination Tables are named in the DataPump using the
fillowing convention by default [DatabaseName].dbo.[TableName], I am getting
errors because the database name in the table definition is different to the
new dynamic database name in the destination connection. How can I overcome
this problem. Can the destination Tables be defined by their table names
only. If so how do I do this using enterprise manager.


Paul

Paul:

You can try to set it up as you specify (using just the table name...I'd
suggest removing just the database name and leaving the schema name "dbo").
To do this, you will have to use the "Disconnected Edit..." when you right
click in the design window for the package. Expand Tasks in the tree and
look at the DestinationObjectName property of the data pump(s) in question.
Edit it as you specify and try it. If that doesn't work, you will have to
modify it using an ActiveX Script, where you can build the name and set the
same property during execution.

Scott

"Paul Say" <saywin (AT) tpg (DOT) com.au> wrote

BTW, if you do the disconnected edit option, it will work, but MAKE SURE YOU
DON'T MAKE ANY CHANGES TO THE DATAPUMPS OR THE WIZARD WILL ADD THE DATABASE
NAME BACK and you will have to remove it again. It is a gotcha and a
maintainance issue that will eventually bite you or someone else. I'd
suggest writting an ActiveX script to run first in the package that will
find all DataPump objects and remove the database name from the
DestinationObjectName. Below is an example I found, haven't tested it
though. In looking at it, I think I would use something like the Split
function with "." being the separator and then test for three items in the
list and piece back together the last two. That way you don't have problems
with spaces by using the below code.

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Option Explicit


Function Main()
Dim Package, Step, Task, S, I


Set Package = DTSGlobalVariables.Parent
For Each Step In Package.Steps
Set Task = Package.Tasks(Step.TaskName)
If Task.CustomTaskID = "DTSDataPumpTask" Then
S = Task.CustomTask.Properties("DestinationObjectName" )
I = InStrRev(S, "[")
If I Then
Task.CustomTask.Properties("DestinationObjectName" ).Value _
= Mid(S, I + 1, Len(S) - I - 1)
End If
End If
Next


Main = DTSTaskExecResult_Success
End Function


"Wm. Scott Miller" <Scott.Miller (AT) spamkillerwvinsurance (DOT) gov> wrote