Solved

DTS functions

Posted on 2004-08-17
5
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

730 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