Re: How to execute a table lookup in a script component?

OK so this code should be in the pipeline. You should be passing the variable to an OLE DB Source Adapter and using it in there.

Looping through the rows one * one will cost you too much

--


Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com




"Phill" <Phill@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:96E0DACA-B25A-41D0-81A2-3BF07AAD040C@xxxxxxxxxxxxx:

Thanks for the response Allan. Yes this is SSIS and yes the variable
contains the whole SQL statement. The error I get is "missing right
parenthesis". The query works in MS Access as a pass-through. This is the
query:

(SELECT TO_CHAR(COST) FROM DALLAS.PART_COST WHERE COST_TYPE='1' AND
EXPIRATION_CODE='MAR06' AND PART_ID=OUPT_PRD.PART.PART_ID ) AS FSC

FROM OUPT_PRD.SHIPMENT, OUPT_PRD.SHIPMENT_DETAIL,
OUPT_PRD.SHIPMENT_SERIAL, OUPT_PRD.PLACE, OUPT_PRD.PART
WHERE OUPT_PRD.SHIPMENT.SHIPMENT_ID =
OUPT_PRD.SHIPMENT_DETAIL.SHIPMENT_ID AND
OUPT_PRD.SHIPMENT_DETAIL.SHIPMENT_ID =
OUPT_PRD.SHIPMENT_SERIAL.SHIPMENT_ID AND
OUPT_PRD.SHIPMENT_DETAIL.SEQUENCE =
OUPT_PRD.SHIPMENT_SERIAL.SEQUENCE AND
OUPT_PRD.SHIPMENT.SHIP_TO_KEY_ID1 =
OUPT_PRD.PLACE.PLACE_ID AND
OUPT_PRD.SHIPMENT_DETAIL.ITEM_ID =
OUPT_PRD.PART.PART_ID AND (OUPT_PRD.SHIPMENT.SHIPMENT_ID = '335251')

And this is the code I tried to put in the script object, which also gives
me the same error message even though there aren't any parentheses.

strConnectionString = "Data Source=oupt;User
ID=xxx;Password=xxx;Provider=OraOLEDB.Oracle.1;Persist Security Info=True;"

strSQL = "SELECT EXPIRATION_CODE FROM OUPT_PRD.PART_COST" & _
" WHERE OUPT_PRD.PART_COST.COST_TYPE='1'" & _
" AND OUPT_PRD.PART_COST.PART_ID='KRC13149/15 R1A'" & _
" AND OUPT_PRD.PART_COST.EXPIRATION_CODE='MAR06'"

Dim myAdapter As Data.OleDb.OleDbDataAdapter = New
Data.OleDb.OleDbDataAdapter(strSQL, strConnectionString)
Dim dsFSC As DataSet = New DataSet()
myAdapter.Fill(dsFSC, "FSC")
Dim fscRow As DataRow

For Each dsFSC In dsFSC.Tables(0).Rows
MsgBox("Here")
MsgBox(fscRow.Item(0).ToString)
Next

Thanks for your help.
Phill

"Allan Mitchell" wrote:

> Is this SSIS?
>
> I am not sure what the problem here is.
>
> Does the variable value contain the whole SQL Statement?
>
> Does the query run on Oracle using say Toad?
>
>
>
> --
>
>
> Allan Mitchell
> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
> http://www.konesans.com
>
>
>
>
> "Phill" <Phill@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:78266070-0026-4E7E-8C13-91B493328C10@xxxxxxxxxxxxx:
>
> > I am using an OLE DB provider with a variable as it's data source. The query
> > is against Oracle 8i and for some reason does not like sub selects, so I am
> > going to have to do a lookup for each record within my script component.
> > Does anyone have a sample of how to do this? Thanks
>
>

.

Relevant Pages

  • RE: Breaking down imported information
    ... Single-record append query: ... ' Check if at EOF of Recordset (rsDataViaCode) ... ' if at EOF Exit Do. ... Dim rsDataViaCode As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: LDAP query information
    ... Copyright 1985-2001 Microsoft Corp. ... Dim strBase, strFilter, strAttributes, strQuery, adoRecordset ... Set adoConnection = CreateObject ... ' Construct LDAP syntax query. ...
    (microsoft.public.windows.server.scripting)
  • Re: LDAP query information
    ... Dim strBase, strFilter, strAttributes, strQuery, adoRecordset ... Set adoConnection = CreateObject ... ' Construct LDAP syntax query. ... Yes, the script uses ADO to query AD directly, which is very efficient - no ...
    (microsoft.public.windows.server.scripting)
  • Re: Need WMI script
    ... I also assume that strGroupName is ... If instead strDomainName is the NetBIOS name of a domain, ... Dim adoCommand, adoConnection, strBase, strFilter, strAttributes, xmlDoc, ... ' Construct the LDAP syntax query. ...
    (microsoft.public.windows.server.scripting)
  • Re: Need WMI script
    ... I also assume that strGroupName is ... Dim adoCommand, adoConnection, strBase, strFilter, strAttributes, xmlDoc, ... ' Construct the LDAP syntax query. ... However, if you use the cscript host, ...
    (microsoft.public.windows.server.scripting)
Loading