August 26, 2004 at 7:13 am
I have the opportunity to take an Input Text File (output from a 3rd Party Application), run it through some transformations and produce another Text File (for input into another 3rd Party Application.) I have done the basic transformations a lot but I am looking for a creative way to handle this one particular transformation. On the Input File there is one particular kind of record (Security ID is the key) which takes up two lines items. One line item contains a negative value and the other line item contains a positive value for price. I need to read both of these records, perform a calculation with fields from both records and then write the record (one line item) to the Output Text File storing the Security ID of the record with the positive value for price.
I was thinking about using DTS and ActiveX Scripting to load (and perform the basic transformations) the Input Text File into a SQL Server Table. Then within the same DTS Package execute a SQL Store Procedure (using a Cursor) to read the two line items, perform the calculations and then insert the one combined record into another SQL Table. Then within that same DTS package I would create the Output Text File from the SQL Server Table.
Please provide some thoughts, ideas, suggestions, etc. I was thinking about using a Cursor because I did not know how to (with ActiveX Script) read the two line items in from the Input Text File when the line items may not be stored right after one another. Could I use an Update Statement with a Sub query against the SQL Table to process the two line items from the SQL Table and Insert the combined record into a new table? Thanks in advance.
August 27, 2004 at 8:34 am
You stated that “the line items may not be stored right after one another”. Is there at least some sore of key value that relates the 2 lines? If so, I would say that you are on the right track all the way up until the point of using a CURSOR. Rather than CURSOR through the imported table, use a SELECT QUERY w/ whatever combination of GROUP BY, SUM, etc. aggregate functions in order to retrieve you single row desired output. This would be much more efficient and make for cleaner code.
Corie Curcillo
MCT, MCDBA, MCSD
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply