I am trying to build a DTS that updates a centeral database with data.
I need to load only the new data each time.
So I build a lookup that gives me the max data in the destination db
and then tried to use it in an if statement.
It does not work. what am I doing wrong?

'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()


if ( DTSLookups("test_max").Execute(DTSSource("Expr1")) <
DTSSource ("Expr1") ) then

DTSDestination("Test_end_date") = DTSSource("Expr1")
DTSDestination("Tool_name") = DTSSource("Expr2")
DTSDestination("Y") = DTSSource("Y")
DTSDestination("X") = DTSSource("X")
Main = DTSTransformStat_OK
end if
End Function

In message <12598265.0404011145.6c2e7b6b (AT) posting (DOT) google.com>, Nechama
Katan <nechamakatan (AT) yahoo (DOT) com> writes
Why is it not working? Any errors?

First guess is that you wish to not insert when the If test is false, in
which case add an else clause

Else
Main = DTSTransformStat_SkipInsert
End If
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

I added the else and I get the following error.
"the number of failing rows exceeds the maximum specified.
Error Code: 0
Error source = Microsoft VBscript runtime error
Error Description: Object doesn't support this property or method.
"

I am doing a datatransform task.

Nechama


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

In message <OCi1WtDGEHA.576 (AT) TK2MSFTNGP11 (DOT) phx.gbl>, Nechama Katan
<nechamakatan (AT) yahoo (DOT) com> writes
So the transform is failing, but could you post the full code, otherwise
I'm just guessing again. Is that the full error message as well, I would
have expected a line number in there for a VBScript error.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Thanks,
Here is the complete package.
The transform without the if works fine.
'************************************************* ***************
'Microsoft SQL Server 2000
'Visual Basic file generated for DTS Package
'File Name: C:\Documents and Settings\nkatan\Desktop\PCM.bas
'Package Name: PCM
'Package Description: Source
'Generated Date: 4/5/2004
'Generated Time: 7:58:31 AM
'************************************************* ***************

Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
set goPackage = goPackageOld

goPackage.Name = "PCM"
goPackage.Description = "Source"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 2
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = 4096
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0



'-----------------------------------------------------------------------
----
' create package connection information
'-----------------------------------------------------------------------
----

Dim oConnection as DTS.Connection2

'------------- a new connection defined below.
'For security purposes, the password is never scripted

Set oConnection = goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0 ")

oConnection.ConnectionProperties("User ID") = "Admin"
oConnection.ConnectionProperties("Data Source") = "C:\Documents and
Settings\nkatan\My Documents\pcm database\Testinfo.mdb"

oConnection.Name = "Microsoft Access"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "C:\Documents and Settings\nkatan\My
Documents\pcm database\Testinfo.mdb"
oConnection.UserID = "Admin"
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False

'If you have a password for this connection, please uncomment and add
your password below.
'oConnection.Password = "<put the password here>"

goPackage.Connections.Add oConnection
Set oConnection = Nothing

'------------- a new connection defined below.
'For security purposes, the password is never scripted

Set oConnection = goPackage.Connections.New("SQLOLEDB")

oConnection.ConnectionProperties("Integrated Security") = "SSPI"
oConnection.ConnectionProperties("Persist Security Info") = True
oConnection.ConnectionProperties("Initial Catalog") = "PCM-TestInfo"
oConnection.ConnectionProperties("Data Source") =
"RF2DZWDB07\SQL01,1434"
oConnection.ConnectionProperties("Application Name") = "DTS Designer"

oConnection.Name = "Microsoft OLE DB Provider for SQL Server"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "RF2DZWDB07\SQL01,1434"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "PCM-TestInfo"
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False

'If you have a password for this connection, please uncomment and add
your password below.
'oConnection.Password = "<put the password here>"

goPackage.Connections.Add oConnection
Set oConnection = Nothing

'-----------------------------------------------------------------------
----
' create package steps information
'-----------------------------------------------------------------------
----

Dim oStep as DTS.Step2
Dim oPrecConstraint as DTS.PrecedenceConstraint

'------------- a new step defined below

Set oStep = goPackage.Steps.New

oStep.Name = "DTSStep_DTSDataPumpTask_1"
oStep.Description = "Transform Data Task: undefined"
oStep.ExecutionStatus = 1
oStep.TaskName = "DTSTask_DTSDataPumpTask_1"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False

goPackage.Steps.Add oStep
Set oStep = Nothing

'-----------------------------------------------------------------------
----
' create package tasks information
'-----------------------------------------------------------------------
----

'------------- call Task_Sub1 for task DTSTask_DTSDataPumpTask_1
(Transform Data Task: undefined)
Call Task_Sub1( goPackage )

'-----------------------------------------------------------------------
----
' Save or execute package
'-----------------------------------------------------------------------
----

