locked
Can we use a fully defined ADO.NET Entity Framework to partially-map an ETL job? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Imagine you have an ADO.NET Entity Framework that maps entities over an IBM IDS Datasource (DB2 or Informix).

    Now imagine that someone asks you to replicate that data in an MSSQL database transforming it to exactly match the EF object schemas ( table and column names, column data-types, etc ).  Consider this in large scale as well.  Consider that this database may house a few hundred tables, and 500,000 to 1,000,000+ rows per table.

    Given the work that has already gone into defining the EF, it would be nice if I could at least stub out an ETL package based on the EF that is already defined.  

    That would be preferred over duplicating the effort in SSIS.  Worse, having to iterate over every entity, and every object instance in each entity.  Those options seem very slow, and very inefficient.  Hoping there is a better, smarter, accelerated way?

    Friday, February 13, 2015 1:39 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,

    For your description scenario, a batch insertion should be needed. If you want to use the Entity Framework to complete this workitem, you could have a check the Entity Framework ETL which provides a batch savechange method and would improve the performance. Of course, it still needs to iterate over every entity, and every object instance in each entity since in Entity Framework, it is designed to work with entities.

    I am not experienced with the DB2 database, however, I suggest that you could confirm with DB2 forum to see if this database has the BULK INSERT (Transact-SQL) function similar with SQL Server database. If has, I suggest that you could call the bulk insert syntax directly in Entity Framework(EF does support execute sql statement directly) or use the stored produce so that these operations as iterate over every entity, and every object instance in each entity are not needed.

    Regards.

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Fred Bao Monday, February 23, 2015 1:34 AM
    Friday, February 13, 2015 6:51 AM