DTS VB .Net application error

R

RSH

Hi,

I am deep into creating a VB .Net application that basically creates SQL
Server versions of our Access databases and copies over all the data. I
Extracted the VB DTS script and brought it into .Net and got the script to
work copying over a single database. Then I turned it into a much more
dynamic application and it works perfectly as long as I do one database at a
time. The issue comes in when i try to handle multiple databases in a
loop...i start getting NULL Exception errors specifically in the line:
goPackage.Tasks.Add(oTask)

It seems to only be a problem in looping through databases. A side note is
that if i comment out the code below (which only manages the data transfer)
the dtabases and tables are setup perfectly even when looping. The problem
only comes in when I am transferring the data in a loop of databases.

Please help...I am going nuts trying to figure out what is going on here.



thanks,

RSH


Function CreateStep2(ByVal DBIndex)

Dim objConn As OleDbConnection
Dim objDTTables As DataTable
Dim tableCount As Integer
Dim strTablename As String
Dim strAccessTablename As String
Dim strDBname As String
Dim objDTColumns As DataTable
Dim RowCount As Integer


' Step 1
'''' EXTRACT ACCESS SCHEMAS
********************************************************************
objConn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & aAccessDBs(DBIndex))

If objConn.State = ConnectionState.Closed Then
objConn.Open()
End If

objDTTables =
objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New
Object() {Nothing, Nothing, Nothing, Nothing})
'''' END EXTRACT ACCESS SCHEMAS



'''' BEGIN TABLE LOOP
********************************************************************
For tableCount = 0 To objDTTables.Rows.Count - 1

strTablename = objDTTables.Rows(tableCount).Item(2)
strDBname = aSQLDBNames(DBIndex)
strAccessTablename = strTablename


If strTablename.IndexOf("MSys") < 0 And
objDTTables.Rows(tableCount).Item(3) = "TABLE" And strTablename.Length > 0
And Not IsNothing(goPackage) Then

' Phase 1
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Dim oStep As DTS.Step2

oStep = goPackage.Steps.New
oStep.Name = "Copy Data from " & strAccessTablename & " to
[" & strDBname & "].[dbo].[" & strTablename & "] Step"
oStep.Description = "Copy Data from " & strAccessTablename &
" to [" & strDBname & "].[dbo].[" & strTablename & "] Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copy Data from " & strAccessTablename & "
to [" & strDBname & "].[dbo].[" & strTablename & "] 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
goPackage.Steps.Add(oStep)
oStep = Nothing


' Phase 2
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup
Dim oCustomTask2 As DTS.DataPumpTask2

oTask = goPackage.Tasks.New("DTSDataPumpTask")
oTask.Name = "Copy Data from " & strAccessTablename & " to
[" & strDBname & "].[dbo].[" & strTablename & "] Task"
oCustomTask2 = oTask.CustomTask

oCustomTask2.Name = "Copy Data from " & strAccessTablename &
" to [" & strDBname & "].[dbo].[" & strTablename & "] Task"
oCustomTask2.Description = "Copy Data from " &
strAccessTablename & " to [" & strDBname & "].[dbo].[" & strTablename & "]
Task"
oCustomTask2.SourceConnectionID = 1
oCustomTask2.SourceSQLStatement = "SELECT * FROM `" &
strAccessTablename & "`"
oCustomTask2.DestinationConnectionID = 2
oCustomTask2.DestinationObjectName = "[" & strDBname &
"].[dbo].[" & strTablename & "]"
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

Dim bResponse As Boolean = False
bResponse = oCustomTask2_Trans_Sub1(oCustomTask2,
strTablename, DBIndex)

If bResponse = True Then
goPackage.Tasks.Add(oTask)
Else
oCustomTask2 = Nothing
oTask = Nothing
End If
End If

Next

End Function








Function oCustomTask2_Trans_Sub1(ByVal oCustomTask2 As
DTS.DataPumpTask2, ByVal strTableName As String, ByVal DBIndex As Integer)

phase.Text = "Reading Data"
System.Windows.Forms.Application.DoEvents()

Dim oTransformation As DTS.Transformation2
Dim oTransProps As DTS.Properties
Dim oColumn As DTS.Column

Dim objConn As OleDbConnection
Dim objDTTables As DataTable
Dim tableCount As Integer
Dim strDBname As String
Dim objDTColumns As DataTable
Dim ColCount As Integer

Dim iTransCount As Integer = 0

objConn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & aAccessDBs(DBIndex))

If objConn.State = ConnectionState.Closed Then
objConn.Open()
End If

objDTColumns =
objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, New
Object() {Nothing, Nothing, strTableName})



For ColCount = 0 To objDTColumns.Rows.Count - 1

Dim strCOLUMN_NAME =
objDTColumns.Rows(ColCount).Item("COLUMN_NAME").ToString
Dim strDATA_TYPE =
objDTColumns.Rows(ColCount).Item("DATA_TYPE").ToString

If strCOLUMN_NAME.IndexOf("s_") < 0 And strDATA_TYPE <> "" And
strCOLUMN_NAME.IndexOf("CrLf") And Not IsNothing(goPackage) Then

'''' Extract Columns of interest values

If iTransCount = 0 Then
oTransformation =
oCustomTask2.Transformations.New("DTS.DataPumpTransformCopy")
oTransformation.Name = "DirectCopyXform" '& strTableName
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 1
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

status.Text = "Writing data to: " & strTableName
System.Windows.Forms.Application.DoEvents()
End If


iTransCount = iTransCount + 1


Dim strORDINAL_POSITION =
objDTColumns.Rows(ColCount).Item("ORDINAL_POSITION").ToString
Dim strCOLUMN_HASDEFAULT =
objDTColumns.Rows(ColCount).Item("COLUMN_HASDEFAULT").ToString
Dim strCOLUMN_DEFAULT =
objDTColumns.Rows(ColCount).Item("COLUMN_DEFAULT").ToString
Dim strNUMERIC_PRECISION =
objDTColumns.Rows(ColCount).Item("NUMERIC_PRECISION").ToString
Dim strDESCRIPTION =
objDTColumns.Rows(ColCount).Item("DESCRIPTION").ToString

Dim strCHARACTER_MAXIMUM_LENGTH =
objDTColumns.Rows(ColCount).Item("CHARACTER_MAXIMUM_LENGTH").ToString
Dim strIS_NULLABLE =
objDTColumns.Rows(ColCount).Item("IS_NULLABLE").ToString
Dim strConvertedDatatype As String
Dim bUniqueIdentifier As Boolean = False



'''' Make sure columns are not SYSTEM COLUMNS and they have
a data type

If strCOLUMN_NAME.IndexOf("s_") < 0 And strDATA_TYPE <> ""
And strCOLUMN_NAME.IndexOf("CrLf") Then


'''' SOURCE
oColumn =
oTransformation.SourceColumns.New(strCOLUMN_NAME, strORDINAL_POSITION)
oColumn.Name = strCOLUMN_NAME

'Dim oSrcColumnName As String = oColumn.Name

oColumn.Ordinal = CType(strORDINAL_POSITION, String)
'oColumn.Flags = 90

If strCHARACTER_MAXIMUM_LENGTH <> String.Empty Then
oColumn.Size = CType(strCHARACTER_MAXIMUM_LENGTH,
String)
End If

If strDATA_TYPE <> String.Empty Then
oColumn.DataType = CType(strDATA_TYPE, String)
End If

If strNUMERIC_PRECISION <> String.Empty Then
oColumn.Precision = CType(strNUMERIC_PRECISION,
Integer)
End If

If strIS_NULLABLE <> String.Empty Then
oColumn.Nullable = strIS_NULLABLE
End If

oTransformation.SourceColumns.Add(oColumn)
oColumn = Nothing



