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.hi,
i had a similar problem and found an easy solution: just create a new
subsciption in reporting services and specify the UNC-output-path and the
column that contains the filename. then schedule the subsciption to run every
night.
"greenmtnsun" wrote:
> 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.|||I don't think I understand how your suggestion could work.
I see nothing on the subscription that allows me to specify a column in a
table that assigns a file name. Perhaps there is a way to use an expression
but I don't know about it.
More importantly I also see nothing that allows me to tell this subscription
that it needs to create one PDF per customer. There is no looping mechanism.
Were you suggesting I create a schedule for EVERY customer? If so, I can't
do that.
Did I miss something?
Keith
"csc67" wrote:
> hi,
> i had a similar problem and found an easy solution: just create a new
> subsciption in reporting services and specify the UNC-output-path and the
> column that contains the filename. then schedule the subsciption to run every
> night.
> "greenmtnsun" wrote:
> > 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.|||Hi Keith,
sorry for being a little bit short on details but memory fades in time...
What you need to do is to define a datadriven subsciption. this doesn´t work
with windows-authentication in your reports connection-string. instead store
a user and password on report server (report properties, data source).
In step 1 of the datadriven subsription you define that the reports are to
be stored in the filesystem. in step 2 leave a connection string. here you
can store windows-credentials for a (super-) user. step 3 is were you then
define a sql query witch returns (at least) the filename plus
report-parameters. More possible fields to generated here and use in step 4
are: the UNC-path or the renderformat (a different for each customer for
example). the user and password provided on this page are for writing files
into the filesystem.
in step 5 you can then match report-parameters with your query from step 3
and finally define a schedule.
have fun!
"greenmtnsun" wrote:
> I don't think I understand how your suggestion could work.
> I see nothing on the subscription that allows me to specify a column in a
> table that assigns a file name. Perhaps there is a way to use an expression
> but I don't know about it.
> More importantly I also see nothing that allows me to tell this subscription
> that it needs to create one PDF per customer. There is no looping mechanism.
> Were you suggesting I create a schedule for EVERY customer? If so, I can't
> do that.
> Did I miss something?
> Keith
> "csc67" wrote:
> > hi,
> >
> > i had a similar problem and found an easy solution: just create a new
> > subsciption in reporting services and specify the UNC-output-path and the
> > column that contains the filename. then schedule the subsciption to run every
> > night.
> >
> > "greenmtnsun" wrote:
> >
> > > 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.