none
SSIS : Table or View generating DUPLICATE records

    Dotaz

  • Hi,

    I am trying migrate data from FoxPro DB to SQL DB, my dataflow structure is as below

    OLE DB Source-->Derived Column-->OLE DB Destination.

    I see Duplicate records are generated when i try loading the packages using TABLE or VIEW mode in OLE DB Source.

    if i Select SQL Command mode it will load exact records.

    for Example:

    >>using TABLE or VIEW mode records loaded are 122 records(2 Duplicate records)

    >>Using SQL Command mode records loaded are 120 records

    Why is this TABLE or VIEW mode generating duplicate records and how to avoid?? Kindly suggest!!

    Thanks

    Manjunath

     

    7. května 2018 20:23

Odpovědi

  • By using TABLE or VIEW are you pointing to table or a view?

    If its a view, I would check the View source to ensure it doesnt cause duplicate entries to be returned

    Also what according to you represent a duplicate? Is it all values in row being same or do you consider only subset of columns for identifying duplicate?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Označen jako odpověď msmanju99 9. května 2018 20:11
    7. května 2018 20:29
  • Hi,

    Please verify the underlying code of View and also compare the sql code which you are using in OLEDB source. As long as you are using same code and same source using either of options(Table/view or SQL Command) should return same result.

    Thanks,

    Alambir

    Please vote this as helpful or Mark this as answered if this helps!


    Thanks, Alambir Please vote this as helpful or Mark as answered if this helps!

    • Označen jako odpověď msmanju99 9. května 2018 20:10
    7. května 2018 20:35

Všechny reakce

  • By using TABLE or VIEW are you pointing to table or a view?

    If its a view, I would check the View source to ensure it doesnt cause duplicate entries to be returned

    Also what according to you represent a duplicate? Is it all values in row being same or do you consider only subset of columns for identifying duplicate?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Označen jako odpověď msmanju99 9. května 2018 20:11
    7. května 2018 20:29
  • Hi,

    Please verify the underlying code of View and also compare the sql code which you are using in OLEDB source. As long as you are using same code and same source using either of options(Table/view or SQL Command) should return same result.

    Thanks,

    Alambir

    Please vote this as helpful or Mark this as answered if this helps!


    Thanks, Alambir Please vote this as helpful or Mark as answered if this helps!

    • Označen jako odpověď msmanju99 9. května 2018 20:10
    7. května 2018 20:35
  • I tried both ways, 

    1. TABLE or VIEW pointing to table or a view
    2. TABLE or VIEW are you pointing to TABLE or VIEW - FAST LOAD

    both generating extra records, 

    Duplicate records : i can say it as EXTRA RECORDS!! In the source table i have 120 records, and if i use TABLE or VIEW mode it will load 122 records in destination and if i use SQL command mode it will load 120 records.
    it will load some junk values in some cases and in some case it will load empty records.

    find the below screenshots

    Screenshot 1: Table or view Mode


    Screenshot 2: SQL Command Mode

    Thanks

    Manju

    7. května 2018 20:59
  • Hi Alambir,

    underlying code!! where can i see this??

    Thanks

    Manjunath

    7. května 2018 21:00
  • Hi Manjunath,

    Underlying code of view can be scripted out in SSMS. If you right click on the view and click Script view as "Create To". this will give you the underlying code.

    One more option to rule out your issue is to make sure you are using same connection manager when using both options.

    Now, there are performance differences when using Data access mode as SQL Command and Table/View and it's always best practice to select the columns need from the view table and use option "SQL Command".

    Now in your case Dataset is very small so it is strange it is loading extra 2 rows. Once you load data to destination table, Do you know what are the 2 rows and which are the records that are getting duplicated? Can you please post this, that will be helpful.

    Thanks,

    Alambir

    Please vote this as helpful or Mark this as answered if this helps!



    Thanks, Alambir Please vote this as helpful or Mark as answered if this helps!

    7. května 2018 21:39
  • Hi Alambir,

    Yes, Script looks fine!!

    and yes, the connection manager i use for both options are same.

    find the below screenshot of the extra 2 rows.

    Thanks

    Manjunath

    8. května 2018 14:56
  • Hi Manjunath,

    In your previous screenshot column names looks different than what you just have posted now. Are you sure you are pulling the data from same schema table?

    In previous screenshot:

    Thanks,

    Alambir


    Thanks, Alambir Please vote this as helpful or Mark as answered if this helps!

    8. května 2018 17:25
  • I just select few columns(only 5 columns)
    8. května 2018 17:43
  • Hi Manjunath,

    Is this just one to one mapping from OLEDB Source to Destination or you are using any transformation between both source and destination?

    Thanks,

    Alambir


    Thanks, Alambir Please vote this as helpful or Mark as answered if this helps!

    8. května 2018 18:15
  • I just select few columns(only 5 columns)

    Nope

    The metadata looks completely different between the two screenshots

    One says city,state,zip,code etc and other one has serial,effdate,...

    They both doesnt seem to have any relationship to each other


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    8. května 2018 18:24
  • Sorry Guys!! I got confused with the datafile while loading. Since we have two different ZIP tables(which will be loaded to two different schemas) with different category of data in it.

    Please find the below, I loaded using Table or view mode and i am getting four extra records  shown below.

    Thanks

    Manju

    8. května 2018 19:31
  • Hi There,

    Just got in some info on the EXTRA RECORDS!!

    As mentioned earlier in my posts i am performing migration process from FoxPro DB to SQL DB,

    I suspected these extra records might be DELETED records in FoxPro DB. and so i inquired with the team who handles FoxPro DB (source data), they said it is DELETED RECORDS!!

    In SSIS, if we select 'Table or View' mode, then it will load all the records including Deleted records as well!!

    Now, How to Avoid deleted records with 'Table or View' mode?? Kindly Suggest

    Thanks 

    10. května 2018 19:13
  • Hi Manju,

    Not sure if this is possible that SSIS can load records if those are already deleted from source FoxPro DB. As long as that delete statement are committed in FoxPro DB, SSIS can never load unnecessary rows.

     

    Again, there is no difference in using 'Table or View' mode or 'SQL Command' mode except performance when using large volume of data. 

    Thanks,

    Alambir

    Please vote this as helpful or Mark as an answered if this helps!


    Thanks, Alambir Please vote this as helpful or Mark as answered if this helps!

    10. května 2018 19:30
  • Hi There!!

    i found below post about FoxPro Deleted records,

    https://stackoverflow.com/questions/44076998/ssis-package-importing-deleted-records-from-visual-foxpro-db/50395912#50395912

    https://connect.microsoft.com/SQLServer/feedback/details/697259/ssis-2008-r2-ole-db-provider-for-visual-foxpro-ole-db-source-includes-deleted-records-even-if-deleted-true

    http://ilovedata.ca/2011/11/09/issue-ssis-2008-r2-ole-db-provider-for-visual-foxpro-ole-db-source-includes-deleted-records-even-if-deletedtrue/

    Unfortunately the second link from microsoft got retired!!! :-( 

    kindly suggest if you get anything out of it.

    Regards

    Manjunatha

    17. května 2018 17:08