'''' DESTINATION
oColumn =
oTransformation.DestinationColumns.New(strCOLUMN_NAME, strORDINAL_POSITION)
oColumn.Name = strCOLUMN_NAME

Dim oDstColumnName As String = oColumn.Name

If strCHARACTER_MAXIMUM_LENGTH <> String.Empty Then
oColumn.Size = CType(strCHARACTER_MAXIMUM_LENGTH,
String)
End If

If strDATA_TYPE <> String.Empty Then
oColumn.DataType = CType(strDATA_TYPE, String)
End If

If strNUMERIC_PRECISION <> String.Empty Then
oColumn.Precision = CType(strNUMERIC_PRECISION,
Integer)
End If

If strIS_NULLABLE <> String.Empty Then
oColumn.Nullable = strIS_NULLABLE
End If

oTransformation.DestinationColumns.Add(oColumn)
oColumn = Nothing

End If
End If
Next

If iTransCount > 0 Then
oTransProps = oTransformation.TransformServerProperties
oTransProps = Nothing
oCustomTask2.Transformations.Add(oTransformation)
oTransformation = Nothing

Return (True)
End If

Return (False)


End Function
 
M

Morgan

Dont know if this will help or not, but did you declare the DTS objects With
Events?
If I remember correctly, people always forgot to use the With Events
modifier in VB6 and had problems with DTS objects.


RSH said:
Hi,

I am deep into creating a VB .Net application that basically creates SQL
Server versions of our Access databases and copies over all the data. I
Extracted the VB DTS script and brought it into .Net and got the script
to
work copying over a single database. Then I turned it into a much more
dynamic application and it works perfectly as long as I do one database at
a
time. The issue comes in when i try to handle multiple databases in a
loop...i start getting NULL Exception errors specifically in the line:
goPackage.Tasks.Add(oTask)

It seems to only be a problem in looping through databases. A side note
is
that if i comment out the code below (which only manages the data
transfer)
the dtabases and tables are setup perfectly even when looping. The
problem
only comes in when I am transferring the data in a loop of databases.

Please help...I am going nuts trying to figure out what is going on here.



thanks,

RSH


Function CreateStep2(ByVal DBIndex)

Dim objConn As OleDbConnection
Dim objDTTables As DataTable
Dim tableCount As Integer
Dim strTablename As String
Dim strAccessTablename As String
Dim strDBname As String
Dim objDTColumns As DataTable
Dim RowCount As Integer


' Step 1
'''' EXTRACT ACCESS SCHEMAS
********************************************************************
objConn = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & aAccessDBs(DBIndex))

If objConn.State = ConnectionState.Closed Then
objConn.Open()
End If

objDTTables =
objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New
Object() {Nothing, Nothing, Nothing, Nothing})
'''' END EXTRACT ACCESS SCHEMAS



'''' BEGIN TABLE LOOP
********************************************************************
For tableCount = 0 To objDTTables.Rows.Count - 1

strTablename = objDTTables.Rows(tableCount).Item(2)
strDBname = aSQLDBNames(DBIndex)
strAccessTablename = strTablename


If strTablename.IndexOf("MSys") < 0 And
objDTTables.Rows(tableCount).Item(3) = "TABLE" And strTablename.Length > 0
And Not IsNothing(goPackage) Then

' Phase 1
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Dim oStep As DTS.Step2

oStep = goPackage.Steps.New
oStep.Name = "Copy Data from " & strAccessTablename & " to
[" & strDBname & "].[dbo].[" & strTablename & "] Step"
oStep.Description = "Copy Data from " & strAccessTablename
&
" to [" & strDBname & "].[dbo].[" & strTablename & "] Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copy Data from " & strAccessTablename & "
to [" & strDBname & "].[dbo].[" & strTablename & "] 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
goPackage.Steps.Add(oStep)
oStep = Nothing


' Phase 2
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup
Dim oCustomTask2 As DTS.DataPumpTask2

oTask = goPackage.Tasks.New("DTSDataPumpTask")
oTask.Name = "Copy Data from " & strAccessTablename & " to
[" & strDBname & "].[dbo].[" & strTablename & "] Task"
oCustomTask2 = oTask.CustomTask

oCustomTask2.Name = "Copy Data from " & strAccessTablename
&
" to [" & strDBname & "].[dbo].[" & strTablename & "] Task"
oCustomTask2.Description = "Copy Data from " &
strAccessTablename & " to [" & strDBname & "].[dbo].[" & strTablename & "]
Task"
oCustomTask2.SourceConnectionID = 1
oCustomTask2.SourceSQLStatement = "SELECT * FROM `" &
strAccessTablename & "`"
oCustomTask2.DestinationConnectionID = 2
oCustomTask2.DestinationObjectName = "[" & strDBname &
"].[dbo].[" & strTablename & "]"
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

Dim bResponse As Boolean = False
bResponse = oCustomTask2_Trans_Sub1(oCustomTask2,
strTablename, DBIndex)

If bResponse = True Then
goPackage.Tasks.Add(oTask)
Else
oCustomTask2 = Nothing
oTask = Nothing
End If
End If

Next

End Function








Function oCustomTask2_Trans_Sub1(ByVal oCustomTask2 As
DTS.DataPumpTask2, ByVal strTableName As String, ByVal DBIndex As Integer)

phase.Text = "Reading Data"
System.Windows.Forms.Application.DoEvents()

Dim oTransformation As DTS.Transformation2
Dim oTransProps As DTS.Properties
Dim oColumn As DTS.Column

Dim objConn As OleDbConnection
Dim objDTTables As DataTable
Dim tableCount As Integer
Dim strDBname As String
Dim objDTColumns As DataTable
Dim ColCount As Integer

Dim iTransCount As Integer = 0

objConn = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & aAccessDBs(DBIndex))

