I work as a developer in a manufacturing company. We use Jitterbit to manipulate complex XML from one system and output it to SQL Server, where further Jitterbit scheduled tasks manipulate the raw staging data before writing it to SAGE.
We've had to introduce a horrible hack (surely not!) to ensure that drawing numbers from XML are inserted into the correct columns in one of the SAGE tables. It would be almost impossible to script example tables and stored procedures to show how this works (and it's proprietary as well) but here's a general overview of what happens.
Jitterbit generates a collection of rows, one per part, from the raw XML. Each row may have a comma-separated list of drawings. These are inserted into a staging table.
Jitterbit has another scheduled task to open a view (let's call it MyDrawingUpdate) on the staging table and, one by one, process each row by sending parameters obtained from the row to a stored procedure which then manipulates the data and writes it to SAGE.
Part of this process is to split the comma-separated string of drawings into a temporary table and then concatenate the rows into separate string variables according to business logic. Once this process is complete a MERGE operation on the SAGE table is performed. So, for example, at the end of this there might be a string variable called @text01 with a value 'DWG001 REV02, DWG003 REV01' which is destined for a particular column in SAGE.
This is the weird part. If I allow Jitterbit to manage this process, I can see, by constantly refreshing a query
SELECT * FROM MyDrawingUpdate
that the number of rows that remain to be processed diminishes. After a minute there are no further rows to process. So I go to SAGE and I can see that the value of @text01 did NOT get correctly written to SAGE. In fact it wrote only 'DWG001 REV02', omitting the second drawing.
But if I execute this stored procedure from SSMS using the same parameters for a single row, the value in @text01 is correctly processed, and the column in SAGE has both drawings.
Other columns, where there may be only one drawing, are correctly populated. But where there are two or more drawings, comma-separated, only the first value is written to SAGE.
So how can the same raw data, operating on the same stored procedure, generate a different result if it's run as part of a collection of updates, than if it's run atomically.
- This topic was modified 1 year, 11 months ago by edwardwill.