Data Mapping

[bev]

This may be the wrong place to post this question, and if so perhaps you could direct me to the appropriate forum. We've got a SQL database constructed by our vendor with the installation of their managed care package of which we use less than 10% of its functionality. We get data in via ASCII text files and check them for data errors and then import them into the SQL database via a dog of a custom VB application provided by our vendor. Everytime we need a modification, it's a long tedious unsatisfying process, so we're thinking we'd like to find an alternative tool to allow us to map data from the incoming ASCII flat files to our SQL database. Our preference is a tool, rather than doing the programming ourselves. Can anyone point me in the right direction?

Thanks in advance,
Bev

 

[SQLSister]

Try DTS which you can access from Enterprise Manager just look under Data Transformation Services. There is an import dat wizard, but if it doesn't suit your needs, you can do just about anything by creating your package.

 

[bev]

SQLSister,

Thanks for your response. I *will* look more closely at DTS as a solution to this problem. But we're actually looking for something to purchase rather than program. It's a long story ... and has a lot to do with both my boss' wishes as well as a consultant's advice. So I'm looking to see what's out there that we can purchase.

I know BizTalk is a possible solution, but I think that's in the $40K range. Any other ideas/suggesions anybody?

Bev

 

[SQLSister]

Let's see, your boss would rather consider purchasing a program than actually use what he already has. While DTS packages take some time to set up especially if the transformations are complex, you will still have to do this with any commercial package you purchase (How else would it know what to transform to what following what business rules?). So you probably don't save any time in set-up and you have an additional cost and you add a third piece of software that will not necessarily be optimal for SQL Server and may, in fact, cause conflicts either now or at some future date.

Suggest you do a formal, written cost-benefit analysis to show the boss why this is a dumb idea (phrase it more diplomatically of course). Much harder to argue with it if you have it black and white. Who knows, if you do it right you might even swing a bonus for saving the company money.

If you choose to do this and don't know where to start, let me know and I'll post an example.