Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Calls SSRS Report with One Parameter and Then Emails Expand / Collapse
Author
Message
Posted Friday, May 10, 2013 3:26 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 9, 2014 8:38 AM
Points: 83, Visits: 313
Sorry if this is the wrong section.

I have a report that I have created that contains one parameter called AgentEmail.

This report generates a list of customers which our support crew are going to call, however, each customer is assigned to a certain support agent by using the agent's email. The support person's email address is part of the report, which is why there's a parameter called AgentEmail.

I want to create a SSIS package where calls the report, fills in the AgentEmail parameter, and then emails the agent with his report with his list of customers.

Hope my explanation was clear.

I've searched and searched, but really haven't found anything that helped. I do find some results were they say to use the ForEachLoop, but directions are terrible.

Thanks for any help provided.

Post #1451751
Posted Friday, May 10, 2013 9:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 5:24 PM
Points: 262, Visits: 681
Before we go down the SSIS path, I just want to verify that Data Driven Subscriptions are not an option for you, because it seems like that should be your first choice for this. Otherwise, you are going to be calling the Report Services web service from SSIS.



Rick Krueger

Follow @dataogre
Post #1451807
Posted Monday, May 13, 2013 8:11 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 9, 2014 8:38 AM
Points: 83, Visits: 313
Rick, did consider the data-driven subscription, but we have Standard installed, so doesn't look like that will be an option. The version of SQL was not my call. This was installed way before I arrived here.
Post #1452125
Posted Monday, May 13, 2013 9:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 5:24 PM
Points: 262, Visits: 681
In general, I don't like calling web services (especially synchronous calls) from an SSIS box, because that ties the performance of the SSIS box to that of the client (the performance of this report). That said, if you proceed, you are going to want to call the Render method for your report and then email the results.



Rick Krueger

Follow @dataogre
Post #1452160
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse