SQL Server Performance Forum – Threads Archive

DTS Data pump question

This may be a simple question, but I’ve had the good fortune (?) until now of not having ad to do anything with DTS. Unfortunately, that has changed, and now I need to find out how to modify a DTS package. The packages has 3 file being imported into a common table. The files have the same format. I need to modify the data pump so that in addition to the file to column mappings, a literal value will be inserted into the final Recordtype column on the table (types being A for Additonal, D for Delete and U for Update). I’ve done a search and can’t find a decent description to do what I’m sure is probably straight forward task. So review: ———– ———-
|TEXT File| | table |
———– ———-
|Col1 |—–>|Col1 |
|… | |…. |
|ColX | |ColX |
———– |A/D/U | <—– how best to get this inserted during the data pump?
———-
What is the best way to Panic, Chaos, Disorder … my work here is done –unknown

---

What I’ve managed to do so far is to add a new transformation, and in ActiveX do the following: ‘**********************************************************************
‘ Visual Basic Transformation Script
‘************************************************************************ ‘ Copy each source column to the destination column
Function Main()
DTSDestination("RecordType") = "D"
Main = DTSTransformStat_OK
End Function
Isn’t there a better way? This (in my current layman understanding) means that activeX script will run for EVERY row in my file? Panic, Chaos, Disorder … my work here is done –unknown

---

]]>