If objConn.State = ConnectionState.Closed Then
objConn.Open()
End If

objDTColumns =
objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, New
Object() {Nothing, Nothing, strTableName})



For ColCount = 0 To objDTColumns.Rows.Count - 1

Dim strCOLUMN_NAME =
objDTColumns.Rows(ColCount).Item("COLUMN_NAME").ToString
Dim strDATA_TYPE =
objDTColumns.Rows(ColCount).Item("DATA_TYPE").ToString

If strCOLUMN_NAME.IndexOf("s_") < 0 And strDATA_TYPE <> "" And
strCOLUMN_NAME.IndexOf("CrLf") And Not IsNothing(goPackage) Then

'''' Extract Columns of interest values

If iTransCount = 0 Then
oTransformation =
oCustomTask2.Transformations.New("DTS.DataPumpTransformCopy")
oTransformation.Name = "DirectCopyXform" '&
strTableName
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 1
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

status.Text = "Writing data to: " & strTableName
System.Windows.Forms.Application.DoEvents()
End If


iTransCount = iTransCount + 1


Dim strORDINAL_POSITION =
objDTColumns.Rows(ColCount).Item("ORDINAL_POSITION").ToString
Dim strCOLUMN_HASDEFAULT =
objDTColumns.Rows(ColCount).Item("COLUMN_HASDEFAULT").ToString
Dim strCOLUMN_DEFAULT =
objDTColumns.Rows(ColCount).Item("COLUMN_DEFAULT").ToString
Dim strNUMERIC_PRECISION =
objDTColumns.Rows(ColCount).Item("NUMERIC_PRECISION").ToString
Dim strDESCRIPTION =
objDTColumns.Rows(ColCount).Item("DESCRIPTION").ToString

Dim strCHARACTER_MAXIMUM_LENGTH =
objDTColumns.Rows(ColCount).Item("CHARACTER_MAXIMUM_LENGTH").ToString
Dim strIS_NULLABLE =
objDTColumns.Rows(ColCount).Item("IS_NULLABLE").ToString
Dim strConvertedDatatype As String
Dim bUniqueIdentifier As Boolean = False



'''' Make sure columns are not SYSTEM COLUMNS and they have
a data type

If strCOLUMN_NAME.IndexOf("s_") < 0 And strDATA_TYPE <> ""
And strCOLUMN_NAME.IndexOf("CrLf") Then


'''' SOURCE
oColumn =
oTransformation.SourceColumns.New(strCOLUMN_NAME, strORDINAL_POSITION)
oColumn.Name = strCOLUMN_NAME

'Dim oSrcColumnName As String = oColumn.Name

oColumn.Ordinal = CType(strORDINAL_POSITION, String)
'oColumn.Flags = 90

If strCHARACTER_MAXIMUM_LENGTH <> String.Empty Then
oColumn.Size = CType(strCHARACTER_MAXIMUM_LENGTH,
String)
End If

If strDATA_TYPE <> String.Empty Then
oColumn.DataType = CType(strDATA_TYPE, String)
End If

If strNUMERIC_PRECISION <> String.Empty Then
oColumn.Precision = CType(strNUMERIC_PRECISION,
Integer)
End If

If strIS_NULLABLE <> String.Empty Then
oColumn.Nullable = strIS_NULLABLE
End If

oTransformation.SourceColumns.Add(oColumn)
oColumn = Nothing



'''' DESTINATION
oColumn =
oTransformation.DestinationColumns.New(strCOLUMN_NAME,
strORDINAL_POSITION)
oColumn.Name = strCOLUMN_NAME

Dim oDstColumnName As String = oColumn.Name

If strCHARACTER_MAXIMUM_LENGTH <> String.Empty Then
oColumn.Size = CType(strCHARACTER_MAXIMUM_LENGTH,
String)
End If

If strDATA_TYPE <> String.Empty Then
oColumn.DataType = CType(strDATA_TYPE, String)
End If

If strNUMERIC_PRECISION <> String.Empty Then
oColumn.Precision = CType(strNUMERIC_PRECISION,
Integer)
End If

If strIS_NULLABLE <> String.Empty Then
oColumn.Nullable = strIS_NULLABLE
End If

oTransformation.DestinationColumns.Add(oColumn)
oColumn = Nothing

End If
End If
Next

If iTransCount > 0 Then
oTransProps = oTransformation.TransformServerProperties
oTransProps = Nothing
oCustomTask2.Transformations.Add(oTransformation)
oTransformation = Nothing

Return (True)
End If

Return (False)


End Function
Click to expand...
 
Top