DTS Help Defining the DTSSource object

Posted on 2005-04-21
Last Modified: 2013-11-30
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
Question by:adwiseman
    LVL 17

    Expert Comment

    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.
    LVL 14

    Author Comment

    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.
    LVL 17

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Viewers will learn how the fundamental information of how to create a table.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now