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
Solved

DTS functions

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Join with a SQL Server STUFF 5 33
SQL query and VBA 5 45
SQL Quer 4 21
question about results where i dont have a match 3 20
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

808 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