Hello, I am on SQL Server 2000/sp3a.

Not , a very elegant way to do, but, I have to develop a mechanism to get input data from user and then generate an export file based on that criteria (in WHERE clause). Ultimately, the package will be called externally from a program but for now, I need to provide some user interface. I selected to declare three global variables on package properties.

Intended variable declarations:
gV_Pub number "0"
gV_IssDate Date "2003/10/01"
gv_Path String "C:\"

Now, when I have created an ActiveX Task for taking user input and the intention was to feed those values to the global variables. However, when the package is executed and values are provided to the three InputBox dialog boxes, it resulted in error (casting problems). And the global variables after package execution are now declared as:

gV_Pub String "1"
gV_IssDate String "1"
gv_Path String "1"

Definitely, these values are wrong. The query generates correct export file from values defined earlier. I think there is some problem with global variable declaration and value assignment. I have tried to use InputBox$ and it seems it is not allowed in VBScript. What could be the problem and how can I develop an intermediate solution?

Just FYI, ActiveX task executes first and then the export process (transform data task) runs.

Code in ActiveX task:
==============
Dim pubNumber, issDate, path

'Entering information in Dialog box

pubNumber = InputBox("Please Enter the Publication Number from listing below:", "Enter the Publication Number", "1")
DTSGlobalVariables("gV_Pub").Value = pubNumber

issDate = InputBox("Enter the Publication Date in yyyy/mm/dd format", "Publication Issue Date", "2003/10/01")
DTSGlobalVariables("gV_IssueDate").Value = pubNumber

path = InputBox("Enter the directory information where export file will be saved", "Path", "C:\")
DTSGlobalVariables("gV_Path").Value = pubNumber


Please help.

Thanks,
MZeeshan

OK

In my opinion DTS is not something I would use an interactive client. That
said it most certainly can be.

You are right in your input boxes
Casting values. Of what datatypes are the GVs declared. I would have them
all as strings
This works

Function Main()


DTSGlobalVariables("gv_1").Value = inputbox("Enter val for #1")
DTSGlobalVariables("gv_2").Value = inputbox("Enter val for #2")
DTSGlobalVariables("gv_3").Value = inputbox("Enter val for #3")


msgbox DTSGlobalVariables("gv_1").Value & " " &
DTSGlobalVariables("gv_2").Value & " " &
DTSGlobalVariables("gv_3").Value


Main = DTSTaskExecResult_Success
End Function


You need to validate the directory the user specifies. Something like (
taken from http://www.sqldts.com/default.aspx?246)


if fso.FolderExists(DTSGlobalVariables("gv_FileLocati on").Value) <> "True"
then
DTSGlobalVariables("gv_FileCheckErrors").Value =
CSTR(DTSGlobalVariables("gv_FileCheckErrors").Valu e) &_
" " & "Source File directory Not Found"
end if

You need to check the issue date is in the correct format as well.








--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"MZeeshan" <mzeeshan (AT) yahoo (DOT) com> wrote

(in WHERE clause). Ultimately, the package will be called externally from a
program but for now, I need to provide some user interface. I selected to
declare three global variables on package properties.
the package is executed and values are provided to the three InputBox dialog
boxes, it resulted in error (casting problems). And the global variables
after package execution are now declared as:
variable declaration and value assignment. I have tried to use InputBox$ and
it seems it is not allowed in VBScript. What could be the problem and how
can I develop an intermediate solution?

Why don't you save the dts code in a vb application. modify the application
so you can fully customize your dts code and make it dynamic.


--
Regards,

Dandy Weyn
MCSE, MCSA, MCDBA, MCT

www.dandyman.net
"MZeeshan" <mzeeshan (AT) yahoo (DOT) com> wrote

(in WHERE clause). Ultimately, the package will be called externally from a
program but for now, I need to provide some user interface. I selected to
declare three global variables on package properties.
the package is executed and values are provided to the three InputBox dialog
boxes, it resulted in error (casting problems). And the global variables
after package execution are now declared as:
variable declaration and value assignment. I have tried to use InputBox$ and
it seems it is not allowed in VBScript. What could be the problem and how
can I develop an intermediate solution?

First of all, thank you very much for providing useful information. Kinda learning experience for me.

What worked out for me was to define an ActiveX Script Task before the export process. Initially, I was trying to define the ActiveX Script transformation as part of the Transform Data Task. Eventually, the dialog boxes will go away when the DTS package will be called from the primary application (developed in VB). Till then, this will provide useful service.

Regards,
MZeeshan