Hi there People,

I was looking at a really good thread earlier which was dealing with
the details of porting DTS packages from Dev to Prod and renaming
connections programatically.

This changed the name of the server in the dest connections. I have
used this process and I think it works really elegantly, however it
does not reflect in the transformations. The dest table is left with
the naming of [dev_server].[dbo].[dest_table] rather than picking up
the new connection name of [prod_server].[dbo].[dest_table].

I tried to look through the task object but could not for the life of
me work out how to read that value, and change it.

Am I going about this all wrong or is there something I have missed?

Any help would be greatly appreciated - I am really not looking
forward to going through and remapping 500+ transformations

Thanks in advance
JPO

The property you are looking for is DestinationObjectName

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"JPO" <justin.parry-okeden (AT) ge (DOT) com> wrote

Thanks Allan, I had actually looked at the DestinationObjectName,
which is a property of the CustomTask Object.

All the articles and examples I find are about creating a CutomTask
object and setting the DestinationObjectName before adding the task as
following:-

Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
Set oCustomTask2 = oTask.CustomTask

oCustomTask2.Name = "DTSTask_DTSDataPumpTask_1"
oCustomTask2.DestinationObjectName = "[pubs].[dbo].[authors1]"

goPackage.Tasks.Add oTask

What I can't work out is how to reference this from within an ActiveX
script to change the property. I can obtain a reference to the Task
Object from the Tasks collection, but from there I cannot obtain a
reference to the oCustomTask2 object or the DestinationObjectName
property.

Any help would be very much appreciated.

Thanks in advance
JPO


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

This is how I would do it

Function Main()

dim PKG
dim TSK

SET PKG = DTSGlobalVariables.Parent
SET TSK = PKG.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

msgbox TSK.DestinationObjectName


Main = DTSTaskExecResult_Success
End Function

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"JPO" <justin.parry-okeden (AT) ge (DOT) com> wrote

Hi there People,

I worked out what the issue was, even though I was talling the
Task.CustomTask object it was still returning objects of the type
ActiveX script Tasks.

A simple work around was to just test in a loop to see if when a
property name of "DestinationObjectName" turned up and then replaced
that with my destination table.

Following below is my ActiveX script:-

thanks again Allan for your help :-)



Function Main()


' Declare Variables
Dim oPKG
Dim oConnection
Dim oPump
Dim oTask
Dim sErrLogName
dim sMonth
dim sDay
dim sExistingDB
dim sNewDB

sExistingDB = "[JPO_TEST]"
sNewDB = "[JPO_QATS_TEST]"


' Get Package Object
Set oPKG = DTSGlobalVariables.Parent

sErrLogName = "d:\" + oPKG.Name + "_" + cstr(Year(date)) + right("0" +
cstr(month(date)), 2) + right("0" + cstr(day(date)), 2) + ".log"
oPKG.LogFileName = sErrLogName

'msgbox oPKG.LogFileName

' Examine Connections
'For Each oConnection in oPKG.Connections
' 'Test for SQL Server Connection
' If oConnection.ProviderID = "SQLOLEDB" Then
' msgbox "Server:= " + oConnection.DataSource + ", DB:= " +
oConnection.Catalog
' Set new Server (DataSource) value
' oConnection.DataSource = "MyServer"
' Set new Database (Catalog) value
' oConnection.Catalog = "JPO_QATS_TEST"
' End If
'Next

'for each oTask in oPKG.Tasks
' set oPump = oTask.CustomTask
'
' for n = 1 to oPump.Properties.count
' if oPump.Properties(n).Name = "DestinationObjectName" then
' 'sout = Replace(oPump.Properties(n).Name, sExistingDB ,
sNewDB,1,1,1)
' oPump.Properties(n).Value = Replace(oPump.Properties(n).Value,
sExistingDB , sNewDB, 1, 1, 1)
'
' 'msgbox "Name: " + cstr(n) + " " + oPump.Properties(n).Name + ",
Value: " + cstr(oPump.Properties(n))
' exit for
' end if
' next
'
'next

' Clear Up
Set oConnection = Nothing
Set oPKG = Nothing
Set oPump = Nothing
set oTask = Nothing

Main = DTSTaskExecResult_Success

End Function