VB Error while parsing is OK...

Question

VB Error while parsing is OK...

FreeHansje

SSChampion

Points: 11751
More actions
November 29, 2005 at 12:27 am

#60302

Hi there,
I'm trying to transfer data from 1 table to another. However, the targettabel has 1 column to be filled with a GlobalVariable. I have created an ActiveX in the transformation-step with this code:
' Copy each source column to the destination column
Function Main()
DTSDestination("Field1") = DTSSource("Field1")
DTSDestination("Field2") = DTSSource("Field2")
DTSDestination("Field3") = DTSSource("Field3")
DTSDestination("Field4") = DTSGlobalVariables("g_RunNr").Value
Main = DTSTransformStat_OK
End Function
Parsing tells me this is OK, but when I execute the package this steps fails with:
Error description: Invalid procedure call or argument: 'DTSDestination'
If I leave the last statement out it performs fine, so it must be the last line. What do I do wrong here?
Greetz,
Hans Brouwer

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

From the error message, this does not look like a problem with your GV. You could check this by replacing the field4 line with

msgbox DTSGlobalVariables("g_RunNr").Value

and then run the step interactively (against a v small recordset, otherwise you will have to click OK a zillion times to cancel the messagebox while it runs through all the recs).

Maybe field4 has not been identified as a valid destination column for this transformation? Check 'Source Columns' under Transformation Options to ensure that it has. You could maybe test this by changing the field3 mapping:

DTSDestination("Field3") = DTSGlobalVariables("g_RunNr").Value

and deleting the final line. If that works OK, you've proved that field4 is the problem.

FreeHansje SSChampion Points: 11751 More actions · Answer 2

Tnx Phil, I think I solved the problem with your help, but still am flabbergasted why it did not work. I had used the msgbox to check my Global end knew it was there and correct.

First I transferred 3 columns, used ActiveX in my DataPump to add the 4th column: an error occurred. This time I copied also the 4th column and changed the DTSSource("Field3") into DTSGlobalVariables("g_RunNr").Value. And now it runs...but...

This was just a testcase to find out how to do what I wanted happening. In the reallife situation there is no DTSSource for the column to be filled with the Global. Ofcourse I can add a dummycolumn, but I would think this should not be necesarry.

I keep trying, if you have any suggestion to resolve this, please let me know.

Thanks again,

Greetz,
Hans Brouwer

FreeHansje SSChampion Points: 11751 More actions · Answer 3

Never mind my last posting, I think I got it running. I did a test with the real-life tables, uneven columncount and added the line to fill the last column. It works.

Still don't know why the first time this would not work, but never mind.

Tnx again,

Greetz,
Hans Brouwer

Phil Parkin SSC Guru Points: 247180 More actions · Answer 4

If there is no DTSSource column to put your GV into, DTS does not know where to put it - which may be stating the bleedin' obvious. That is why you were getting the 'invalid argument' error.

Which brings me onto my next question ... why are you setting the value source data? Surely you would want to be modifying the value of the destination data, unless you actually want to write back to the source data as part of the transformation.

Anyway, sounds like you're making progress, so that's good.

FreeHansje SSChampion Points: 11751 More actions · Answer 5

Hm..I think I did not make completely clear what I intend to accomplish.

There is this datatransfer from Table1 to Table2; these tables are similar, but for 1 extra column in T2: a number(INTEGER), FK and part of the PK, which indicates a loadnr from yet another table. Since this changes every run, and because it is used elsewhere in the package, I have placed this RunNumber in a Global.

When loading, this RunNumber is to be put into this extra column in T2.

Hope this makes it clear? And yes, I am very new in VB, do/did most programming in T-SQL.

Greetz,
Hans Brouwer

Phil Parkin SSC Guru Points: 247180 More actions · Answer 6

Why not just write a stored procedure and use that? Usually no need to use DTS if all of your data is within SQL Server.

-- edit. I misread your original post a little ... sorry. As long as DTSDestination("Field4") has been defined as a valid destination field, I see nothing at all wrong with your code. It should be straightforward.