Trying to modify a Dynamic Properties Task

[Steve Steele]

I'm attempting to modify the SourceQuerySQL property of a Dynamic Properties task
in an ActiveX script. I've done similar modifications to other types of tasks,
but for some reason can't get it to work in a Dyn Prop task. I guess I'm having a
little trouble figuring out the object model for this task. Anyone have a sample
of how to modify this property?

Thanks,
Steve

[Darren Green]

In article <043301c0ce58$6939bf50$a5e62ecf@tkmsftngxa07>, Steve Steele
<sst...@enermetrix.com> writes

For some reason it is not covered very well in Disco Edit, but using the
Save As VB feature all is revealed.

Public Sub Task_Sub1(ByVal goPackage As Object)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask1 As DTSCustTasks.DynamicPropertiesTask
Set oTask = goPackage.Tasks.New("DTSDynamicPropertiesTask")
Set oCustomTask1 = oTask.CustomTask

oCustomTask1.Name = "DTSTask_DTSDynamicPropertiesTask_1"
oCustomTask1.Description = "Dynamic Properties Task: undefined"

Dim oAssignment As DTSCustTasks.DynamicPropertiesTaskAssignment
'------- An Assignment is defined here

Set oAssignment = oCustomTask1.Assignments.New
oAssignment.SourceType = 1
oAssignment.SourceQueryConnectionID = 1
oAssignment.SourceQuerySQL = "SELECT 'FredValue' AS Ex1"
oAssignment.DestinationPropertyID = "'Global Variables';
'Fred';'Properties';'Value'"

oCustomTask1.Assignments.Add oAssignment
Set oAssignment = Nothing

goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing

End Sub

You'll have to use something like an ActiveX Script Task to access this
from within a package as the Dynamic Properties Task does not expose
itself to another Dynamic Properties Task.

That's justice for you, but to the good old days of ActX script for
dynamic property work, none of these new task to do it for you <vbg>
--
Darren Green
SQL/DTS - http://www.swynk.com/friends/green/

[Steven Steele]

Darren,

First of all, thanks for taking the time to reply to my "cry for help." Your
suggestion of trying to identify the Dynamic Property Task objects by saving it
as a VB script is something I've already tried. And it does work in an ActX
script. My problem is that VB script defines everything as an ADD and I need to
modify an existing one. For some reason, I can't get the correct syntax for
modifying the Assignments property. For a SQL task, I've used this:

Set oCustomTask = oTask
("DTSTask_DTSDataDrivenQueryTask_1").CustomTask.Properties.Item
("SourceSQLStatement")
oCustomTask.Value = "SELECT blah blah blah" & vbCrLf
etc.

I just can't figure out what object.value I need to use to insert SQL into a Dyn
Prop task. Any other suggestions?

Thanks for you help,
Steve

End Sub

.

[Darren Green]

In article <126eb01c0cf3f$92b4b6e0$19ef2ecf@tkmsftngxa01>, Steven Steele
<sst...@enermetrix.com> writes

>Darren,
>
>First of all, thanks for taking the time to reply to my "cry for help." Your
>suggestion of trying to identify the Dynamic Property Task objects by saving it
>as a VB script is something I've already tried. And it does work in an ActX
>script. My problem is that VB script defines everything as an ADD and I need to
>modify an existing one. For some reason, I can't get the correct syntax for
>modifying the Assignments property. For a SQL task, I've used this:
>
> Set oCustomTask = oTask
>("DTSTask_DTSDataDrivenQueryTask_1").CustomTask.Properties.Item
>("SourceSQLStatement")
> oCustomTask.Value = "SELECT blah blah blah" & vbCrLf
> etc.
>
>I just can't figure out what object.value I need to use to insert SQL into a Dyn
>Prop task. Any other suggestions?
>
>Thanks for you help,
>Steve
>

You need to reference the correct assignment object (DynamicPropertiesTa
skAssignment) in the assignments collection (DynamicPropertiesTaskAssign
ments).

Each assignment has DestinationPropertyID property which tells us
something about what it is aimed at updating.

SourceQuerySQL is the property we want.

This will check all assignments for one that assigns the global
variables "MsgBoxString". This is just to make sure we amend the correct
assignment in case of multiple.

We then check that it is a Query type.

We then set the new SQL Statement, in this case to "SELECT 'Fred
Value'". Something quick and easy for me to test when I assigned it to a
global variable.

Watch for line wrap.

Function Main()

Dim oCustomTask, oAssignment
Set oCustomTask = DTSGlobalVariables.Parent.Tasks("DTSTask_DTSDy
namicPropertiesTask_1").CustomTask

' Find the Assignment that changes the global variable "Fred"
' Change the SourceSQLQuery property
For Each oAssignment In oCustomTask.Assignments
If oAssignment.DestinationPropertyID = "'Global
Variables';'MsgBoxString';'Properties';'Value'" Then
' Check this is a query to be extra sure
' DTSDynamicPropertiesSourceType_Query
If oAssignment.SourceType = 1 Then
oAssignment.SourceQuerySQL = "SELECT
'Fred Value'"
End If
End If
Next

Set oAssignment = Nothing
Set oCustomTask = Nothing
Main = DTSTaskExecResult_Success
End Function

Regards