June 29, 2005 at 8:37 am
Does anyone know if Windows API declarations and calls can be made in the DTS ActiveX scripting facility in SQL Server.
June 29, 2005 at 10:34 am
Not to my knowledge. Interested to know why you would want to.
June 29, 2005 at 12:40 pm
You'll wish you hadn't asked 😉
I'm creating a DTS job that will extract some data to a CSV file, Zip the file up to a WinRAR file (or should that be RAR a file up;-)) and then Email it.
The only way I can see of creating the Rar file from the DTS package is to call the ShellExecute method on an object instance of the Shell.Application class in Windows. However the tricky thing is that this would just fire off the "rar -a fiiletocompact.csv result.rar" command asynchronously and not wait for the Rar to finish - as is the nature of shellexecute. So I have some VB code that I used to have that is effectively a ShellandWait, but it requires a few WinAPI calls.
The lowdown is that I'm after a method of waiting of creating a Rar file and waiting for that to finish before I go to the next step.
Current solution looks like creating a VB COM server that implements the required ShellandWait API functionality, which I can then instantiate from my ActiveX DTS step.
June 30, 2005 at 2:33 am
You were right, I wish i hadn't!
There is a WAITFOR command in sql server that allows you to delay processing, but this has to be set at the beginning. if you have a small delay in between creating the rar file and the next step, this should be OK for you.
A more controlled method is to use filexists from the filesystemobjects in an activex vb script that loops around checking for existence of the rar file before continuing. Always include a timeout limit in this scenario or you will end up with the server locking up if the file is never found.
June 30, 2005 at 5:44 am
Not sure if this will help, but can you instanciate and use the WshShell.Run method and use the last param to run the process syncronously? I think this would get rid of your timing problems as the RAR is created.
-Mike Gercevich
June 30, 2005 at 6:12 am
Why couldn't you put the RAR command in an Execute Process task? The step would not be complete until RAR finished.
June 30, 2005 at 8:19 am
The problem with calling Win APIs from within an ActiveX Script is that it only allows for variables to be dimmed as variant and APIs require strict declarations.
July 1, 2005 at 3:50 am
Ahh, well done Scott, just what the doctor ordered. I'd not come across the execute shell task before
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply