Data transfer with transformation using EF / SQL Server

.net c# entity-framework entity-framework-6 sql-server


I'm attempting to develop code that will move data from one application to a new one.

SQL Server is used by both the old and new apps. The previous database is badly constructed in two key areas: it includes extra, superfluous tables and improperly utilises foreign keys (for example, but not only this, lookup tables are used where an enum should have been used instead).

The "proper" way to do this, in my opinion, would be to use SSIS, but as I am unfamiliar with it and have had trouble understanding how to set it up, I am considering a code-based approach instead (run once console application).

Entity Framework will be used to do this since I am familiar with it and because it is simple to set up.

The biggest issue is keeping connections, thus I'm searching for advice on how to keep linked data up to date. Customer, for instance, has several addresses, and each address has a customer ID.

If if it were that easy, I could just ask the customers. Use Include(x => x.Address) to add the Customers to the newly created database (creating new Ids automatically in the target database along the way). However, I believe that this would be untidy and rather demanding since there are a lot of tables (around 30) that are all connected in some manner and each contain numerous rows of data. This is because the data would probably need to be loaded into memory all at once (since I would have to transform the data as well).

Alternately, you could use SET IDENTITY INSERT to duplicate the tables one at a time while preserving the previous Ids in the new tables, but it just seems weird.

Exist any other options outside enrolling in an SSIS course for myself?

8/7/2018 9:06:09 AM

Popular Answer

For converting data from several sources/destinations, SSIS is more suited. If both databases are running on SQL Server, I would consider transferring the table structure alone using SQL queries. While EF is technically feasible, it will take longer and use more resources, particularly if it is not executed on the database server itself. (data sent by wire, then received)

Say, for instance I had a table that was connected to an order table by a "OrderNumber," but I want to switch to using an OrderId and an FK instead. My new table is set up with an OrderId column that is nullable and an OrderNumber field that generally has a following underscore to indicate that the column will be removed. I conduct a second step to update the OrderId after importing the data with the order numbers pointed to the temporary column. After verifying that all OrderIds have been used and there are no more null values, the OrderId column can be made non-nullable, FKs & constraints can be established, and the additional OrderNumber_ column can be removed. It is often preferable to build up the tables without constraints or indexes when making these modifications and then add them once the data has been migrated. (Smaller, but you should be relatively certain that your data is being sent accurately and fully.) Before deciding on the new FKs, divide the proposed data structure into smaller pieces and transfer or reassociate it one table at a time. Temp tables may also be used in complex situations. All of this may be done on a recovered backup as a proof of concept, scripted, then set up to run on a production server that has been backed up after you are certain that everything is finished and tested.

It would be preferable to have lookup tables together with supporting enumerations as opposed to eliminating the lookup table entirely. When a query returns arbitrary values, values with FKs to lookup tables help maintain referential integrity at the database level and help provide meaning to random numbers. As a sign that these lookup tables are controlled by code and need to be read-only, I often prefix them with "Enum" when they are connected to enums. In order to stop modifications to certain tables, you may also set up Insert/Update/Delete triggers. (which update scripts may turn off and on again when the enum needs to be expanded)

8/7/2018 9:58:11 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow