I have to do a migration where I should retrieve data from 40 Access tables, which should contain several of the same columns (but not all, the schemas are different). To make it simpler, I used SSMA to convert these tables in SQL Server.

It is part of a migration through SSIS. I have to merge all these tables in one table without duplicates. A portion of the treatment is the same for all the tables but there are some subtleties. One problem is that the column names have slight differences (like X_Y instead of X). And each table have specific columns but I do not want to retrieve the data in those.

I hope it is not too broad a question, but:

  • Is it better to try to do a generic part, taking the table name as a variable to apply the same treatment with conditions in it to take care of the differences?

  • Or should I just copy & paste a core/template data flow and manage each difference in separate packages?

  • "without duplicates" That's the devil in the details. Table A defines customer X as living on Main Street. Table B defines same customer X as living on Oak Street. Given that we can only import one instance of this customer - which one is correct? How did you know that - aka what is the business rule for survivorship – billinkc Oct 6 '15 at 14:46

Given that:

  • There are some columns you don't want to pull through for each table
  • Column names are different for each table
  • You only have 40 tables
  • This is a one-time migration (this is safe to assume, correct?)

Then I would just copy and paste the core dataflow and make per-table changes as appropriate. For a non-repeating process that is a small amount of tables (not 200 or more), you don't want to spend too much time creating a repeatable process that will never be repeated ;-). And you are going to have to manage the differences anyway, so you might as well just put your effort into that part.

And it should be possible in SSIS to define a reusable component (script task) to handle some of the "common" manipulations. If not, just create a stored procedure that accepts a table name and handle the common steps there. Then, in SSIS, just call that Stored Procedure for each table and that way you will have a single definition for the common stuff, and if you need to make a slight change in the common changes, then you will only need to make them in one spot :).

(please see UPDATE section below) Another option for handling data migration when you have issues of not pulling all fields over and possibly renaming fields is to use the SqlBulkCopy Class in .NET as it handles those things quite nicely. You can either create a C# Task in SSIS to use this class, or you can use a SQLCLR stored procedure such as DB_BulkCopy which is part of the SQL# SQLCLR library (which I am the author of, but this stored procedure is in the Free version). I describe this in a little more detail in the following answer:

Import data from one Database to another script


The following requirement was added a day after I posted the suggestions above:

I have to merge all these tables in one table without duplicates.

In light of this new requirement, I think that using the SqlBulkCopy alternate option is less attractive than sticking with SSIS in the manner that I described first. The only way to de-dupe the data using SqlBulkCopy / DB_BulkCopy would be to run the process from the destination / target server, load the data into a temp table (it would have to be a global temporary table -- meaning, starting with ##) from the source server using SqlBulkCopy or DB_BulkCopy, and then insert into the real destination table from the global temporary table while filtering out the "duplicates" based on whatever rules you have to determine what a duplicate is.

| improve this answer | |

Have you looked at the SQL Server Migration Assistant tool? This would probably assist you greatly in the migration as it maps source tables to destination tables despite possible naming irregularities. The tool is provided to my knowledge free of charge.



| improve this answer | |
  • I already use it, sorry for not explaining that. So my problem is if I should loop through the new SQL tables as I must merge them in one table or if I should have a specific treatment for each. – Loïc Lopes Oct 4 '15 at 8:54
  • I think I need some clarification here. Are you trying to figure out if it is best when migrating multiple Access files to a single SQL Server database if you should take tables from your Access file that would be similar and combine them together to form a single larger table? For example, if you have File_A with Table_A and, File_B with Table_A1 does Table_A have a relationship with Table_A1? – Travis Page Oct 5 '15 at 13:40
  • No, I want to know what would be the best practice to merge several tables in the same database which have some similar columns (but not all). Should I make a specific treatment for each table or should I put some effort into making something generic I can re use for each table? I hope it is more understandable. – Loïc Lopes Oct 6 '15 at 8:16
  • I think this falls into a scope of knowing your data unfortunately. Depending on the migration source to destination (OLTP vs OLAP) the strategy for normalization is going to vary. Also, the practice of master data management comes into play. Which record is the ultimate source of record? Both of these make it extremely challenging to offer a solution to the problem that someone else may be able to utilize. I hope I have given you at least some clarification. If so, based on the comments I will update my answer to reflect the thread here. – Travis Page Oct 7 '15 at 1:45
  • The ultimate source of records are the SQL Server tables created from SSMA. I must create new SQL server tables (in another database) which take each information from all the source tables. – Loïc Lopes Oct 7 '15 at 7:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.