6 Replies Latest reply on Jun 19, 2015 7:44 AM by coherentkris

    Export/Import process anomaly

    coherentkris

      I am trying to diagnose an issue with a stable data transfer script that recently reported inconsistencies.

       

      Process goal is to move data from ESS shadow tables (oracle) into FM tables.

      The process is fired nightly via server side script and has been running perfectly for months.

      Their are 12 tables where data is moved one table at a time.

      Two weeks ago one and only one of 12 table data sync routines showed problems.

      The destination table is NOT nulled before import.

       

      In a nutshell:

      Go to source (ESS) layout

      Capture the count of records (10516)

      Export all of the data into a .mer file

      Record the export last error code (0)

      Go to destination layout (FM table)

      Import matching on three fields, auto enter calcs on, add remaining records as new

      Record the import last error code (0)

      Count records in destination (10515)

      Compare destination record count with source record count.

       

      Their is no garbage ( ascii control characters ) in the source data.

      The source data contains no empty values in any of the 3 match fields.

      The 3 match fields form a valid composite key in the source table (no duplicates exist).

      Validated that the .mer file contains the correct record count (10516).

      Import dialog character set is Windows (ANSI).

      Output dialog character set is Windows (ANSI) and no formatting is retained.

      import last error code = 0.

      export last error code = 0.

      Their is no evidence of corruption (server did not experience any unplanned shutdowns, consistency check of the file shows no issues, recover on the file shows no significant issues)

       

      After run:

      Source table total record count is 10516.

      Destination table total record count is 10515.

      These numbers should match.

       

      Unfortunately i am unable to post the file or the script.

       

      Any thoughts?

       

      KM

       

      Message was edited by: Kris Musshorn The 3 match fields form a valid composite key in the source table (no duplicates exist).

        • 1. Re: Export/Import process anomaly
          Extensitech

          Can you determine which one record isn't making it? Create a relationship from ESS to destination and omit where there's a matching destination record? Or export both lists to excel and check them against each other using a vlookup?

           

          This is, I think, the prime suspect

          The 3 match fields for a valid composite key in the source table (no duplicates exist).

          ... but if you could identify the record that's not making it, I believe the reason will be easier to spot.

           

          Chris Cain

          Extensitech

          • 2. Re: Export/Import process anomaly
            coherentkris

            I am unable to identify the errant record.

            Using the composite key:

            Every record in source has a match in destination after run.

            Every record in destination has a match in source after run.

            • 3. Re: Export/Import process anomaly
              Extensitech

              If that's true, then our "prime suspect" isn't true.

               

              Chris Cain

              Extensitech

              • 4. Re: Export/Import process anomaly
                user19752

                What is your 3 match fields ? all text ?

                Create relation between old and new tables, then

                Count(old::matchField)>1

                in new table will show "duplicated match" records.

                • 5. Re: Export/Import process anomaly
                  Fred(CH)

                  I probably missed something since english is not my native language but this statement sounds wrong to me because :

                   

                  In a import step, when there are many matching fields, if only one of those is matching the record IS updated, and thus, not created.

                   

                  So in other words, i think you cannot expect any valid composite key based on multiple fields with import step. You need to create a calculated key which concatenate the three field. Then, use it as the only matching field on your import step.

                   

                  coherentkris a écrit:

                   

                  The 3 match fields form a valid composite key in the source table (no duplicates exist).

                  • 6. Re: Export/Import process anomaly
                    coherentkris

                    Update:

                    Seems as if my analysis of the situation was incomplete.

                     

                    I had forgotten a critical fact in ESS relationships and relationships in general.

                     

                    Error 1 - the composite key on the source (ESS) side used a calculation (FM / Unstored) field as a match field.

                    Error 2 (once error 1 was fixed) - The relationship between source and destination using the composite key was one to many.

                     

                    Fix - null the destination table before import and add new records on import.