November 22, 2005 at 10:13 pm
Is there any way of transforming/mapping source and destination columns programatically. Also, I would like to know the types of transformation we can perform on data.
Thanks & Regards
Shakeel
November 23, 2005 at 10:04 am
This question and similar ones are asked time and time again.
The answer unfortunately remains the same. Not easily.
In order to do this, you would have to delve into the object model in DMO and create a a package on the fly. However, I have never seen anyone post the code to do so.
If you want to have sonmething dynamic, then the way to go is to create a stored procedure that accepts source and destination columns and then uses them to create an insert statement using dynamic sql - Messy and not very secure.
With regards to types of transformations, dts will perform the usual suspects. E.G Adding two columns together, trimming strings. If you want something more advanced like normalising a table, then forget it. Freehand sql is the only way. if you want this kind of transformation power look into something like Informatica.
At the end of the day DTS is really a cheap tool that is simple to use and performs 90% of most people's requirements and connects to multiple data sources. It is not intended to be dynamic in nature, nor possess advanced transformation procedures.
However, the new version in 2005 (SSIS) is moving nearer to a more complete product.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply