I've converted a dts package saved as a visual basic file into a vb.net project and receive the following error when trying to execute:

'Copy Data from Sheet1$ to [database].[dbo].[table] Step failed.
Microsoft JET Database Engine Cannot start your application.
The workgroup information file is missing or opened exclusively by another user.'

I'm trying to import an excel file that doesn't have any security enabled, and isn't already opened to a sql server 2000 database. The Code used is listed below. I've used the vb6 to .net conversion wizard before for a dts package from an access database without problems. In the actual code I uncommented and set the oConnection.Password property. Any ideas?

Option Strict Off
Option Explicit On
Module Module1
'************************************************* ***************
'Microsoft SQL Server 2000
'Visual Basic file generated for DTS Package
'File Name: BOM_Import.bas
'Package Name: BOM_Import
'Package Description: BOM File import from excel to sql server
'Generated Date: 10/18/2004
'Generated Time: 2:14:22 PM
'************************************************* ***************

Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Public Sub Main()
goPackage = goPackageOld

goPackage.Name = "BOM_Import"
goPackage.Description = "BOM File import from excel to sql server"
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


Dim oConnProperty As DTS.OleDBProperty

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

Dim oConnection As DTS.Connection2

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

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

oConnection.ConnectionProperties.Item("Data Source").Value = "C:\BOM.xls"
oConnection.ConnectionProperties.Item("Extended Properties").Value = "Excel 8.0;HDR=YES;"

oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "C:\BOM.xls"
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>"

'UPGRADE_WARNING: Couldn't resolve default property of object oConnection. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
goPackage.Connections.Add(oConnection)
'UPGRADE_NOTE: Object oConnection may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oConnection = Nothing

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

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

oConnection.ConnectionProperties.Item("Persist Security Info").Value = True
oConnection.ConnectionProperties.Item("User ID").Value = "UserID"
oConnection.ConnectionProperties.Item("Initial Catalog").Value = "DatabaseName"
oConnection.ConnectionProperties.Item("Data Source").Value = "SqlServerName"
oConnection.ConnectionProperties.Item("Application Name").Value = "DTS Import/Export Wizard"

oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "SqlServerName"
oConnection.UserID = "UserID"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "DatabaseName"
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>"

'UPGRADE_WARNING: Couldn't resolve default property of object oConnection. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
goPackage.Connections.Add(oConnection)
'UPGRADE_NOTE: Object oConnection may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oConnection = Nothing

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

Dim oStep As DTS.Step2
Dim oPrecConstraint As DTS.PrecedenceConstraint

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

oStep = goPackage.Steps.New

oStep.Name = "Delete from Table [DatabaseName].[dbo].[TableName] Step"
oStep.Description = "Delete from Table [DatabaseName].[dbo].[TableName] Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Delete from Table [DatabaseName].[dbo].[TableName] Task"
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

'UPGRADE_WARNING: Couldn't resolve default property of object oStep. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
goPackage.Steps.Add(oStep)
'UPGRADE_NOTE: Object oStep may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oStep = Nothing

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

oStep = goPackage.Steps.New

oStep.Name = "Copy Data from Sheet1$ to [DatabaseName].[dbo].[TableName] Step"
oStep.Description = "Copy Data from Sheet1$ to [DatabaseName].[dbo].[TableName] Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copy Data from Sheet1$ to [DatabaseName].[dbo].[TableName] Task"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = True
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False

'UPGRADE_WARNING: Couldn't resolve default property of object oStep. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
goPackage.Steps.Add(oStep)
'UPGRADE_NOTE: Object oStep may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oStep = Nothing

'------------- a precedence constraint for steps defined below

oStep = goPackage.Steps.Item("Copy Data from Sheet1$ to [DatabaseName].[dbo].[TableName] Step")
oPrecConstraint = oStep.precedenceConstraints.New("Delete from Table [DatabaseName].[dbo].[TableName] Step")
oPrecConstraint.StepName = "Delete from Table [DatabaseName].[dbo].[TableName] Step"
oPrecConstraint.PrecedenceBasis = 1
oPrecConstraint.Value = 0

oStep.precedenceConstraints.Add(oPrecConstraint)
'UPGRADE_NOTE: Object oPrecConstraint may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oPrecConstraint = Nothing

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

