I am at wits end here. Package is set up to truncate 2 tables and load
data from a Goldmine 5.0 .dbf database and copy that data to a sql 7
table. If I execute the package in SQL EM it runs perfectly. If I use
dtsrun it works perfectly. If I use the dts pkg lib from within vb and
execute it fails every time. "No value given for one or more required
parameters."

The preview works as expected on the transformations every time as
well. I am only selecting 3 columns and transforming them. Anyone have
any thoughts at all? I am curently using the dBase 5.0 driver. I have
tried it with a dsn as well as using the dts "New Connection" object
and setting that up as well. It doesnt seem to make a difference. I
even tried the dBase IV driver. It all works in SQL EM and with
dtsrun. I can even use Shell to exec the same dtsrun command and that
works from within VB. But I need more control and some error handling
capabilities should an error arise.

What is the code you are using to Execute your package through VB ?

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Josh Thompson" <josht01 (AT) yahoo (DOT) com> wrote

Funny thing is I changed the Data Pump task in DTS from "SELECT Col1,
Col2, COl3 FROM ContSupp" which worked fine in DTS and failed with the
code below from VB. On occassion it would return the error "Not Enough
Parameters Supplied. Expected 3" If I added another column it expected
4. So I changed it to a select * and wouldn't ya know the damn thing
works just fine now. Slower than heck but I am stumped as to what the
difference is. Perhaps VB is defaulting something that dts or dts run is
not? I don't know.

At any rate, here is the code I was using. Including the Shell command
that executed the dtsrun command, which works perfectly except I lose
all granular control and it lacks any real-time error handling or status
information.

Private Function UpdateFromGoldmine() As Boolean
On Error GoTo errUpdating

Dim lpErrorCode As Long
Dim strErrDesc As String
Dim intResponse As Integer
Dim intStatus As Integer
Dim i As Integer

'---THIS WORKS PERFECTLY
'Shell "dtsrun /S*** /U*** /P*** /NGoldMineContactsImport"

'---THIS FAILS
Set oPkg = New DTS.Package

blnSuccess = True

With oPkg
.FailOnError = True
.PackagePriorityClass = DTSPriorityClass_High
.LoadFromSQLServer SQL_SERVER, UID, PWD,
DTSSQLStgFlag_UseTrustedConnection, , , ,
"TestGoldmineImport" ''DTS_PKG_NAME

For i = 1 To oPkg.Steps.Count
oPkg.Steps(i).ExecuteInMainThread = True
Next i

.Execute
End With

For i = 1 To oPkg.Steps.Count
If oPkg.Steps(i).ExecutionResult =
DTSStepExecResult_Failure Then
oPkg.Steps(i).GetExecutionErrorInfo
lpErrorCode, strErrDesc
intStatus = False
Else
txtWarning.Text = txtWarning.Text & vbCrLf &
oPkg.Steps(i).TaskName & ": " & oPkg.Steps(i).Description & "
Succeeded!"
End If

If intStatus = False Then
txtWarning.Text = txtWarning.Text & vbCrLf &
oPkg.Steps(i).Description & " in the " & _
oPkg.Description & " failed." '
Update the warning label

txtWarning.Visible = True
blnSuccess = False
End If
Next i

UpdateFromGoldmine = blnSuccess

oPkg.UnInitialize

Set oPkg = Nothing

Exit Function


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