January 8, 2007 at 12:39 pm
I am new to DTS, but really enjoy it and was wondering if someone could help me with the following small vb app.
I am using the following DTS insert statement to insert records into my table. I have multiple textboxes that needs to be filled and then inserted, none of them exept Nulls. How can I modify my code to insert those textboxes as well as run through the boxes and then check if they have nulls and NOT insert the ones that has nulls?
My form has 9 textboxes. Textbox1, 2, 3 Needs to insert values into the first column. Textbox4, 5, 6 into the second and then Texrbox7, 8, 9 into the last column.
My question is how do I format the following line of code to do what I need?
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('1rowst', '2rowst', '3rowst')"
I think it is something like this, but I am Really not sure and some help would be greatly appretiated:
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('Textbox1', 'Textbox4', 'Textbox7')"
I am really not sure. Here follows the whole programs vb code.
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
goPackage = goPackageOld
goPackage.Name = "Multiple boxes"
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 oConnection As DTS.Connection2
oConnection = goPackage.Connections.New("SQLOLEDB")
oConnection.ConnectionProperties.Item("Integrated Security").Value = "SSPI"
oConnection.ConnectionProperties.Item("Persist Security Info").Value = True
oConnection.ConnectionProperties.Item("Initial Catalog").Value = "Compare"
oConnection.ConnectionProperties.Item("Data Source").Value = "(local)"
oConnection.ConnectionProperties.Item("Application Name").Value = "DTS Designer"
oConnection.Name = "Microsoft OLE DB Provider for SQL Server"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "(local)"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "Compare"
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False
goPackage.Connections.Add(oConnection)
oConnection = Nothing
Dim oStep As DTS.Step2
oStep = goPackage.Steps.New
oStep.Name = "DTSStep_DTSExecuteSQLTask_1"
oStep.Description = "Execute SQL Task: undefined"
oStep.ExecutionStatus = 4
oStep.TaskName = "DTSTask_DTSExecuteSQLTask_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)
oStep = Nothing
Call Task_Sub1(goPackage)
goPackage.Execute()
tracePackageError(goPackage)
goPackage.Uninitialize()
goPackage = Nothing
goPackageOld = Nothing
End Sub
'-----------------------------------------------------------------------------
' error reporting using step.GetExecutionErrorInfo after execution
'-----------------------------------------------------------------------------
Public Sub tracePackageError(ByVal oPackage As DTS.Package)
Dim ErrorCode As Long
Dim ErrorSource As String
Dim ErrorDescription As String
Dim ErrorHelpFile As String
Dim ErrorHelpContext As Long
Dim ErrorIDofInterfaceWithError As String
Dim i As Integer
For i = 1 To oPackage.Steps.Count
If oPackage.Steps.Item(i).ExecutionResult = DTS.DTSStepExecResult.DTSStepExecResult_Failure Then
oPackage.Steps.Item(i).GetExecutionErrorInfo(ErrorCode, ErrorSource, ErrorDescription, _
ErrorHelpFile, ErrorHelpContext, ErrorIDofInterfaceWithError)
MsgBox(oPackage.Steps.Item(i).Name & " failed" & vbCrLf & ErrorSource & vbCrLf & ErrorDescription)
End If
Next i
End Sub
Public Sub Task_Sub1(ByVal goPackage As Object)
Dim oTask As DTS.Task
'Dim oLookup As DTS.Lookup
Dim oCustomTask1 As DTS.ExecuteSQLTask2
oTask = CType(goPackage, DTS.Package).Tasks.New("DTSExecuteSQLTask")
oTask.Name = "DTSTask_DTSExecuteSQLTask_1"
oCustomTask1 = oTask.CustomTask
oCustomTask1.Name = "DTSTask_DTSExecuteSQLTask_1"
oCustomTask1.Description = "Execute SQL Task: undefined"
oCustomTask1.SQLStatement = "Insert into TestTable (Test1, Test2, Test3) " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('1rowst', '2rowst', '3rowst')"
oCustomTask1.ConnectionID = 1
oCustomTask1.CommandTimeout = 0
oCustomTask1.OutputAsRecordset = False
goPackage.Tasks.Add(oTask)
oCustomTask1 = Nothing
oTask = Nothing
End Sub
January 9, 2007 at 6:43 am
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" & Textbox1.Text & "', '" & Textbox4.Text & "', '" & Textbox7.Text & "')"
Russel Loski, MCSE Business Intelligence, Data Platform
January 9, 2007 at 7:00 am
Russel,
Thanks, you are really helping me out and I really appreciate it. Now that you answered my first question maybe you can help me with the next one based on your answer above.
I want all the textboxes on one form. As you know my db does not allow the insert of nulls. How do I go about in VB to run and check a textbox and if it does not have a value, skip that box and not try to import the next one and if that has nothing in, just the first record?
Do I do something like this?
If Textbox2 = '' then
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" & Textbox1.Text & "', '" & Textbox4.Text & "', '" & Textbox7.Text & "')"
Elseif Textbox3 = '' then
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" & Textbox1.Text & "', '" & Textbox4.Text & "', '" & Textbox7.Text & "')"
And
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" & Textbox2.Text & "', '" & Textbox5.Text & "', '" & Textbox8.Text & "')"
Again, This is probably completely wrong, I know, but I am trying.
Thanks Russel!!
January 9, 2007 at 7:25 am
Just a couple modifications if you are using VB.Net. You need the text property. I believe that the Text property always has at least an empty string. I would use this test in order to simplify my code.
if Textbox1.Text <> "" then
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" & Textbox1.Text & "', '" & Textbox4.Text & "', '" & Textbox7.Text & "')"
elseif Textbox2.Text <> "" then
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" & Textbox2.Text & "', '" & Textbox5.Text & "', '" & Textbox8.Text & "')"
elseif Textbox3.Text <> "" then
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" & Textbox3.Text & "', '" & Textbox6.Text & "', '" & Textbox9.Text & "')"
end if
Russel Loski, MCSE Business Intelligence, Data Platform
January 9, 2007 at 7:29 am
That worked, Thanks Russel!!
Still working on the Save file, but that needs to wait for now. I have to finish this first.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply