Run SSRS report through SSIS using multiple choice parameter

Zaran 21 Reputation points
2021-05-16T01:42:39.74+00:00

Hi,

I appreciated it if you can help me how to complete the below code?
I want to run SSRS report through SSIS package.
My report in SSRS has a filter like Customer_Name, and it is a checkbox filter with multiple choice.
In order to call SSRS report through SSIS, I wrote the below code, and it works correctly when I select one value as filter for the Customer_Name, if I want to select more than one value, the result isn't correct.
***Question: "passing multiple values from one parameter to SSRS through SSIS". (I don't know how to complete the below code when I select multiple value)

Here the code:

Public Sub Main()
Dim Reporthpath As String=Dts.variables("Reprt1")
Dim Filepath As String=Dts.Variables("Filepath1")
Dim Filename As String=Dts.Variables("Filename1")
Dim Customer_Name As String= Dts.Variables("Customer_Name1")

Try
GeneralReport(Repotpath,Filepath,Filename,Customer_Name)
Dts.TaskResult= ScriptResult.Success
Catch
Dts.TaskResult=ScriptResult.Failure
End Try
End Sub

Public Sub Generate_Report(Reportpath A String, Filepath As String, Filename As String, Customer_Name As String)
Dim objectRSExec As New Aserv.ReportExecutionService()
Dim objResult() As Byte
Dim objStream As FileStream
Dim strFileType As String
Dim strFileExtension As String
Dim strFullPathName As String

Dim parameters(1) As Aserv.ParameterValue
parameters(0)=New Aserv.ParameterValue()
parameteme="Customer_Name"
parameters(0).Value=Customer_Name

strFileType="EXCELOPENXML"
strFileExtension="xlsx"
strFullPathName=Filepath+Filename+".xlsx"

With objRSExec
.Timeout = 400000
.Credetials=System.Net.CredentialCach.DefaultCredentials
.URL = "*****"
.LoadReport(Reportpath,Nothing)
.SetExecutionParameters, "en-us")
objResults= .Render(strFileType,Nothing,strFileExtension, Nothing, Nothing, Nothing, Nothing)
End With

With objStream
.Write(objResults,0,objResults.Lenght)
Close()
End With
End Sub

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,407 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. ZoeHui-MSFT 30,411 Reputation points
    2021-05-17T06:09:21.983+00:00

    Hi @Zaran ,

    Code writing is out of my ability.

    You may refer passing-value-to-multi-value-parameter-from-ssis-using-report-server-webservice to see if it will be useful.

    You may also take a look at the following reference as said here:

    how-to-pass-a-report-parameter-with-multiple-value.html

    Regards,

    Zoe


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues October

    0 comments No comments