June 25, 2008 at 5:31 am
I've created a DTS package that reads order information from Informix SE 7.21 tables into lovely customised tables in SQL Server 2000. That works great.
However, I want to reduce the strain on the Informix server as it is my main transaction processing database and so I want to restrict the data my package imports by referencing an order number in global variables.
In other words, instead of importing all order data, I just want to import an order that has been selected by a user (for completeness of my posting here, I am using an Access front-end to SQL Server and using pass-through queries to run procedures to run the DTS package..............erm......got that?). So, I want the user to select an order number and Access fire the procedure to 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:
SELECT soh_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 AS CustVATno, soh_cusref AS CustOrdNo, soh_ref1 AS Elig, soext_code1 AS Release, pltc_desc AS PayTerms, soh_delcon AS ContrNo, soh_ref2 AS Application, 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 = ?)
And I have set-up a global variable called "SO" in my package properties, with an initial value of "FT036902" for testing purposes. However, I cannot get into the parameter tab on the transform task. When I click it, I get this:
Package Error
HResult of 0x80040e51 (-2147217839) returned
Unexpected error occurred. An error result was returned without an error message.
A search on the Internet on the error code came up with this explanation: "The provider cannot derive parameter info and SetParameterInfo has not been called"
Is there anything obvious I'm doing wrong or haven't set? Has anyone else tried doing this with Informix or another data source other than SQl Server?
Cheers!
June 25, 2008 at 9:49 am
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?
July 2, 2008 at 11:18 am
For anyone interested, I worked around this by using an ActiveX Script Task in DTS that referred to the global variable. E.g:
rsXMC.Open "SELECT soh_ordref, soext_code2, soh_orddate, soh_account, ndm_name, ndm_addr1, ndm_addr2, ndm_addr3, ndm_addr4, ndm_addr5, ndm_postcode, dlcus_txregno, soh_cusref, soh_ref1, soext_code1, pltc_desc, soh_delcon, soh_ref2, soh_transp, soh_ordval + soh_invval AS OrdVal, cvm_desc, soh_delname, soh_delad1, soh_delad2, soh_delad3, soh_delad4, soh_delad5, soh_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 = '" & DTSGlobalVariables("gvSalesOrder").Value & "'", cnSTX
rsXMC being my ADO recordset object and cnSTX being my Informix ODBC connection object. My Access project passes the order number to the DTS job global variable and the script receives it via the DTSGlobalVariables("gvSalesOrder").Value object.
This works great, but I've now hit another snag which I shall be raising a separate forum posting for...........
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply