Dynamic Properties DTS Task - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic Properties DTS Task

Author

Topic

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-08-07 : 13:37:56

I have a DTS Package with the following:

DYNAMIC Properties Task ----> Transformation Task

The Transformation Task is between 2 Tables on the same Server and database so the connection is identical.

I have set the connection properties in the DYNAMIC TASK TO READ FROM A GLOBAL VARIABLE and when I hardcode the GLOBAL VARIABLE I can move the package to a different server and if I modify the GLOBAL VARIABLE value it still works. I'm not trying to see If I can modify the DYNAMIC TASK TO USE A QUERY

SELECT @@SERVERNAME

HOWEVER It's not working
I have also trying to setup an execute task before the DYNAMIC PROPERTIES TASK to change the global variable.
Still no luck..

Any help would be appreciated......

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-07 : 13:49:44

This shows how to get a global variable in an activex script - just do it the other way rond to set it.
http://www.nigelrivett.net/SetDTSRunTimeValues.html

This execs an Sp and gets the result
http://www.nigelrivett.net/DTSExecuteStoredProcedure.html

SELECT @@SERVERNAME
isn't a useful query as the package will have to connect to the server to run so will already know the servername.

To run packages on different servers I have an SP to load them which sets the servername and maybe database name in the global variables (easy using dtsrun in v2000) then an activex script uses that to change the connections
http://www.nigelrivett.net/SetDTSRunTimeValues.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-08-07 : 15:16:14

Nr,

Modifying the globals manually is not a problem but I want the globals to be modified dynamically so that when a package is moved no extra work is to be done.

We are doing QA on hundreds of dts packages per week comming from all IT organizations and I'm trying to make the process more efficient.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-07 : 15:39:07

That's this bit.
To run packages on different servers I have an SP to load them which sets the servername and maybe database name in the global variables (easy using dtsrun in v2000) then an activex script uses that to change the connections
http://www.nigelrivett.net/SetDTSRunTimeValues.html

Are you sure that all these dts packages are necessary?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-08-07 : 15:56:11

NR, Global Variables work but need modification.
Using DTSRUN /A will also work but DTS package can not be run from Design view without modifying GV's.
Using INI FILE ON THE DIFFERENT PRODUCTION, QA, DEV Servers will also work but requires alot of work setting INI FILE PROPERTIES.

Anyone have a script to migrate packages and update properties? Otherwise I think will develop a VB Tool to Migrate DTS packages.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-07 : 16:19:18

>> Using DTSRUN /A will also work but DTS package can not be run from Design view without modifying GV's.
?????
Well yes - you have to set the global variable. When designing you only have to do it once and save it though.
Why create a VB app to migrate when you can do the same thing with an activex script in the package?

>> Using INI FILE ON THE DIFFERENT PRODUCTION, QA, DEV Servers will also work but requires alot of work setting INI FILE PROPERTIES.
Could do that and it isn't much work.
Better to have a table on the server which holds the values and the SP that calls the packages sets them before running. You can also use the same SP to log the call and monitor the duration and results. It can also be used to restrict the packages that run at the same time.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources