August 10, 2005 at 1:48 pm
im trying to execute a package which loops through a directory and inserts multiple text files into one table. the problem is each text file contains a different number of columns and the dts transformation mapping throws an error each time the number of columns change from file to file. is there a way to "clear transformations" automatically?
ie: File 1 contains 25 columns & inserts into TableA which contain 25 columns
File 2 contains 23 columns & an error is thrown because the transformation mapping is looking for column 24 and 25.
thanks!
ryan
August 10, 2005 at 5:57 pm
One approach would be a custom transformation script. Here is a simplistic example (for a table with two columns only)
'**********************************************************************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'************************************************************************
Function Main()
Dim Ptr as Integer
On Error Goto OutOfColumns
Ptr = 1
DTSDestination("ID") = DTSSource("ID")
Ptr = 2
DTSDestination("Description") = DTSSource("Description")
ProcExit:
Main = DTSTransformStat_OK
Exit Function
OutOfColumns:
If Ptr <=1 Then
DTSDestination("ID") = ""
End If
If Ptr <=2 Then
DTSDestination("Description") = ""
End If
Resume ProcExit
End Function
------------------------------------
What this does:
Ptr keeps track of what column is being referenced. When your error is generated, PTR will point to the first un-updated column. The code in the error trap will update the fields as needed (default values, NULL, whatever), and go to the OK step.
Will it work? It should. Will it be fast? No.
If there was some way to determine the number of columns being imported prior to starting the file import, I would write the appropriate import DTS routines for each possible number of columns, and have a conditional call the appropriate import.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply