Database design: how to investigate raw data and model it into an existing database?

As a database developers, what is the best way to investigate new raw data for database when you have to model this data into an开发者_如何学编程 existing database? I mean a situation where you get raw data for example in text files and you don't have any description about the data types or column lengths in that file. I'm looking for tools and best practices to do this investigation. Is there available some pre-defined tools for this in SQL Server?

Edit: I'm talking about the technical side of this process, e.g. how to see if data in a columns has to be mapped into SQL Server table as tinyint, int or bigint...

There are no tools that will tell you what data means.

You need to talk to the owners of the data, understand the meaning of it and when you are comfortable with your understanding, you can model your database and create your ETL process.

Seems to me that you are looking for a data-profiling tool. Your title "... to model ..." is a bit misleading.

Take a look at a few options:

• SSIS data profiling task

• Talend

• Data Cleaner