Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

DTS functions

Posted on 2004-08-17
5
Medium Priority
?
285 Views
Last Modified: 2013-11-30
I have written a function that checks to see if a directory exists, I am not sure whether it works or even if I did it correctly?

Option Explicit

Function directoryExist()

      dim objFSO

      ' create file sytem object
      set objFSO = createObject("Scripting.FileSystemObject")

      ' check if the directory exists
      if objFSO.FolderExists(dtsGlobalVariables("gDirectory").value) <>  "True" then
            directoryExist = CBool(True)
      else
            directoryExist = CBool(False)
      end if

      set objFSO = nothing

End Function

My aim is to have something I can reuse in several packages.

The question is:
- will the above work?
- what do I need to do, to be able to call it like directoryExist() ?
- can I pass the parameter gDirectory only as a global variable, normally you would have something like

Option Explicit

Function directoryExist( String sDirectory )

      dim objFSO

      ' create file sytem object
      set objFSO = createObject("Scripting.FileSystemObject")

      ' check if the directory exists
      if objFSO.FolderExists(*** sDirectory ***) <>  "True" then
            directoryExist = CBool(True)
      else
            directoryExist = CBool(False)
      end if

      set objFSO = nothing

End Function
0
Comment
Question by:Tacobell777
  • 2
5 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 11827663
In answer to some of your questions:

To see if it works properly, replace the DTS specific syntax with constants then copy the source code into a text file with a VBS extension and run it (double click it)

There are three ways I can think of to make a reusable function in DTS:

1. Put it into a stored procedure or function and call it from your DTS (it would be written in T-SQL).
2. Create your own DTS task DLL (then your functionality would appear in the DTS Task list and you could drop it on your page) but that is probably overkill.... mind you, you might be able to find one on the internet that does just that.
3. Create a single DTS packagfe with one step -a VBScript step- that takes its input from a global variable and passes its output to a global variable. Then you can call that package from any other package using a execute package step.


Number three is probably your best option. Let me know if you would like more detail.





0
 
LVL 17

Author Comment

by:Tacobell777
ID: 11847661
I'd appreciate some sample code or more info on step 3, I think thats the way I want to go.
In particular how to call the package from another package.

Cheers.
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 1000 total points
ID: 11857220
To call a package from another package, use the execute package task.

I've just had a look and it turns out its easy to pass variables into a sub package but not so easy to get the return value back out.

In answer to your original questions...

Will the above work?
Yes but the return value of the function is always used by DTS to determine wether the step succeeded or failed. If you want to use input and output values, use global variables.

How do I call it?
As soon as you have a step in your DTS package, DTS will run it according to the defined workflow. So if you have a VBScript task sitting on your page all alone, DTS will just run it automatically once and thats it. If you want to run your step you again have to set its status to 'waiting' (from some VBScript somewhere), then DTS will run it again. The thing is, it just runs it in parallel with all the other steps... the script that sets it to 'waiting' doesn't know when it starts or finishes.

Do I have to pass the parameter as a global variable?
Yes thats the only (easy) way to do it.


I recommend you read this link:

http://www.sqldts.com/default.aspx?211

About some ways to check for a file and use workflow



http://www.sqldts.com/default.aspx?215

About executing a package from a package.


There's also some info there about returning a value from a sub package.

SQLDTS is a great site.. gets straight to the point with lots of examples.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question