Solved

A cleaner, faster method needed - Syntax help needed

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

730 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