SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Calls SSRS Report with One Parameter and Then Emails


SSIS Calls SSRS Report with One Parameter and Then Emails

Author
Message
DarthBurrito
DarthBurrito
SSC Veteran
SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)

Group: General Forum Members
Points: 281 Visits: 365
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.
Rick Krueger
Rick Krueger
Old Hand
Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)

Group: General Forum Members
Points: 334 Visits: 691
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
DarthBurrito
DarthBurrito
SSC Veteran
SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)

Group: General Forum Members
Points: 281 Visits: 365
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.
Rick Krueger
Rick Krueger
Old Hand
Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)

Group: General Forum Members
Points: 334 Visits: 691
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search