Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

A cleaner, faster method needed - Syntax help needed

Posted on 2006-11-30
3
Medium Priority
?
257 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 64

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 64

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

618 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