Generate thousands of SSRS PDFs programmatically with SP or SSIS

I have a report that I'd like to involve in delivering tons of PDFs for each of our customers for a billing cycle. There is a table that the report reads from that gives it all its data necessary for the report (which is a customer bill) and the table also has a column that has the file name for the PDF for that particular customers bill for the report. Basically each table row represents one output report (bill) and each table row has its name nicely formated for me. Writing the report is not a problem. Figuring out how to run through thousands of rows generating a PDF for each with the file name from the table is my challenge.

I would like to create either a SP or a SSIS package (that is scheduled) that can run a report for each line of data in my table and spit out a PDF file to a UNC path. It might have to generate thousands of PDFs to a UNC path that has plenty of space. There will be another SSIS package that moves the PDFs later to their proper directory.

So, is there someone who has done this before? Any suggestions? Is there a quick path to doing this, would it take a ton of time? Any tutorials out there?

Thanks,
Keith

p.s. My preference is to do this in SP's or SSIS and if I need a .NET language to do that in VB.NET, but I'd rather avoid that if I can.

Yes, I am actually doing something similar in SSIS with reports.

I'm not generating thousands of them, but I am calling into RS and saving out a PDF file at the end of a process, from withinthe SSIS package. Earlier in the package there is some loop processing where I'm building and saving multiple files to disk, that part isn't a problem for you I'm sure. It was hard for me, but I'm not really very knowledgeable about SSIS <g>. You sound like you like it!

I use package variables to hold the various things I need from RS, and another variable for the output dir... which I use if the derived output file name doesn't come complete with a full path.

NB: I'm using URL Access, because I see absolutely no advantage to using SOAP for this. IOW, I just used a straight script (not ActiveX Script) task, not a Web Service task. Your mileage may vary.

The actual VB.NET code I use in the script task is a variant of what I posted in this thread. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1718577&SiteID=1 -- you'll even see the Dts variables referenced there <s>.

The only real "gotcha" I've found with this approach in SSIS (the URL Access part I mean) is building up the request URL properly with parameters, without having an ampersand mistakenly encoded or something from having been stored in the package variable. It's handleable and I'm not sure you even indicated that you need to worry about this. I think you'll see that hack in the code snippet in the thread referenced above.

So... Which part is really bothering you? The scaleability aspect, or ensuring rights to the output path, passing the credentials to the reporting server, or what? If it's the credentials I'm not really expert with that either but the same techniques are going to work as you would use *outside* of SSIS, in any VB.NET app that calls out to RS.

>L<

|||If you are using SSRS Enterprise, you should look into using "Data Driven Subscriptions". It sounds like that is exactly what you need. Creating an SSIS package to do what you're talking about would probably be overkill since the feature is out of the box with SSRS.

Here's a link to get you started:

http://technet.microsoft.com/en-us/library/ms159150.aspx
|||

I'm not sure it's overkill. I guess it depends on what you're comfortable with and where you want to do the work.

* -- the OP is already using a separate package to move the files after creation. This argues for a combined process, to make sure that things happen in the right sequence.

* -- the OP is concerned about scaleability (I have asked him/her for details, and it may be justified or not; we can't say). IAC the point is that moving the behavior out of RS may be legitimate as a way of moving some of the activity to a different box.

It's definitely do-able either way ...

>L<