July 14, 2004 at 3:58 pm
Is there a way to use the Windows API to open the "File Open Dialog Box" from within an Active X Script in DTS? I would like to automate a task that dynamically assigns the datasource of an Access Connection Object based on user input. The end-user would be prompted to select a database from the "File Open Dialog" and then based on what was selected, the Connection object could be updated to point to the desired database.
Yes, Yes, I know -- why not simply go into the connection object and change it there from within the DTS? Simply put, lets just say that I like to do things the hard way. Essentially, I'm trying to create more of a User Interface from within DTS.
Yes, I know that DTS was not really design for this type of task, but with the Active X Script object, you are allowed to do so much more that just your basic ETL processing. The problem though might be that the variables required by the API will not be strong-typed and by default are defined as a "Variant".
Has anyone has any experience with trying to do something like this? I found the following article/DLL on SQLDTS.com, but my company will not allow me to load 3rd party add-ons to the server, so this is out of the question.
Here's the link though for those who are interested:
http://www.sqldts.com/default.aspx?226
Any help / thoughts would be greatly appreciated.
Thanks,
Brian
July 15, 2004 at 3:52 am
Have you thought about calling the dts package from vba and sending in the parameters you want to be dynamic.
You can give your users a locked down front end giving you better security as well.
See this link to give you an idea:
http://www.sqldts.com/default.aspx?208,2
July 15, 2004 at 3:52 am
Have you thought about calling the dts package from vba and sending in the parameters you want to be dynamic.
You can give your users a locked down front end giving you better security as well.
See this link to give you an idea:
http://www.sqldts.com/default.aspx?208,2
July 15, 2004 at 7:39 am
Although creating a front-end (in VB\VBA) to alter the information within the DTS is a viable option, I was hoping to keep everything in a DTS package. Any other suggestions?
Thanks!!!
July 15, 2004 at 8:17 am
Have you thought about using the read config task.
I created one to dynamically populate the destination file through activex.
You may be able to use global variables. See this link. It got me started:
http://www.databasejournal.com/features/mssql/article.php/3073161
July 15, 2004 at 9:03 am
Well, I really don't want to read the values from an INI file (or any other format) via the Dynamic Properties Task. What I am looking for is to reproduce the behavior of clicking on the "..." button which allows you to choose which file to use. This opens the "File Open" dialog box that allows the end-user to choose the appropriate file.
What I've used in the past in VB/VBA is the Windows API call:
Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean
to allow user input (note--I do not have the Developers version of MS-Office, otherwise I would have used the Common Dialog Control).
Can I get this same type of behavior from within running a DTS?
Thanks!!!
July 15, 2004 at 9:07 am
I get you now. I don't know of a way to reporoduce the behaviour. I very much doubt there is unless you specifically build something.
July 15, 2004 at 9:27 am
Regarding specifically building something, that looks like what the guys over at SQLDTS.com did and made it available to the masses. They also have an article which shows how to write your own custom tasks... http://www.sqldts.com/default.aspx?284
but I do not have access to Visual Basic to be able to create a library.
Like I stated in my original post, my hands are tied as far as loading 3rd party objects to the server AND the company won't give me access to a programming environment which allows you to create executables or libraries. I'm simply stuck with DTS and the available tasks from there in.
I might have to explore the option of creating something in VBA (like have a simple front-end application created in Access or Excel) and pass in information to the DTS like you stated earlier.
I kind of have the feeling that I won't be able to make any API Calls since the variables are defined as String or whatever, and in an ActiveX Script within DTS only allows for Variants.
Any comments / ideas / direction???
Thanks!!!
July 16, 2004 at 8:19 am
This isn't exactly the same as the file open dialog box but it might do the trick.
DIM FileName
FileName = InputBox("Enter file to open:", "File Open")
Darrell Parrish
La Crosse, WI
July 16, 2004 at 8:32 am
Yes, that would be an alternative approach, but I was hoping for more of a point and click solution. An input box would require the user to type out the entire path and filename (which in our case can be a pretty long string).
Thanks for the suggestion.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply