December 13, 2005 at 4:12 am
Hello all,
Dummy question I'm sure but would someone mind explaining how DTSDestination() and DTSSource() know which connection to reference? In all of the Google stuff I've trawled the code runs as follows
Function Main()
DTSDestination("DeptName") = DTSSource("DeptName")
DTSDestination("Sales") = DTSSource("Sales")
DTSDestination("Sale_Date") = DTSSource("Sale_Date")
......
End Function
Do i need to set a connection object and ref it?
TIA
Dan
December 13, 2005 at 4:34 am
The point here is that the code above is ONLY applicable to an activex script attached to an actual transformation task. As duplicate source and destination names are not allowed, the code knows to use the transformation in scope at that time.
If you put the function in a step outside of a transformation then you canot reference DTSSource and DTSDestination as it is outside the scope. You can get to object like global variables as they are distinct also.
December 13, 2005 at 5:19 am
Thanks. I've been landed with being the DTS guy and so am a bit clueless (my thoughts on the architecture don't seem to matter!!)
Do you mean that an ActiveX jobbie has to be registered as a lookup on the Connection?
All help appreciated.
D.
December 13, 2005 at 7:10 am
DTSSource and DTSDestination are available within a Transform Data task.
When you create a Transform Data Task, you must specify a source connection and a destination connection. It is from these connections that the fields are drawn.
The ActiveX scripts that you have seen are probably transformations within the Transform Data task - so the source and destination are already set by the task itself.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 13, 2005 at 7:16 am
Basically there are two ocassions where you can run an active x script.
1. As a job step - Select ActiveX Script Task.
This runs a script that is not directly related to a transformation. Eg. You can set a value for a global variable here or a path for a text file connection.
2. as part of a trnasformation - Double click on a trnasformation task, select new and then select activex. This is where you can set dtssource and detination values. E.g. You can join multiple source values into one destination value or enter any value you want to go into the destination.
(This example relates to the code you posted here. It automatically knows to look at the transformation in that scope.)
Hope it helps you out. For more info, visit http://www.sqldts.com - A lot of good examples explained fully.
December 14, 2005 at 6:54 am
If you are just doing staight imports of data without and sort of transformation or applying business rules to the data you are transfering, there is no need to use an ActiveX Script in the transformation task. It adds uneeded overhead and is much slower, in my experience, than using a simple "Copy Column" method.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply