DTS Help Defining the DTSSource object

This is a 2 part question

First, where can I get a good reference for DTS Package objects like (DTSSource, DTSDestination , DTSGlobalVariables, ....) I want to know all the paramerers, and functiosn of all the objects I use when writing DTS activeX scripts?

Second, is my issue.

I'm loading a tab Delimited File into a table with 50 fields.  The number of columns in the file changes, from record to record, and file to file, but never exceedes 50.  What is the best way to load them in.

My first attempt yielded a ActiveX transformation task, that looked something like this.

Function Main()
      DTSDestination("F0") = DTSSource("Col001")
      DTSDestination("F1") = DTSSource("Col002")
      DTSDestination("F2") = DTSSource("Col003")
      DTSDestination("F3") = DTSSource("Col004")
      DTSDestination("F4") = DTSSource("Col005")
      DTSDestination("F5") = DTSSource("Col006")
      DTSDestination("F6") = DTSSource("Col007")
      DTSDestination("F7") = DTSSource("Col008")
      DTSDestination("F8") = DTSSource("Col009")
      DTSDestination("F9") = DTSSource("Col010")
      DTSDestination("F10") = DTSSource("Col011")
      DTSDestination("F11") = DTSSource("Col012")
      DTSDestination("F12") = DTSSource("Col013")
      DTSDestination("F13") = DTSSource("Col014")
      DTSDestination("F14") = DTSSource("Col015")
      DTSDestination("F15") = DTSSource("Col016")
      DTSDestination("F16") = DTSSource("Col017")

' This is where I was trying to figure out if Col018 was a valid DTSSource Column, if not, don't try and load it.
      IF IsNull(DTSSource("Col018").Value) then
            DTSDestination("F17") = DTSSource("Col018")
                end if

      Main = DTSTransformStat_OK
End Function
LVL 14
Who is Participating?
BillAn1Connect With a Mentor Commented:
I don't know of a way to handle cases where the source file has variable number of columns.
Is it possible to add a header row into the file with 50 columns? Then even if the data has less than 50 cols, the DTS will recognise that there are 50 cols in the source, and so you should have no problem. Otherwise I don't know of any ActiveX to check for whether a column actually exists or not.
There is no need / value in checking if the value is NULL before asigning it.
If you don't load anything, the target field will be NULL, which is the saem thing as if you loaded NULL in the firts place, so why bother check.
If you don't need any other data processing except to copy the source cols to the destination, youwill be much beter off performance-wise not to use activeX scripting at all, but rather use a simple Data Transformation task instead.
adwisemanAuthor Commented:
Yes true, But I am doing some other stuff in the transformation that I did not post, including writing global variables to additional fields in the table.  You make a good point though, perhaps I should look into doing a bulk copy, and then updating after the fact.

The problem is not that the field in the source is null, it's that it does not exist.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.