regards Dynamic Properties Task in DTS
-
Dear all
i would like to know abt Dynamic Properties Task in DTS. iam not at all clear why and for what purpose this task is needed in DTS. i Also did not find good explanation in BOOKS ONLINE. so i request some one could give me a outline or links where i could find them
thanx to all
regards,
Rajiv.
-
I can only quote from my experience with dynamic properties tasks, but the idea behind it is the same for everyone.
The task is there to set values at runtime rather than than having them hard coded into the package.
Example. I wanted a package that I could run against multiple databases without having to edit the package each time. I was able to do this by changing the connection details each time I ran by specifying an ini file with the connection details inside it. I then only had to edit the text file each time I ran it.
This link is a good place to start:
http://databasejournal.com/features/mssql/article.php/3073161
Also http://www.sqldts.com will give further ideas and examples.
------------------------------
The Users are always right - when I'm not wrong! -
Rajiv,
For my part, I have an FTP process set up with DTS. I store values such as remote server name, user name, password, and file names in a SQL table. Then, at any point in the future if any of these values change, they only need to be changed in the table and read into the DTS package. You don't have to change the DTS package at all.
-
Thanks Jonathan and Edwin. I will try as u said and tell u wat happened. Thanks a lot
Regards,
Rajiv.
-
I found a nifty new use for the dynamic properties task. I use it to manage step precedence bases and values in a package that requires the dynamic skipping of certain steps.
[font="Courier New"]ZenDada[/font]
-
Is this instead of using workflow scripts? If so, that is a really neat idea. I've always hated how the workflow scripts are "hidden" away.
--------------------
Colt 45 - the original point and click interface
-
"I found a nifty new use for the dynamic properties task. I use it to manage step precedence bases and values in a package that requires the dynamic skipping of certain steps."
Could you please follow up with an example of how you are doing this, that sounds really neat!
-
Here is an example.
I have a package configuration table that stores a bunch of dates, flags and such that tell this particular package how to run, one of which is a flag to turn DTSStep_DTSExecutePackageTask_1 on and off. I read these values in an exec sql task and spit them out to globals via output parameters.
An ActiveX decides what to do with DTSStep_DTSExecutePackageTask_1 and sets globals for the dynamic properties task:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim oPkg, oStpRunReageDTS
Set oPkg = DTSGlobalVariables.Parent
Set oStpRunReageDTS = oPkg.Steps("DTSStep_DTSExecutePackageTask_1")
If DTSGlobalVariables("giRunReage").Value = 0 Then
'flag says don't run the reage package
'set prec basis to status and val to inactive
'use these in the dynamic properties task so downstream tasks run anyway
'see page 474-475 in Sams Microsoft SQL Server 2000 DTS
oStpRunReageDTS.DisableStep = True
DTSGlobalVariables("PrcBasis").Value = 0
DTSGlobalVariables("PrcVal").Value = 3
ElseIf DTSGlobalVariables("giRunReage").Value = 1 Then
'flag says do run the reage package
'set prec basis to result and val to success
'the downstream tasks will be dependent on success
oStpRunReageDTS.DisableStep = False
DTSGlobalVariables("PrcBasis").Value = 1
DTSGlobalVariables("PrcVal").Value = 0
Else
Main = DTSTaskExecResult_Failure
End If
Set oPkg = Nothing
Set oStpRunReageDTS = Nothing
Main = DTSTaskExecResult_Success
End Function
I am using the enumeration numbers of the constants for the globals.
On success this ActiveX goes to the Dynamic Properties Task (DPT) which uses the globals PrcBasis and PrcVal for all of the steps that have oStpRunReageDTS as a precedent. It's easy to find the steps and their precedence constraints in the DPT, and assign the globals to their basis and value properties. These downstream steps also have other steps as precedents and I am happily able to leave these alone.
On success of the DPT, flow passes to the Run Reage DTS step.
Now the package marches right past this step when the flag says it should.
I like this approach - the mixture of ActiveX and DPT - because it is easy to read, and easy and FAST to code. Granted this could all be done programmatically in the ActiveX like we did back in SQL Server 7, but this way is more flexible, easier to read and easier to manage.
[font="Courier New"]ZenDada[/font]
-
Setting connections dynamically in INI file
does not work smoothly in my case.
I have 10 Transformations and even if I change the value of DestinationDatabase in INI file from "MBVaR" to "MBVaR_NEW"
and execute Dynamic Properties Task
it still transfers the records into the old "MBVaR" database
because in Disconnect Edit if I go into each Transformation Task Properties
"DestinationObjectName" includes database name - [MBVaR].[dbo].[DAILY]
So the only solution for me is to manually go to each Task and change [MBVaR] to [MBVaR_NEW].
Not really dynamic but what can I do?
Robert
-
"Not really dynamic but what can I do?"
You could remove the database name from the object name...
--------------------
Colt 45 - the original point and click interface -
Hi all,
As rightly mentioned by Jonathan, the use of Dynamic properties task is to read the connection parameters from an .ini file rather having to hard code it in the Active X scripts of DTS packages for connection details like Servername, Database name, username, password, source path, destination path etc.
Since password is also specified in the file you may want to make the .ini file as hidden.
Thanks
Amol
- Hey Phil, I am stumped. I tried deleting this in the DTP, it comes back as the original Db. I tried Replace on the DestObjName to change it. Script didn't fail but Db name didn't change. Tried same with global and assigning it in the DTP. Same thing! I figured there would be a property in the DTP where I could set the Db name to the Initial Catalog of the Connection. No where to be found. How do change/remove it?
[font="Courier New"]ZenDada[/font]
-
Hi Phill,
You're right. My problem was that I was thinking
DTS will not work if I manually change ObjectNames.
Phill, can I ask another question?
I have this code in my SQL task:
--------------------------------
DECLARE
@INTERVAL_ID smallint,
@mbvarCOBDate smalldatetime
SELECT @mbvarCOBDate = ?
SELECT @INTERVAL_ID = INTERVAL_ID FROM INTERVAL
WHERE
(METHOD = 'STANDARD' AND EFFECTIVE <= @mbvarCOBDate AND EXPIRY >= @mbvarCOBDate)
---------------------------------
I setup InputGlobalVariable "gv_INTERVALID" for this task.
But when I execute it "gv_INTERVALID" does not change.
If I run it in QA it returns 41.
-
I think this should be giving you a syntax error. I don't think you can use a parameter in an assignment.
[font="Courier New"]ZenDada[/font]
-
I saved the package, closed DTS, opened it again and now it works.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply