DTS VB .Net application error

[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(ByV al DBIndex)

Dim objConn As OleDbConnection
Dim objDTTables As DataTable
Dim tableCount As Integer
Dim strTablename As String
Dim strAccessTablen ame As String
Dim strDBname As String
Dim objDTColumns As DataTable
Dim RowCount As Integer
' Step 1
'''' EXTRACT ACCESS SCHEMAS
*************** *************** *************** *************** ********
objConn = New OleDbConnection ("Provider=Micr osoft.Jet.OLEDB .4.0;Data
Source=" & aAccessDBs(DBIn dex))

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

objDTTables =
objConn.GetOleD bSchemaTable(Sy stem.Data.OleDb .OleDbSchemaGui d.Tables, New
Object() {Nothing, Nothing, Nothing, Nothing})
'''' END EXTRACT ACCESS SCHEMAS

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

strTablename = objDTTables.Row s(tableCount).I tem(2)
strDBname = aSQLDBNames(DBI ndex)
strAccessTablen ame = strTablename
If strTablename.In dexOf("MSys") < 0 And
objDTTables.Row s(tableCount).I tem(3) = "TABLE" And strTablename.Le ngth > 0
And Not IsNothing(goPac kage) Then

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

oStep = goPackage.Steps .New
oStep.Name = "Copy Data from " & strAccessTablen ame & " to
[" & strDBname & "].[dbo].[" & strTablename & "] Step"
oStep.Descripti on = "Copy Data from " & strAccessTablen ame &
" to [" & strDBname & "].[dbo].[" & strTablename & "] Step"
oStep.Execution Status = 1
oStep.TaskName = "Copy Data from " & strAccessTablen ame & "
to [" & strDBname & "].[dbo].[" & strTablename & "] Task"
oStep.CommitSuc cess = False
oStep.RollbackF ailure = False
oStep.ScriptLan guage = "VBScript"
oStep.AddGlobal Variables = True
oStep.RelativeP riority = 3
oStep.CloseConn ection = False
oStep.ExecuteIn MainThread = True
oStep.IsPackage DSORowset = False
oStep.JoinTrans actionIfPresent = False
oStep.DisableSt ep = False
oStep.FailPacka geOnError = False
goPackage.Steps .Add(oStep)
oStep = Nothing
' Phase 2
+++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++
Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup
Dim oCustomTask2 As DTS.DataPumpTas k2

oTask = goPackage.Tasks .New("DTSDataPu mpTask")
oTask.Name = "Copy Data from " & strAccessTablen ame & " to
[" & strDBname & "].[dbo].[" & strTablename & "] Task"
oCustomTask2 = oTask.CustomTas k

oCustomTask2.Na me = "Copy Data from " & strAccessTablen ame &
" to [" & strDBname & "].[dbo].[" & strTablename & "] Task"
oCustomTask2.De scription = "Copy Data from " &
strAccessTablen ame & " to [" & strDBname & "].[dbo].[" & strTablename & "]
Task"
oCustomTask2.So urceConnectionI D = 1
oCustomTask2.So urceSQLStatemen t = "SELECT * FROM `" &
strAccessTablen ame & "`"
oCustomTask2.De stinationConnec tionID = 2
oCustomTask2.De stinationObject Name = "[" & strDBname &
"].[dbo].[" & strTablename & "]"
oCustomTask2.Pr ogressRowCount = 1000
oCustomTask2.Ma ximumErrorCount = 0
oCustomTask2.Fe tchBufferSize = 1
oCustomTask2.Us eFastLoad = True
oCustomTask2.In sertCommitSize = 0
oCustomTask2.Ex ceptionFileColu mnDelimiter = "|"
oCustomTask2.Ex ceptionFileRowD elimiter = vbCrLf
oCustomTask2.Al lowIdentityInse rts = False
oCustomTask2.Fi rstRow = 0
oCustomTask2.La stRow = 0
oCustomTask2.Fa stLoadOptions = 2
oCustomTask2.Ex ceptionFileOpti ons = 1
oCustomTask2.Da taPumpOptions = 0

Dim bResponse As Boolean = False
bResponse = oCustomTask2_Tr ans_Sub1(oCusto mTask2,
strTablename, DBIndex)

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

Next

End Function


Function oCustomTask2_Tr ans_Sub1(ByVal oCustomTask2 As
DTS.DataPumpTas k2, ByVal strTableName As String, ByVal DBIndex As Integer)

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

Dim oTransformation As DTS.Transformat ion2
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=Micr osoft.Jet.OLEDB .4.0;Data
Source=" & aAccessDBs(DBIn dex))

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

objDTColumns =
objConn.GetOleD bSchemaTable(Sy stem.Data.OleDb .OleDbSchemaGui d.Columns, New
Object() {Nothing, Nothing, strTableName})

For ColCount = 0 To objDTColumns.Ro ws.Count - 1

Dim strCOLUMN_NAME =
objDTColumns.Ro ws(ColCount).It em("COLUMN_NAME ").ToString
Dim strDATA_TYPE =
objDTColumns.Ro ws(ColCount).It em("DATA_TYPE") .ToString

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

'''' Extract Columns of interest values

If iTransCount = 0 Then
oTransformation =
oCustomTask2.Tr ansformations.N ew("DTS.DataPum pTransformCopy" )
oTransformation .Name = "DirectCopyXfor m" '& strTableName
oTransformation .TransformFlags = 63
oTransformation .ForceSourceBlo bsBuffered = 1
oTransformation .ForceBlobsInMe mory = False
oTransformation .InMemoryBlobSi ze = 1048576
oTransformation .TransformPhase s = 4

status.Text = "Writing data to: " & strTableName
System.Windows. Forms.Applicati on.DoEvents()
End If
iTransCount = iTransCount + 1
Dim strORDINAL_POSI TION =
objDTColumns.Ro ws(ColCount).It em("ORDINAL_POS ITION").ToStrin g
Dim strCOLUMN_HASDE FAULT =
objDTColumns.Ro ws(ColCount).It em("COLUMN_HASD EFAULT").ToStri ng
Dim strCOLUMN_DEFAU LT =
objDTColumns.Ro ws(ColCount).It em("COLUMN_DEFA ULT").ToString
Dim strNUMERIC_PREC ISION =
objDTColumns.Ro ws(ColCount).It em("NUMERIC_PRE CISION").ToStri ng
Dim strDESCRIPTION =
objDTColumns.Ro ws(ColCount).It em("DESCRIPTION ").ToString

Dim strCHARACTER_MA XIMUM_LENGTH =
objDTColumns.Ro ws(ColCount).It em("CHARACTER_M AXIMUM_LENGTH") .ToString
Dim strIS_NULLABLE =
objDTColumns.Ro ws(ColCount).It em("IS_NULLABLE ").ToString
Dim strConvertedDat atype As String
Dim bUniqueIdentifi er 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_N AME, strORDINAL_POSI TION)
oColumn.Name = strCOLUMN_NAME

'Dim oSrcColumnName As String = oColumn.Name

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

If strCHARACTER_MA XIMUM_LENGTH <> String.Empty Then
oColumn.Size = CType(strCHARAC TER_MAXIMUM_LEN GTH,
String)
End If

If strDATA_TYPE <> String.Empty Then
oColumn.DataTyp e = CType(strDATA_T YPE, String)
End If

If strNUMERIC_PREC ISION <> String.Empty Then
oColumn.Precisi on = CType(strNUMERI C_PRECISION,
Integer)
End If

If strIS_NULLABLE <> String.Empty Then
oColumn.Nullabl e = strIS_NULLABLE
End If

oTransformation .SourceColumns. Add(oColumn)
oColumn = Nothing

'''' DESTINATION
oColumn =
oTransformation .DestinationCol umns.New(strCOL UMN_NAME, strORDINAL_POSI TION)
oColumn.Name = strCOLUMN_NAME

Dim oDstColumnName As String = oColumn.Name

If strCHARACTER_MA XIMUM_LENGTH <> String.Empty Then
oColumn.Size = CType(strCHARAC TER_MAXIMUM_LEN GTH,
String)
End If

If strDATA_TYPE <> String.Empty Then
oColumn.DataTyp e = CType(strDATA_T YPE, String)
End If

If strNUMERIC_PREC ISION <> String.Empty Then
oColumn.Precisi on = CType(strNUMERI C_PRECISION,
Integer)
End If

If strIS_NULLABLE <> String.Empty Then
oColumn.Nullabl e = strIS_NULLABLE
End If

oTransformation .DestinationCol umns.Add(oColum n)
oColumn = Nothing

End If
End If
Next

If iTransCount > 0 Then
oTransProps = oTransformation .TransformServe rProperties
oTransProps = Nothing
oCustomTask2.Tr ansformations.A dd(oTransformat ion)
oTransformation = Nothing

Return (True)
End If

Return (False)
End Function

[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" <wa************ *@yahoo.com> wrote in message
news:%2******** ********@TK2MSF TNGP14.phx.gbl. ..

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(ByV al DBIndex)

Dim objConn As OleDbConnection
Dim objDTTables As DataTable
Dim tableCount As Integer
Dim strTablename As String
Dim strAccessTablen ame As String
Dim strDBname As String
Dim objDTColumns As DataTable
Dim RowCount As Integer
' Step 1
'''' EXTRACT ACCESS SCHEMAS
*************** *************** *************** *************** ********
objConn = New
OleDbConnection ("Provider=Micr osoft.Jet.OLEDB .4.0;Data
Source=" & aAccessDBs(DBIn dex))

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

objDTTables =
objConn.GetOleD bSchemaTable(Sy stem.Data.OleDb .OleDbSchemaGui d.Tables, New
Object() {Nothing, Nothing, Nothing, Nothing})
'''' END EXTRACT ACCESS SCHEMAS

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

strTablename = objDTTables.Row s(tableCount).I tem(2)
strDBname = aSQLDBNames(DBI ndex)
strAccessTablen ame = strTablename
If strTablename.In dexOf("MSys") < 0 And
objDTTables.Row s(tableCount).I tem(3) = "TABLE" And strTablename.Le ngth > 0
And Not IsNothing(goPac kage) Then

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

oStep = goPackage.Steps .New
oStep.Name = "Copy Data from " & strAccessTablen ame & " to
[" & strDBname & "].[dbo].[" & strTablename & "] Step"
oStep.Descripti on = "Copy Data from " & strAccessTablen ame
&
" to [" & strDBname & "].[dbo].[" & strTablename & "] Step"
oStep.Execution Status = 1
oStep.TaskName = "Copy Data from " & strAccessTablen ame & "
to [" & strDBname & "].[dbo].[" & strTablename & "] Task"
oStep.CommitSuc cess = False
oStep.RollbackF ailure = False
oStep.ScriptLan guage = "VBScript"
oStep.AddGlobal Variables = True
oStep.RelativeP riority = 3
oStep.CloseConn ection = False
oStep.ExecuteIn MainThread = True
oStep.IsPackage DSORowset = False
oStep.JoinTrans actionIfPresent = False
oStep.DisableSt ep = False
oStep.FailPacka geOnError = False
goPackage.Steps .Add(oStep)
oStep = Nothing
' Phase 2
+++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++
Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup
Dim oCustomTask2 As DTS.DataPumpTas k2

oTask = goPackage.Tasks .New("DTSDataPu mpTask")
oTask.Name = "Copy Data from " & strAccessTablen ame & " to
[" & strDBname & "].[dbo].[" & strTablename & "] Task"
oCustomTask2 = oTask.CustomTas k

oCustomTask2.Na me = "Copy Data from " & strAccessTablen ame
&
" to [" & strDBname & "].[dbo].[" & strTablename & "] Task"
oCustomTask2.De scription = "Copy Data from " &
strAccessTablen ame & " to [" & strDBname & "].[dbo].[" & strTablename & "]
Task"
oCustomTask2.So urceConnectionI D = 1
oCustomTask2.So urceSQLStatemen t = "SELECT * FROM `" &
strAccessTablen ame & "`"
oCustomTask2.De stinationConnec tionID = 2
oCustomTask2.De stinationObject Name = "[" & strDBname &
"].[dbo].[" & strTablename & "]"
oCustomTask2.Pr ogressRowCount = 1000
oCustomTask2.Ma ximumErrorCount = 0
oCustomTask2.Fe tchBufferSize = 1
oCustomTask2.Us eFastLoad = True
oCustomTask2.In sertCommitSize = 0
oCustomTask2.Ex ceptionFileColu mnDelimiter = "|"
oCustomTask2.Ex ceptionFileRowD elimiter = vbCrLf
oCustomTask2.Al lowIdentityInse rts = False
oCustomTask2.Fi rstRow = 0
oCustomTask2.La stRow = 0
oCustomTask2.Fa stLoadOptions = 2
oCustomTask2.Ex ceptionFileOpti ons = 1
oCustomTask2.Da taPumpOptions = 0

Dim bResponse As Boolean = False
bResponse = oCustomTask2_Tr ans_Sub1(oCusto mTask2,
strTablename, DBIndex)

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

Next

End Function


Function oCustomTask2_Tr ans_Sub1(ByVal oCustomTask2 As
DTS.DataPumpTas k2, ByVal strTableName As String, ByVal DBIndex As Integer)

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

Dim oTransformation As DTS.Transformat ion2
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=Micr osoft.Jet.OLEDB .4.0;Data
Source=" & aAccessDBs(DBIn dex))

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

objDTColumns =
objConn.GetOleD bSchemaTable(Sy stem.Data.OleDb .OleDbSchemaGui d.Columns, New
Object() {Nothing, Nothing, strTableName})

For ColCount = 0 To objDTColumns.Ro ws.Count - 1

Dim strCOLUMN_NAME =
objDTColumns.Ro ws(ColCount).It em("COLUMN_NAME ").ToString
Dim strDATA_TYPE =
objDTColumns.Ro ws(ColCount).It em("DATA_TYPE") .ToString

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

'''' Extract Columns of interest values

If iTransCount = 0 Then
oTransformation =
oCustomTask2.Tr ansformations.N ew("DTS.DataPum pTransformCopy" )
oTransformation .Name = "DirectCopyXfor m" '&
strTableName
oTransformation .TransformFlags = 63
oTransformation .ForceSourceBlo bsBuffered = 1
oTransformation .ForceBlobsInMe mory = False
oTransformation .InMemoryBlobSi ze = 1048576
oTransformation .TransformPhase s = 4

status.Text = "Writing data to: " & strTableName
System.Windows. Forms.Applicati on.DoEvents()
End If
iTransCount = iTransCount + 1
Dim strORDINAL_POSI TION =
objDTColumns.Ro ws(ColCount).It em("ORDINAL_POS ITION").ToStrin g
Dim strCOLUMN_HASDE FAULT =
objDTColumns.Ro ws(ColCount).It em("COLUMN_HASD EFAULT").ToStri ng
Dim strCOLUMN_DEFAU LT =
objDTColumns.Ro ws(ColCount).It em("COLUMN_DEFA ULT").ToString
Dim strNUMERIC_PREC ISION =
objDTColumns.Ro ws(ColCount).It em("NUMERIC_PRE CISION").ToStri ng
Dim strDESCRIPTION =
objDTColumns.Ro ws(ColCount).It em("DESCRIPTION ").ToString

Dim strCHARACTER_MA XIMUM_LENGTH =
objDTColumns.Ro ws(ColCount).It em("CHARACTER_M AXIMUM_LENGTH") .ToString
Dim strIS_NULLABLE =
objDTColumns.Ro ws(ColCount).It em("IS_NULLABLE ").ToString
Dim strConvertedDat atype As String
Dim bUniqueIdentifi er 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_N AME, strORDINAL_POSI TION)
oColumn.Name = strCOLUMN_NAME

'Dim oSrcColumnName As String = oColumn.Name

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

If strCHARACTER_MA XIMUM_LENGTH <> String.Empty Then
oColumn.Size = CType(strCHARAC TER_MAXIMUM_LEN GTH,
String)
End If

If strDATA_TYPE <> String.Empty Then
oColumn.DataTyp e = CType(strDATA_T YPE, String)
End If

If strNUMERIC_PREC ISION <> String.Empty Then
oColumn.Precisi on = CType(strNUMERI C_PRECISION,
Integer)
End If

If strIS_NULLABLE <> String.Empty Then
oColumn.Nullabl e = strIS_NULLABLE
End If

oTransformation .SourceColumns. Add(oColumn)
oColumn = Nothing

'''' DESTINATION
oColumn =
oTransformation .DestinationCol umns.New(strCOL UMN_NAME,
strORDINAL_POSI TION)
oColumn.Name = strCOLUMN_NAME

Dim oDstColumnName As String = oColumn.Name

If strCHARACTER_MA XIMUM_LENGTH <> String.Empty Then
oColumn.Size = CType(strCHARAC TER_MAXIMUM_LEN GTH,
String)
End If

If strDATA_TYPE <> String.Empty Then
oColumn.DataTyp e = CType(strDATA_T YPE, String)
End If

If strNUMERIC_PREC ISION <> String.Empty Then
oColumn.Precisi on = CType(strNUMERI C_PRECISION,
Integer)
End If

If strIS_NULLABLE <> String.Empty Then
oColumn.Nullabl e = strIS_NULLABLE
End If

oTransformation .DestinationCol umns.Add(oColum n)
oColumn = Nothing

End If
End If
Next

If iTransCount > 0 Then
oTransProps = oTransformation .TransformServe rProperties
oTransProps = Nothing
oCustomTask2.Tr ansformations.A dd(oTransformat ion)
oTransformation = Nothing

Return (True)
End If

Return (False)
End Function