DTS transformation question

Hello,
I am having problems with a quite simple DTS package. I guess the solution is quite simple, but I can't see it.

E.q: I am importing a Excel sheet with 1-3 columns into a predefined table in SQL 7.0 (default values). The number of columns might however change from time to time. I have designed the DTS package transformation using VB script.

Function Main()
DTSDestination("A") = DTSSource("A")
DTSDestination("B") = DTSSource("B")
DTSDestination("C") = DTSSource("C")
Main = DTSTransformStat_OK
End Function

but when I execute the package linked to a Excel file just containing column A and C I get an error claiming that the DTSDestination("B") is missing.

I am looking for a method that will either ignore the error or ignore the command under execution.

My real DTS package has several columns that might be missing.

ThanksPost some examples of your data. How are you able to map to columns a and c skipping b in your destination ?|||Originally posted by rnealejr
Post some examples of your data. How are you able to map to columns a and c skipping b in your destination ?

I was thinking of something like this

Function Main()
DTSDestination("A") = DTSSource("A")
IF ( DTSSource("B") EXIST) Then
DTSDestination("B") = DTSSource("B")
ELSE
DTSDestination("B") = "Mydefaultvalue"
END IF
DTSDestination("C") = DTSSource("C")
Main = DTSTransformStat_OK
End Function

, but the logical expression ( DTSSource("B") EXIST) is not valid. I am looking for some command or expression that will handle this.

I have seen postings where the expression is like:
IF isNull( DTSSource("B")) Then ....
,but isNull is just checking for empty cells. In my case the column "B" is not available.|||I don't think this is possible - I will check further - However, since you only have 3 possibilities you can create a path in dts to check the number of columns in the excel file and based on the answer (success or failure) have it execute the appropriate transformation task.|||Originally posted by rnealejr
I don't think this is possible - I will check further - However, since you only have 3 possibilities you can create a path in dts to check the number of columns in the excel file and based on the answer (success or failure) have it execute the appropriate transformation task.

Thanks rnealejr

I just got feedback from the SQL Development Team. They say:

The ActiveX Script task includes a list of functions that you can use in the
DTS scripts.
Instead of
IF ( DTSSource("B") EXIST) Then
DTSDestination("B") = DTSSource("B")
ELSE
...

Try
IF not DTSSource("B") is nothing Then
DTSDestination("B") = DTSSource("B")
ELSE
...
I have not been able to try this yet, but hopfully it will work. I will give you feedback if it does. Thanks again for your time.|||Interesting - If you are successful with this, then DTSSource is an object variable. You might try Empty/vbNull/Null as well.

Let me know if you are successful.|||Originally posted by rnealejr
Interesting - If you are successful with this, then DTSSource is an object variable. You might try Empty/vbNull/Null as well.

Let me know if you are successful.

Hi,

It did not work.

It seems that the Source columns are validated before the VBscript is executed.

Lasse