Prompt user for Date w/ ActiveX script in DTS package

Question

Post reply

Prompt user for Date w/ ActiveX script in DTS package

Tammi L. Smith

Valued Member

Points: 57
More actions
October 4, 2005 at 11:05 am

#89564

Hello out there,
I am a Unix/Oracle/sqlplus person who has since changed jobs and am now working in a MSSQL Server 2000 environment. I am having a terrible time trying to figure out how to prompt a user for a begin and end date and then pass those two variables into a SQL script. I have created a DTS package in Enterprise Manager with a Query that pulls data from multiple tables and populates an Excel spreadsheet. It's currently grabbing all the data but I need to prompt the user for a begin and end date and only select a certain date range of rows. I am new to ActiveX/VBScript. But, it seems I have successfully created an input box with ActiveX script under Workflow Properties. When I execute the DTS package, the input box pops up and asks for the date. I added a global variable for the date. But, when I try to change the query to include the parameter by using a ?, I get an error. Says it doesn't recognize the field "updatedate" in the table. The field updatedate does exist in the table tbl_cs_sv_service. I'm not passing it in correctly from the input window but not sure what I'm missing. Can anyone help guide me in the right direction?
Here's the query for what it's worth:
select '81' 'Provider #',
       case when a.servprovidercode > 0 then (select f.firstname
                                               from tbl_pn_employee f where substring(e.servproviderid,2,5) = f.employeeid)
                                        else 'Unspecified' end 'Serv Provider First Name',
       case when a.servprovidercode > 0 then (select f.lastname
                                              from tbl_pn_employee f where substring(e.servproviderid,2,5) = f.employeeid)
                                        else 'Unspecified' end 'Serv Provider Last Name',
       b.CaseNumber 'Client ID',
       c.PaNumber 'Authorization',
       convert(char(10),a.ServiceDateFrom, 101) 'Date of Service',
       case d.Ycode when null then d.CPTCode else d.Ycode end 'Proc Code',
       d.Modifier,
       cast (a.Units as dec(4,2)) Units,
       cast (a.UnitServCost as dec(5,2)) 'Rate'
from tbl_cs_sv_service a, tbl_cs_consumer b, tbl_cm_ur c, tbl_cs_sv_servicecharge d, tbl_cs_fd_servprovidercode e
where a.consumerid = b.consumerid
and   a.consumerid = c.consumerid
and   a.servicecode = c.servicecode
and   a.servicecode = d.servicecode
and   a.servprovidercode *= e.servprovidercode
and   a.servicedatefrom between c.authorizationbegin and c.authorizationend
and   a.servicedatefrom between d.effectivedate and d.expiredate
and   a.claimed != 1
and   a.programcode != 0
and   a.locationcode != 0
and   b.divisioncode = 1
and   a.updatedate = ?    *This is where I want to grab what user entered. For now I'm just using = ? for testing and just prompting for one date. After I get it working with one date I'll prompt for two dates and use where a.updatedate between ? and ?.
order by 'Serv Provider First Name', 'Serv Provider Last Name', b.lastname, b.firstname, a.servicedatefrom
Here's the ActiveX script
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Dim begdate
begdate = inputbox ("Enter From Date as YYYY-MM-DD")
Function Main()
Main = DTSStepScriptResult_ExecuteTask
End Function
Thanks in advance for any guidance.
Tammi

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

There is a work-around for mapping global variables that is at http://www.sqldts.com/default.aspx?234.

Basically, the steps are:

1. Have a dummy sql statement that is on one line that has the desired variables. in your case:

select * from tbl_cs_sv_service where updatedate between ? and ?

2. Map the ? place holders to the Global Variables.

3. Replace the dummy SQL with the real SQL.

SQL = Scarcely Qualifies as a Language

Tammi L. Smith Valued Member Points: 57 More actions · Answer 2

Hi Carl,

Thanks for the tip - by doing what you said I was able to map the ? place holders to the Global Variables under Transform Data Task Properties. I then remove the dummy select line and insert the real line that I want to use with the query....which is and a.updatedate = ?. When I click OK to save I still get the following message.

Package Error

Error Source: Microsoft OLE DB Provider for SQL Server

Error Descr: The column prefix 'a' does not match with a table name or alias name used in the query

Guessing that it doesn't like me using alias names ????. This same query works just fine in DTS when I'm not trying to use an ActiveX popup box w/ global variables and parameters. Any other thoughts?

Thanks much........Tammi