Solved

A cleaner, faster method needed - Syntax help needed

Posted on 2006-11-30
3
253 Views
Last Modified: 2008-03-10
Friends,

I have the code, that in it its present form, will work, but it's rather ugly.  Is there a better method?

Thanks,
Eric

    Private Sub btnExportToEvent_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportToEvent.Click

        Dim SSISPackage As String = Nothing
        dbUpdate.UpdateDatabase_String(RSdsn, "SetPreamble", "Preamble", Preamble, "")
        Dim ResultMessage As String = "The SSIS Packages have completed, and here are the results:" & vbCrLf & vbCrLf

        Select Case SSISRun
            Case "Practice1"
                SSISPackage = DetermineSSISPackageToRun(RSSQLServer, "Competitors")
                ResultMessage = ResultMessage & ExecuteSSISPackage(SSISPackage)
                SSISPackage = DetermineSSISPackageToRun(RSSQLServer, "Events")
                ResultMessage = ResultMessage & ExecuteSSISPackage(SSISPackage)
                SSISPackage = DetermineSSISPackageToRun(RSSQLServer, "Passings")
                ResultMessage = ResultMessage & ExecuteSSISPackage(SSISPackage)
                SSISPackage = DetermineSSISPackageToRun(RSSQLServer, "Results")
                ResultMessage = ResultMessage & ExecuteSSISPackage(SSISPackage)
                SSISPackage = DetermineSSISPackageToRun(RSSQLServer, "Runs")
                ResultMessage = ResultMessage & ExecuteSSISPackage(SSISPackage)
                SSISPackage = DetermineSSISPackageToRun(RSSQLServer, "Sections")
                ResultMessage = ResultMessage & ExecuteSSISPackage(SSISPackage)
                SSISPackage = DetermineSSISPackageToRun(RSSQLServer, "SectionTimes")
                ResultMessage = ResultMessage & ExecuteSSISPackage(SSISPackage)
                SSISPackage = DetermineSSISPackageToRun(RSSQLServer, "Timelines")
                ResultMessage = ResultMessage & ExecuteSSISPackage(SSISPackage)
            Case SSISRun.Contains("Practice")
                If SSISRun <> "Practice1" Then
                    SSISPackage = DetermineSSISPackageToRun(RSSQLServer, "Passings")
                    ResultMessage = ResultMessage & ExecuteSSISPackage(SSISPackage)
                    SSISPackage = DetermineSSISPackageToRun(RSSQLServer, "Results")
                    ResultMessage = ResultMessage & ExecuteSSISPackage(SSISPackage)
                    SSISPackage = DetermineSSISPackageToRun(RSSQLServer, "SectionTimes")
                    ResultMessage = ResultMessage & ExecuteSSISPackage(SSISPackage)
                End If
            Case "Qualifications"
                SSISPackage = DetermineSSISPackageToRun(RSSQLServer, "Passings")
                ResultMessage = ResultMessage & ExecuteSSISPackage(SSISPackage)
                SSISPackage = DetermineSSISPackageToRun(RSSQLServer, "Results")
                ResultMessage = ResultMessage & ExecuteSSISPackage(SSISPackage)
                SSISPackage = DetermineSSISPackageToRun(RSSQLServer, "SectionTimes")
                ResultMessage = ResultMessage & ExecuteSSISPackage(SSISPackage)
            Case "Race"
                SSISPackage = DetermineSSISPackageToRun(RSSQLServer, "Cautions")
                ResultMessage = ResultMessage & ExecuteSSISPackage(SSISPackage)
                SSISPackage = DetermineSSISPackageToRun(RSSQLServer, "Passings")
                ResultMessage = ResultMessage & ExecuteSSISPackage(SSISPackage)
                SSISPackage = DetermineSSISPackageToRun(RSSQLServer, "Penalties")
                ResultMessage = ResultMessage & ExecuteSSISPackage(SSISPackage)
                SSISPackage = DetermineSSISPackageToRun(RSSQLServer, "Results")
                ResultMessage = ResultMessage & ExecuteSSISPackage(SSISPackage)
                SSISPackage = DetermineSSISPackageToRun(RSSQLServer, "SectionTimes")
                ResultMessage = ResultMessage & ExecuteSSISPackage(SSISPackage)
        End Select
        MessageBox.Show(ResultMessage, "SSIS Export Results", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Sub
    Public Function ExecuteSSISPackage(ByVal SSISPackage As String)

        Dim app As Microsoft.SqlServer.Dts.Runtime.Application
        app = New Microsoft.SqlServer.Dts.Runtime.Application
        Dim pkgResults_Sql As DTSExecResult
        Dim TableToExport As String = Nothing
        Dim pkgIn As Package = New Package
        Dim SSISPackageResult As String = Nothing

        pkgIn = app.LoadFromSqlServer("\\" + SSISPackage, RSSQLServer, SQLSAUsername, SQLSAPassword, Nothing)
        pkgResults_Sql = pkgIn.Execute()
        SSISPackageResult = SSISPackage & " - " & pkgResults_Sql.ToString() & vbCrLf
        Return SSISPackageResult

    End Function
    Private Function DetermineSSISPackageToRun(ByVal Server As String, ByVal TableToExport As String)
        Dim strSSISPackage As String = Nothing
        strSSISPackage = (SeriesAbbr & "_" & TableToExport & "_" & Server)
        Return strSSISPackage
    End Function

Thanks in advance!
0
Comment
Question by:indy500fan
  • 2
3 Comments
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 18048969
Hi indy500fan;

See if this will fit your needs.

I modified your case statement in the following way

    Case SSISRun.Contains("Practice")

Returns a Boolean and because  the test expression is a string the system will test the value True or False to see if it matches the test expression which in your case would be one of the following words Practice1, Qualifications, Race and one case that should not be Practice1. Therefore this case will never match any of the test expression and so will never get executed. So what I did is to place it in the Case Else section. This will only get executed if all the others do not match.

    Private Sub btnExportToEvent_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles btnExportToEvent.Click

        Dim SSISPackage() As String = Nothing
        dbUpdate.UpdateDatabase_String(RSdsn, "SetPreamble", "Preamble", Preamble, "")
        Dim ResultMessage As String = _
            "The SSIS Packages have completed, and here are the results:" & vbCrLf & vbCrLf

        Select Case SSISRun
            Case "Practice1"
                SSISPackage = New String() {"Competitors", "Events", "Passings", _
                    "Results", "Runs", "Sections", "SectionTimes", "Timelines"}
            Case "Qualifications"
                SSISPackage = New String() {"Passings", "Results", "SectionTimes"}
            Case "Race"
                SSISPackage = New String() {"Competitors", "Passings", "Penalties", _
                    "Results", "SectionTimes"}
            Case Else
                SSISPackage = New String() {"Passings", "Results", "SectionTimes"}
        End Select

        ResultMessage = DetermineSSISPackageToRun(RSSQLServer, SSISPackage)
        MessageBox.Show(ResultMessage, "SSIS Export Results", MessageBoxButtons.OK, _
              MessageBoxIcon.Information)

    End Sub

    Private Function DetermineSSISPackageToRun(ByVal Server As String, _
        ByVal TableToExport() As String) As String

        Dim msg As String = Nothing
        Dim strSSISPackage As String = Nothing

        For Each table As String In TableToExport
            strSSISPackage = (SeriesAbbr & "_" & table & "_" & Server)
            msg += ExecuteSSISPackage(strSSISPackage)
        Next

        Return msg

    End Function

    Public Function ExecuteSSISPackage(ByVal SSISPackage As String) As String

        Dim app As Microsoft.SqlServer.Dts.Runtime.Application
        app = New Microsoft.SqlServer.Dts.Runtime.Application
        Dim pkgResults_Sql As DTSExecResult
        Dim TableToExport As String = Nothing
        Dim pkgIn As Package = New Package
        Dim SSISPackageResult As String = Nothing


        pkgIn = app.LoadFromSqlServer("\\" + SSISPackage, RSSQLServer, SQLSAUsername, SQLSAPassword, Nothing)
        pkgResults_Sql = pkgIn.Execute()
        SSISPackageResult = SSISPackage & " - " & pkgResults_Sql.ToString() & vbCrLf
       
        Return SSISPackageResult

    End Function

Fernando
0
 

Author Comment

by:indy500fan
ID: 18049019
Hey Fernando!  How have you been.  Makes total sense!  This is something of what I was looking for!

Thank you!

Eric
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 18049066
I am doing fine, hope your are the same. Glad I was able to help. ;=)
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
rebind a grid after user clicks on node in treeview 1 41
VB.Net - MemoryMappedFiles - Confirm receipt 2 24
vb.net winforms sizing/resolution? 4 42
Code enhancement 4 22
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question