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.
| Author |
Topic |
|
maloy
Starting Member
19 Posts |
 Posted - 2002-02-21 : 11:24:04
|
| Hi all - need ur help!My client has suddenly had a change of mind: will have nothing to do with DTS! Probably his DBA had some problems with it! Whatever it is, I have several DTS packages now all of which use dynamic properties tasks for initialization of global variables including server name, db catalog, userid and password.These also have an Exec SQL Task each which calls some procedures and one ActiveX script task at the end for checking the status of the procedure execution and writing a report to be viewed by the DBA. Now I need to use T-SQL stored procs to do all the stuff. Calling the procedures is ok, but what is worrying is how to simulate the dynamic properties? Can xp_cmdshell be used to any of such stuff...also the ActiveX script part?Any and all help would be appreciated!Thanks much!Maloy |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-02-21 : 11:44:35
|
| You can pass params into you stored procs to subtitute for the dynamic properties task. As far as the ActiveX Task, I'm not sure how you would do that besides making a DLL / EXE that you run fro inside your Stored Proc.I highly suggest keeping the DTSpackage. I've had NO problems with the ones I've made. They are very fast, and fairly easy to maintain. I think that stored proc you are about to write will be a BEAR to maintain.Michael |
 |
|
|
khenry
Starting Member
16 Posts |
Posted - 2002-02-21 : 12:23:19
|
| Keep your Exec SQL tasks and just pass their results to variables. You can use these variables inplace of the DTS Globals.Except where the Global is an object name that is, in those cases you will need to build a @sql string variable an either exec or sp_executesql it.K e i t h H e n r y |
|
|
|
maloy
Starting Member
19 Posts |
Posted - 2002-02-22 : 07:25:14
|
| Can u explain a bit about running the dll/exe from within the stored procedure?ThanksMaloy |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-22 : 07:32:24
|
| You can use the DTSRUN command-line utility to run DTS packages, and can call this from within a SQL stored procedure using the xp_cmdshell utility (here's an abbreviated version):EXECUTE master..xp_cmdshell 'dtsrun /Sserver /Uusername /Ppassword /Npackagename'There is a switch (/A) for setting global variable values; you need to include the variable name, data type and the value immediately after the /A. Use multiple /A flags for each variable.Books Online has more information under "dtsrun" and "xp_cmdshell", and this site is pretty good on advanced DTS stuff:www.sqldts.comEdited by - robvolk on 02/22/2002 07:33:28 |
|
|
|
maloy
Starting Member
19 Posts |
Posted - 2002-02-22 : 10:39:18
|
| Thanks! I'll check this out.But does this mean I've to use the sp_OA... procedures to create the packages, or it is enough to use the dtsrun command. Also can this be scheduled?Maloy |
|
|
|
|
|
|
|
|
T-SQL instead of DTS? - dynamic properties task