none
Data Loss issue while transferring data from Source(Oracle) to Data Warehouse Staging Database(SQL Server) RRS feed

  • Question

  • Project Overview

    Loading data from Oracle Database to Datawarehouse Staging Database(SQL Server). Using ETL process we are loading data from Oracle to SQL Server. Staging Database in SQL server is an exact replica of Source Oracle schema.

    We have developed an SSIS package to initially load complete data into Staging Database and then apply data cleaning and load the data to Data Warehouse(SQL Server). In SSIS Package for migrating data from Source to Staging, we are using "Data Flow Task" in SSIS package and we are splitting the table data into 20 batches and loading batch-wise into Stage database in a sequential order

    Issue:

    We will build the .ispac file and deploy in PROD Server scheduled a package execution in SQL server agent. After package complete execution. The count of the source tables is not matching with the stage tables.

    But when we are running the same package in the local machine the count of source tables are matching with stage tables.

    As we are loading the data in Batch-Wise. In some cases, we are missing a few records in every batch or only a few batches. But we are not sure why the records are missing while transferring the data only in PROD Server

    Wednesday, August 14, 2019 5:59 AM

All replies

  • Hi Sai Varun Dodla,

    Please check which records are missing .

    May I know if you change the SSIS package in SSDT after you deploy in PROD Server?

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, August 14, 2019 9:39 AM
  • Hi Mona,

    Thanks for your reply, We are aware of which records are missing in a table but we are not aware why those records are missing? May be due to network issue or SSIS package issue. We are not sure what might may be reason?

    And we are not changing SSIS package in SSDT while deploying in PROD Server.

    Regards,

    Sai Varun Dodla

    Tuesday, August 20, 2019 5:54 AM
  • Hi Sai Varun Dodla,

    Please try to redeploy the SSIS package in PROD Server.

    Could you please share the example of the good data and missing data , and screen shot of your SSIS package?

    (Note:Please hide your personal sensitive info)

    Best Regards,

    Mona



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, August 20, 2019 8:15 AM