CODEWITHSUNDEEP
sqlsql-serverdata-migrationdata-mapping
user3452281
user3452281

Reputation: 13

SQL data mapping & migration tool

I am developing one automation tool for data migration from one table to other table, here I am looking for one function or SP for which I will pass source column and destination column as input parameter. I want output parameter to return true when source column data is compatible to copy to destination column. If not then it should return false.

For example, if a source column is varchar and a destination column is integer, the script should check all the data in a source column in good enough to move to an integer column or not and return the output flag. I want a script to work like this for all types of data types. Any suggestions will be helpful.

Upvotes: 1

Views: 1703

Answers (2)

Vinnie
Vinnie

Reputation: 3929

I have an SSIS solution for this that I put together using EzAPI. I've got it posted to GitHub, so feel free to look:

https://github.com/thevinnie/SyncDatabases

Now, the part of that which is relevant to you would be where I use the information schema to make sure that the source and destination are schema matches. If not, C# script task will generate the statement to create, alter or drop the necessary column(s).

The EzAPI part is cool with SSIS because it will allow you to programatically generate an SSIS package. For the project requirements, I needed to be able to load data every time and not let schema changes in the source break the process.

Comments and recommendations are welcome. Hopefully it'll help, but I think you'll be looking at the INFORMATION_SCHEMA.COLUMNS either way.

Upvotes: 0

bartover
bartover

Reputation: 428

If you're on SQL Server 2012 you have TRY_CAST(), TRY_CONVERT() and TRY_PARSE() at your disposal (see this post by Biz Nigatu of blog.dbandbi for comparison).

That said, you still need to check for truncation errors, e.g. by converting to target datatype and back, then comparing the original value with the one after conversions.

I've seen similar tools in the past, might be a good idea to see if one isn't already available online for free. Even a purchase might be less expensive than the time you put into developing and troubleshooting your own tool.

Upvotes: 1

Related Questions