SQL Server Performance

Using DTS global variables with an Informix data source

Discussion in 'SQL Server DTS-Related Questions' started by rock_and_roll, Jun 25, 2008.

  1. rock_and_roll New Member

    I've created a DTS package that reads order information from InformixSE 7.21 tables into lovely customised tables in SQL Server 2000. Thatworks great.

    However, I want to reduce the strain on theInformix server as it is my main transaction processing database and soI want to restrict the data my package imports by referencing an ordernumber in global variables.

    In other words, instead of importingall order data, I just want to import an order that has been selectedby a user (for completeness of my posting here, I am using an Accessfront-end to SQL Server and using pass-through queries to runprocedures to run the DTS package..............erm......got that?). So,I want the user to select an order number and Access fire the procedureto run the DTS package to get the data for the selected order.

    OK, I have a transform data task like this in my DTS package:

    SELECTsoh_ordref AS SOno, soext_code2 AS Ref, soh_orddate AS OrdDate,soh_account AS Cust, ndm_name AS CustName, ndm_addr1 AS CustAddr1,ndm_addr2 AS CustAddr2, ndm_addr3 AS CustAddr3, ndm_addr4 AS CustAddr4,ndm_addr5 AS CustAddr5, ndm_postcode AS CustPost, dlcus_txregno ASCustVATno, soh_cusref AS CustOrdNo, soh_ref1 AS Elig, soext_code1 ASRelease, pltc_desc AS PayTerms, soh_delcon AS ContrNo, soh_ref2 ASApplication, soh_transp AS DelTerms, soh_ordval + soh_invval AS OrdVal,cvm_desc AS Currency, soh_delname AS DelName, soh_delad1 AS DelAddr1,soh_delad2 AS DelAddr2, soh_delad3 AS DelAddr3, soh_delad4 AS DelAddr4,soh_delad5 AS DelAddr5, soh_delpost AS DelPost
    FROM sohead, OUTER soextra1, plterm, cvmas, dlcust, ndmas
    WHERE soh_ordref = soext_ordref AND soext_seq = 0
    AND soh_terms = pltc_code
    AND soh_currency = cvm_currency
    AND soh_account = dlcus_customer
    AND dlcus_ndcode = ndm_ndcode
    AND (soh_ordref = ?)

    AndI have set-up a global variable called "SO" in my package properties,with an initial value of "FT036902" for testing purposes. However, Icannot get into the parameter tab on the transform task. When I clickit, I get this:

    Package Error

    HResult of 0x80040e51 (-2147217839) returned
    Unexpected error occurred. An error result was returned without an error message.

    Asearch on the Internet on the error code came up with this explanation:"The provider cannot derive parameter info and SetParameterInfo has notbeen called"

    Is there anything obvious I'm doing wrong orhaven't set? Has anyone else tried doing this with Informix or anotherdata source other than SQl Server?

    Cheers!
  2. rock_and_roll New Member

    As a follow-up to this, I'm starting to realise that it's the Informix ODBC driver that can't handle the "?" in the SQL statement. Are global variables only allowed with SQL Server data sources?
  3. satya Moderator

    That shouldn't be a problem, global variable works under the package and driver doesn't have any influence here. Have you tried that option>

Share This Page