Help on global variable for insert statement

  • 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

  • oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('" & Textbox1.Text & "', '" & Textbox4.Text & "', '" & Textbox7.Text & "')"

    Russel Loski, MCSE Business Intelligence, Data Platform

  • 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!!

     

  • 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

  • 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