'------------- call Task_Sub1 for task Delete from Table [DatabaseName].[dbo].[TableName] Task (Delete from Table [DatabaseName].[dbo].[TableName] Task)
Call Task_Sub1(goPackage)

'------------- call Task_Sub2 for task Copy Data from Sheet1$ to [DatabaseName].[dbo].[TableName] Task (Copy Data from Sheet1$ to [DatabaseName].[dbo].[TableName] Task)
Call Task_Sub2(goPackage)

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

'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute()
'UPGRADE_WARNING: Couldn't resolve default property of object goPackage.Steps.Item. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
tracePackageError(goPackage)
goPackage.Uninitialize()
'to save a package instead of executing it, comment out the executing package line above and uncomment the saving package line
'UPGRADE_NOTE: Object goPackage may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
goPackage = Nothing

'UPGRADE_NOTE: Object goPackageOld may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
goPackageOld = Nothing

End Sub


'-----------------------------------------------------------------------------
' error reporting using step.GetExecutionErrorInfo after execution
'-----------------------------------------------------------------------------
Public Sub tracePackageError(ByRef oPackage As DTS.Package)
Dim ErrorCode As Integer
Dim ErrorSource As String
Dim ErrorDescription As String
Dim ErrorHelpFile As String
Dim ErrorHelpContext As Integer
Dim ErrorIDofInterfaceWithError As String
Dim i As Short

For i = 1 To oPackage.Steps.Count
If oPackage.Steps.Item(i).ExecutionResult = DTS.DTSStepExecResult.DTSStepExecResult_Failure Then
oPackage.Steps.Item(i).GetExecutionErrorInfo(Error Code, ErrorSource, ErrorDescription, ErrorHelpFile, ErrorHelpContext, ErrorIDofInterfaceWithError)
MsgBox(oPackage.Steps.Item(i).Name & " failed" & vbCrLf & ErrorSource & vbCrLf & ErrorDescription)
End If
Next i

End Sub

'------------- define Task_Sub1 for task Delete from Table [DatabaseName].[dbo].[TableName] Task (Delete from Table [DatabaseName].[dbo].[TableName] Task)
Public Sub Task_Sub1(ByVal goPackage As DTS.Package2)

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

Dim oCustomTask1 As DTS.ExecuteSQLTask2
'UPGRADE_WARNING: Couldn't resolve default property of object goPackage.Tasks. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
oTask = goPackage.Tasks.New("DTSExecuteSQLTask")
oTask.Name = "Delete from Table [DatabaseName].[dbo].[TableName] Task"
oCustomTask1 = oTask.CustomTask

oCustomTask1.Name = "Delete from Table [DatabaseName].[dbo].[TableName] Task"
oCustomTask1.Description = "Delete from Table [DatabaseName].[dbo].[TableName] Task"
oCustomTask1.SQLStatement = "delete from [DatabaseName].[dbo].[TableName]"
oCustomTask1.ConnectionID = 2
oCustomTask1.CommandTimeout = 0
oCustomTask1.OutputAsRecordset = False

'UPGRADE_WARNING: Couldn't resolve default property of object goPackage.Tasks. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
goPackage.Tasks.Add(oTask)
'UPGRADE_NOTE: Object oCustomTask1 may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oCustomTask1 = Nothing
'UPGRADE_NOTE: Object oTask may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oTask = Nothing

End Sub

'------------- define Task_Sub2 for task Copy Data from Sheet1$ to [DatabaseName].[dbo].[TableName] Task (Copy Data from Sheet1$ to [DatabaseName].[dbo].[TableName] Task)
Public Sub Task_Sub2(ByVal goPackage As DTS.Package2)

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

Dim oCustomTask2 As DTS.DataPumpTask2
'UPGRADE_WARNING: Couldn't resolve default property of object goPackage.Tasks. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
oTask = goPackage.Tasks.New("DTSDataPumpTask")
oTask.Name = "Copy Data from Sheet1$ to [DatabaseName].[dbo].[TableName] Task"
oCustomTask2 = oTask.CustomTask

oCustomTask2.Name = "Copy Data from Sheet1$ to [DatabaseName].[dbo].[TableName] Task"
oCustomTask2.Description = "Copy Data from Sheet1$ to [DatabaseName].[dbo].[TableName] Task"
oCustomTask2.SourceConnectionID = 1
oCustomTask2.SourceSQLStatement = "select `Part Number`,`Description`,`Qty`,`Ref Des` from `Sheet1$`"
oCustomTask2.DestinationConnectionID = 2
oCustomTask2.DestinationObjectName = "[DatabaseName].[dbo].[TableName]"
oCustomTask2.ProgressRowCount = 1000
oCustomTask2.MaximumErrorCount = 0
oCustomTask2.FetchBufferSize = 1
oCustomTask2.UseFastLoad = True
oCustomTask2.InsertCommitSize = 0
oCustomTask2.ExceptionFileColumnDelimiter = "|"
oCustomTask2.ExceptionFileRowDelimiter = vbCrLf
oCustomTask2.AllowIdentityInserts = False
oCustomTask2.FirstRow = 0
oCustomTask2.LastRow = 0
oCustomTask2.FastLoadOptions = 2
oCustomTask2.ExceptionFileOptions = 1
oCustomTask2.DataPumpOptions = 0

Call oCustomTask2_Trans_Sub1(oCustomTask2)


'UPGRADE_WARNING: Couldn't resolve default property of object goPackage.Tasks. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
goPackage.Tasks.Add(oTask)
'UPGRADE_NOTE: Object oCustomTask2 may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oCustomTask2 = Nothing
'UPGRADE_NOTE: Object oTask may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oTask = Nothing

End Sub

Public Sub oCustomTask2_Trans_Sub1(ByVal oCustomTask2 As DTS.DataPumpTask2)

Dim oTransformation As DTS.Transformation2
Dim oTransProps As DTS.Properties
Dim oColumn As DTS.Column
'UPGRADE_WARNING: Couldn't resolve default property of object oCustomTask2.Transformations. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
oTransformation = oCustomTask2.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DirectCopyXform"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

oColumn = oTransformation.SourceColumns.New("Part Number", 1)
oColumn.Name = "Part Number"
oColumn.Ordinal = 1
oColumn.Flags = 102
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add(oColumn)
'UPGRADE_NOTE: Object oColumn may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

oColumn = oTransformation.SourceColumns.New("Description", 2)
oColumn.Name = "Description"
oColumn.Ordinal = 2
oColumn.Flags = 102
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add(oColumn)
'UPGRADE_NOTE: Object oColumn may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

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

oTransformation.SourceColumns.Add(oColumn)
'UPGRADE_NOTE: Object oColumn may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

oColumn = oTransformation.SourceColumns.New("Ref Des", 4)
oColumn.Name = "Ref Des"
oColumn.Ordinal = 4
oColumn.Flags = 102
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add(oColumn)
'UPGRADE_NOTE: Object oColumn may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

oColumn = oTransformation.DestinationColumns.New("PartNo", 1)
oColumn.Name = "PartNo"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 100
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add(oColumn)
'UPGRADE_NOTE: Object oColumn may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

oColumn = oTransformation.DestinationColumns.New("Descriptio n", 2)
oColumn.Name = "Description"
oColumn.Ordinal = 2
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add(oColumn)
'UPGRADE_NOTE: Object oColumn may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

oColumn = oTransformation.DestinationColumns.New("Qty", 3)
oColumn.Name = "Qty"
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)
'UPGRADE_NOTE: Object oColumn may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

oColumn = oTransformation.DestinationColumns.New("RefDes", 4)
oColumn.Name = "RefDes"
oColumn.Ordinal = 4
oColumn.Flags = 104
oColumn.Size = 500
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add(oColumn)
'UPGRADE_NOTE: Object oColumn may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

oTransProps = oTransformation.TransformServerProperties


'UPGRADE_NOTE: Object oTransProps may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oTransProps = Nothing

'UPGRADE_WARNING: Couldn't resolve default property of object oCustomTask2.Transformations. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
oCustomTask2.Transformations.Add(oTransformation)
'UPGRADE_NOTE: Object oTransformation may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oTransformation = Nothing

End Sub
End Module


---
Posted using Wimdows.net NntpNews Component -

Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.

Hello.

I have the exact same problem!!!

I have even executed the code generated by the enterprise manager and it
throws the same error.

Any ideas?

Thanks



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