Solved

DTS functions

Posted on 2004-08-17
5
271 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 250 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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 setup several different housekeeping processes for a SQL Server.

932 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now