Script Task Builds Successfully But Still Has Compile Error (SQL 2012) RRS feed

  • Question

  • This is repost in a way but the problem has not gone away and I really need to find a solution.

    I have some script from SSIS 2008 that uses the ReportExecution2005 web service to execute SSRS reports and save the results to Excel. There were some initial problems with confusion between Web Service Reference (legacy?) and Service Reference (WCF) but I think that I have that sorted out now.

    The remaining problem is that, while the VB script (which I have pasted in from a working SSIS 2008 Script Task) builds/complies without error, I get a compile error when I close the Script Task and it has the dreaded "Red X" in the Control Flow. If I attempt to run the package I get a message box saying that there is a compile error.

    How can I identify the problem and fix the error? The script itself has no errors in SSIS 2008.


    R Campbell

    Monday, November 10, 2014 8:32 AM

All replies

  • This is the code by the way (see below) as pasted into the Main sub.

    I have added http://localhost/reportserver/reportexecution2005.asmx as a Web Service Reference (under the  "Advanced" button) not just a Service Reference (WCF). I can build the code without error but I get an error, saying that there is compile error, when I close the Script Task. ReportExecutionService definitely shows up in Object Explorer, not the WCF "equivalent" of ReportExecutionServiceSoapClient.

    I have always had the impression, that when you close a Script Task, a compiler kicks in to auto-generate some internal script. Could it be (as seems likely) that this compiler is picking up errors that "Build" doesn't (when the script is open)? I think that there was once an explicit Compile rather than just Build option in the Script editing environment but I can't find it in SQL 2012 Database Tools (nee BIDS).

            Dim objRSExec As New localhost.ReportExecutionService
            Dim objParam0 As New localhost.ParameterValue
            Dim objParam1 As New localhost.ParameterValue
            Dim objParam2 As New localhost.ParameterValu
            Dim objParam3 As New localhost.ParameterValue
            Dim objParam4 As New localhost.ParameterValue
            Dim objParam5 As New localhost.ParameterValue
            Dim objParam6 As New localhost.ParameterValue
            Dim objParams() As localhost.ParameterValue
            Dim objResult() As Byte
            Dim objStream As FileStream
            Dim FileType As String
            Dim FileExtension As String
            Dim FilePath As String
            Dim NumberOfParameters As Integer
            Dim ReportName As String
            Dim TaskSuccess As Boolean = True
            FileType = Dts.Variables("FileType").Value.ToString()
            FileExtension = Dts.Variables("FileExtension").Value.ToString()
            FilePath = Dts.Variables("FilePathNameExt").Value.ToString()
            ReportName = Dts.Variables("ReportPathName").Value.ToString()
            NumberOfParameters = CInt(Dts.Variables("NumberOfParameters").Value) - 1
            ReDim objParams(NumberOfParameters)
            If NumberOfParameters >= 0 Then
                objParam0.Name = Dts.Variables("P0Name").Value.ToString()
                objParam0.Value = Dts.Variables("P0Value").Value.ToString()
                objParams(0) = objParam0
            End If
            If NumberOfParameters >= 1 Then
                objParam1.Name = Dts.Variables("P1Name").Value.ToString()
                objParam1.Value = Dts.Variables("P1Value").Value.ToString()
                objParams(1) = objParam1
            End If
            If NumberOfParameters >= 2 Then
                objParam2.Name = Dts.Variables("P2Name").Value.ToString()
                objParam2.Value = Dts.Variables("P2Value").Value.ToString()
                objParams(2) = objParam2
            End If
            If NumberOfParameters >= 3 Then
                objParam3.Name = Dts.Variables("P3Name").Value.ToString()
                objParam3.Value = Dts.Variables("P3Value").Value.ToString()
                objParams(3) = objParam3
            End If
            If NumberOfParameters >= 4 Then
                objParam4.Name = Dts.Variables("P4Name").Value.ToString()
                objParam4.Value = Dts.Variables("P4Value").Value.ToString()
                objParams(4) = objParam4
            End If
            If NumberOfParameters >= 5 Then
                objParam5.Name = Dts.Variables("P5Name").Value.ToString()
                objParam5.Value = Dts.Variables("P5Value").Value.ToString()
                objParams(5) = objParam5
            End If
            If NumberOfParameters >= 6 Then
                objParam6.Name = Dts.Variables("P6Name").Value.ToString()
                objParam6.Value = Dts.Variables("P6Value").Value.ToString()
                objParams(6) = objParam6
            End If
                With objRSExec
                    .Credentials = System.Net.CredentialCache.DefaultNetworkCredentials
                    .Url = "http://localhost/reportserver/reportexecution2005.asmx"
                    .LoadReport(ReportName, Nothing)
                    .SetExecutionParameters(objParams, "en-us")
                    objResult = .Render(FileType, Nothing, FileExtension, Nothing, Nothing, Nothing, Nothing)
                End With
                objStream = File.Create(FilePath, objResult.Length)
                With objStream
                    .Write(objResult, 0, objResult.Length)
                End With
            Catch e As Exception
                Dts.Log("ERROR - RSExec - " & e.Message.ToString(), 100, Nothing)
                TaskSuccess = False
            End Try
            If TaskSuccess Then
                Dts.TaskResult = ScriptResults.Success
                Dts.TaskResult = ScriptResults.Failure
            End If

    R Campbell

    Monday, November 10, 2014 10:03 AM