Solved

A cleaner, faster method needed - Syntax help needed

Posted on 2006-11-30
3
251 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 62

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 62

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

864 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now