Solved

DTS functions

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

708 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

18 Experts available now in Live!

Get 1:1 Help Now