'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
goPackage.Uninitialize
'to save a package instead of executing it, comment out the executing
package line above and uncomment the saving package line
set goPackage = Nothing

set goPackageOld = Nothing

End Sub


'------------- define Task_Sub1 for task DTSTask_DTSDataPumpTask_1
(Transform Data Task: undefined)
Public Sub Task_Sub1(ByVal goPackage As Object)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask1 As DTS.DataPumpTask2
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
Set oCustomTask1 = oTask.CustomTask

oCustomTask1.Name = "DTSTask_DTSDataPumpTask_1"
oCustomTask1.Description = "Transform Data Task: undefined"
oCustomTask1.SourceConnectionID = 1
oCustomTask1.SourceSQLStatement = "SELECT [Alignment Results].*,
[Test Run].[Finish Time] AS " & vbCrLf
oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement &
"Expr1, 'D1C - 03' AS Expr2" & vbCrLf
oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement &
"FROM [Alignment Results] " & vbCrLf
oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement &
"INNER " & vbCrLf
oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement &
"JOIN" & vbCrLf
oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & "
[Test Run] ON [Alignment Results].RunNumber " & vbCrLf
oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & "=
[Test Run].RunNumber"
oCustomTask1.DestinationConnectionID = 2
oCustomTask1.DestinationObjectName = "[PCM-TestInfo].[dbo].[Alignment
Results]"
oCustomTask1.ProgressRowCount = 1000
oCustomTask1.MaximumErrorCount = 0
oCustomTask1.FetchBufferSize = 1
oCustomTask1.UseFastLoad = True
oCustomTask1.InsertCommitSize = 0
oCustomTask1.ExceptionFileColumnDelimiter = "|"
oCustomTask1.ExceptionFileRowDelimiter = vbCrLf
oCustomTask1.AllowIdentityInserts = False
oCustomTask1.FirstRow = 0
oCustomTask1.LastRow = 0
oCustomTask1.FastLoadOptions = 2
oCustomTask1.ExceptionFileOptions = 1
oCustomTask1.DataPumpOptions = 0

Call oCustomTask1_Trans_Sub1( oCustomTask1 )
'------- A Lookup is defined here

Set oLookup = oCustomTask1.Lookups.New("test_max")
oLookup.Name = "test_max"
oLookup.ConnectionID = 2
oLookup.Query = "SELECT MAX([Finish Time]) AS Expr1" & vbCrLf
oLookup.Query = oLookup.Query & "FROM [PCM-TestInfo].dbo.[Test
Run]"
oLookup.MaxCacheRows = 0

oCustomTask1.Lookups.Add oLookup
Set oLookup = Nothing


goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask1.Transformations.New("DTSPump.DataPump TransformScript")
oTransformation.Name = "DTSTransformation__1"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("Expr2" , 1)
oColumn.Name = "Expr2"
oColumn.Ordinal = 1
oColumn.Flags = 98
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Expr1" , 2)
oColumn.Name = "Expr1"
oColumn.Ordinal = 2
oColumn.Flags = 118
oColumn.Size = 0
oColumn.DataType = 7
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Tip Diameter Y" , 3)
oColumn.Name = "Tip Diameter Y"
oColumn.Ordinal = 3
oColumn.Flags = 118
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Tip Diameter X" , 4)
oColumn.Name = "Tip Diameter X"
oColumn.Ordinal = 4
oColumn.Flags = 118
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Final Touch Y Pos" ,
5)
oColumn.Name = "Final Touch Y Pos"
oColumn.Ordinal = 5
oColumn.Flags = 118
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("AlignODHorEnc" , 6)
oColumn.Name = "AlignODHorEnc"
oColumn.Ordinal = 6
oColumn.Flags = 118
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("AlignFTHorEnc" , 7)
oColumn.Name = "AlignFTHorEnc"
oColumn.Ordinal = 7
oColumn.Flags = 118
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Final Touch X Pos" ,
8)
oColumn.Name = "Final Touch X Pos"
oColumn.Ordinal = 8
oColumn.Flags = 118
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("First Touch Y Pos" ,
9)
oColumn.Name = "First Touch Y Pos"
oColumn.Ordinal = 9
oColumn.Flags = 118
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("First Touch X Pos" ,
10)
oColumn.Name = "First Touch X Pos"
oColumn.Ordinal = 10
oColumn.Flags = 118
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Probe ID" , 11)
oColumn.Name = "Probe ID"
oColumn.Ordinal = 11
oColumn.Flags = 118
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("RunNumber" , 12)
oColumn.Name = "RunNumber"
oColumn.Ordinal = 12
oColumn.Flags = 118
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Test_end_d ate" ,
1)
oColumn.Name = "Test_end_date"
oColumn.Ordinal = 1
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 135
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Tool_name" , 2)
oColumn.Name = "Tool_name"
oColumn.Ordinal = 2
oColumn.Flags = 120
oColumn.Size = 6
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Tip Diameter Y"
, 3)
oColumn.Name = "Tip Diameter Y"
oColumn.Ordinal = 3
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Tip Diameter X"
, 4)
oColumn.Name = "Tip Diameter X"
oColumn.Ordinal = 4
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Final Touch Y
Pos" , 5)
oColumn.Name = "Final Touch Y Pos"
oColumn.Ordinal = 5
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("AlignODHor Enc" ,
6)
oColumn.Name = "AlignODHorEnc"
oColumn.Ordinal = 6
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("AlignFTHor Enc" ,
7)
oColumn.Name = "AlignFTHorEnc"
oColumn.Ordinal = 7
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Final Touch X
Pos" , 8)
oColumn.Name = "Final Touch X Pos"
oColumn.Ordinal = 8
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("First Touch Y
Pos" , 9)
oColumn.Name = "First Touch Y Pos"
oColumn.Ordinal = 9
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("First Touch X
Pos" , 10)
oColumn.Name = "First Touch X Pos"
oColumn.Ordinal = 10
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Probe ID" , 11)
oColumn.Name = "Probe ID"
oColumn.Ordinal = 11
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("RunNumber" , 12)
oColumn.Name = "RunNumber"
oColumn.Ordinal = 12
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties

oTransProps("Text") =
"'************************************************ **********************
" & vbCrLf
oTransProps("Text") = oTransProps("Text") & "' Visual Basic
Transformation Script" & vbCrLf
oTransProps("Text") = oTransProps("Text") &
"'************************************************ **********************
**" & vbCrLf
oTransProps("Text") = oTransProps("Text") & "' Copy each source
column to the destination column" & vbCrLf
oTransProps("Text") = oTransProps("Text") & "Function Main()" & vbCrLf
oTransProps("Text") = oTransProps("Text") & "if (
DTSLookups(""test_max"").Execute(DTSDestination("" Test_end_date"")) <
DTSSource (""Expr1"") ) then" & vbCrLf
oTransProps("Text") = oTransProps("Text") & "Main =
DTSTransformStat_OK" & vbCrLf
oTransProps("Text") = oTransProps("Text") & "Else" & vbCrLf
oTransProps("Text") = oTransProps("Text") & " Main =
DTSTransformStat_SkipInsert" & vbCrLf
oTransProps("Text") = oTransProps("Text") & "End If" & vbCrLf
oTransProps("Text") = oTransProps("Text") &
" DTSDestination(""Test_end_date"") = DTSSource(""Expr1"")" & vbCrLf
oTransProps("Text") = oTransProps("Text") &
" DTSDestination(""Tool_name"") = DTSSource(""Expr2"")" & vbCrLf
oTransProps("Text") = oTransProps("Text") & " DTSDestination(""Tip
Diameter Y"") = DTSSource(""Tip Diameter Y"")" & vbCrLf
oTransProps("Text") = oTransProps("Text") & " DTSDestination(""Tip
Diameter X"") = DTSSource(""Tip Diameter X"")" & vbCrLf
oTransProps("Text") = oTransProps("Text") & " DTSDestination(""Final
Touch Y Pos"") = DTSSource(""Final Touch Y Pos"")" & vbCrLf
oTransProps("Text") = oTransProps("Text") &
" DTSDestination(""AlignODHorEnc"") = DTSSource(""AlignODHorEnc"")" &
vbCrLf
oTransProps("Text") = oTransProps("Text") &
" DTSDestination(""AlignFTHorEnc"") = DTSSource(""AlignFTHorEnc"")" &
vbCrLf
oTransProps("Text") = oTransProps("Text") & " DTSDestination(""Final
Touch X Pos"") = DTSSource(""Final Touch X Pos"")" & vbCrLf
oTransProps("Text") = oTransProps("Text") & " DTSDestination(""First
Touch Y Pos"") = DTSSource(""First Touch Y Pos"")" & vbCrLf
oTransProps("Text") = oTransProps("Text") & " DTSDestination(""First
Touch X Pos"") = DTSSource(""First Touch X Pos"")" & vbCrLf
oTransProps("Text") = oTransProps("Text") & " DTSDestination(""Probe
ID"") = DTSSource(""Probe ID"")" & vbCrLf
oTransProps("Text") = oTransProps("Text") &
" DTSDestination(""RunNumber"") = DTSSource(""RunNumber"")" & vbCrLf
oTransProps("Text") = oTransProps("Text") & " " & vbCrLf
oTransProps("Text") = oTransProps("Text") & "End Function"
oTransProps("Language") = "VBScript"
oTransProps("FunctionEntry") = "Main"

Set oTransProps = Nothing

oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!