?
Solved

A cleaner, faster method needed - Syntax help needed

Posted on 2006-11-30
3
Medium Priority
?
256 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

777 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