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 post 1 (of 2 total)
You must be logged in to reply to this topic. Login to reply