Hi, I have a best question practice about database design. My idea is to
store business rules information for my customers. Basically, I define a set
of rules. For each rule, I know the type. Then I want to describe the
customers' sets of rules.
I want a table of CustomerID, RuleID, Value
Example:
Rule 1 = tax rate (Currency)
Rule 2 = Fiscal Year Start (Date)
Rule 3 = Have website (Boolean)
Then, my table might look like this: (CustomerID, RuleID, Value)
123,1,7.5
123,2,#1/1/5#
123,3,True
Obviously, I can't have a single column in SQL Server 2000 that holds
variant types. One solution is to have a column for each type and keep Null
values in all of them except the correct type column. That doesn't seem
right.
One suggestion was to cast the value to Binary, store it as such and then
cast the Binary value back to the expected data type when we need to use it.
Would that work in all cases?
Any thoughts are appreciated.
John,
these are not 'business rules'...these are attributes of customer and
would most effectively be stored as separate attributes....overtyping
fields based on other fields is something we did in languages like
cobol to save space and coding.....not really something we should do
in an environment where the end user is supposed to query the data.
Best Regards
Peter Nolan
www.peternolan.com
|||To add to Peter's reply, what you're doing is creating what is known as an
OTLT (One True Lookup Table) or EAV (Entity Attribute-Value). This is a
very common mistake that developers new to databases make, and it can cause
severe data integrity problems. Please refer to:
http://groups.google.com/groups?hl=e...lt&qt_s=Search
http://groups.google.com/groups?hl=e...av&qt_s=Search
http://groups.google.com/groups?hl=e...rm&qt_s=Search
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"JohnMSyrasoft" <JohnMSyrasoft@.discussions.microsoft.com> wrote in message
news:6EFAB117-BD1F-48FF-B58F-C7DD61B13A86@.microsoft.com...
> Hi, I have a best question practice about database design. My idea is to
> store business rules information for my customers. Basically, I define a
> set
> of rules. For each rule, I know the type. Then I want to describe the
> customers' sets of rules.
> I want a table of CustomerID, RuleID, Value
> Example:
> Rule 1 = tax rate (Currency)
> Rule 2 = Fiscal Year Start (Date)
> Rule 3 = Have website (Boolean)
> Then, my table might look like this: (CustomerID, RuleID, Value)
> 123,1,7.5
> 123,2,#1/1/5#
> 123,3,True
> Obviously, I can't have a single column in SQL Server 2000 that holds
> variant types. One solution is to have a column for each type and keep
> Null
> values in all of them except the correct type column. That doesn't seem
> right.
> One suggestion was to cast the value to Binary, store it as such and then
> cast the Binary value back to the expected data type when we need to use
> it.
> Would that work in all cases?
> Any thoughts are appreciated.
>
|||I looked at the first search and at the first thread that came up. OTLT was
advocated (amongst others) by a guy from HP Openview. I have worked with HP
Openview and I though it was a pile of junk*. I now understand why.
* clearest memory of that symptom: having a dialog box with 3 buttons, 2 of
which had the same keyboard shortcut
Jacco Schalkwijk
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eftQlTVzFHA.2076@.TK2MSFTNGP14.phx.gbl...
> To add to Peter's reply, what you're doing is creating what is known as an
> OTLT (One True Lookup Table) or EAV (Entity Attribute-Value). This is a
> very common mistake that developers new to databases make, and it can
> cause severe data integrity problems. Please refer to:
> http://groups.google.com/groups?hl=e...lt&qt_s=Search
> http://groups.google.com/groups?hl=e...av&qt_s=Search
> http://groups.google.com/groups?hl=e...rm&qt_s=Search
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "JohnMSyrasoft" <JohnMSyrasoft@.discussions.microsoft.com> wrote in message
> news:6EFAB117-BD1F-48FF-B58F-C7DD61B13A86@.microsoft.com...
>
|||Hi Adam,
wow, it even has a name? And I've not heard of it? That's something.
We have done things like encode large numbers of miscellaneous codes
into one lookup table to save ourselves the paid of having large
numbers of lookup tables...and one operational system I used to use at
IBM was the Common Table Management System where very large numbers of
code tables were stored....but these were not attributes of a defined
entity...or they were used to decode code stored on the original
entity when we wanted to save space....not an issue any more..
Peter
Showing posts with label customers. Show all posts
Showing posts with label customers. Show all posts
Monday, March 26, 2012
Generic data types in SQL Server
Hi, I have a best question practice about database design. My idea is to
store business rules information for my customers. Basically, I define a se
t
of rules. For each rule, I know the type. Then I want to describe the
customers' sets of rules.
I want a table of CustomerID, RuleID, Value
Example:
Rule 1 = tax rate (Currency)
Rule 2 = Fiscal Year Start (Date)
Rule 3 = Have website (Boolean)
Then, my table might look like this: (CustomerID, RuleID, Value)
123,1,7.5
123,2,#1/1/5#
123,3,True
Obviously, I can't have a single column in SQL Server 2000 that holds
variant types. One solution is to have a column for each type and keep Null
values in all of them except the correct type column. That doesn't seem
right.
One suggestion was to cast the value to Binary, store it as such and then
cast the Binary value back to the expected data type when we need to use it.
Would that work in all cases?
Any thoughts are appreciated.John,
these are not 'business rules'...these are attributes of customer and
would most effectively be stored as separate attributes....overtyping
fields based on other fields is something we did in languages like
cobol to save space and coding.....not really something we should do
in an environment where the end user is supposed to query the data.
Best Regards
Peter Nolan
www.peternolan.com|||To add to Peter's reply, what you're doing is creating what is known as an
OTLT (One True Lookup Table) or EAV (Entity Attribute-Value). This is a
very common mistake that developers new to databases make, and it can cause
severe data integrity problems. Please refer to:
http://groups.google.com/groups?hl=...tlt&qt_s=Search
http://groups.google.com/groups?hl=...eav&qt_s=Search
http://groups.google.com/groups?hl=...orm&qt_s=Search
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"JohnMSyrasoft" <JohnMSyrasoft@.discussions.microsoft.com> wrote in message
news:6EFAB117-BD1F-48FF-B58F-C7DD61B13A86@.microsoft.com...
> Hi, I have a best question practice about database design. My idea is to
> store business rules information for my customers. Basically, I define a
> set
> of rules. For each rule, I know the type. Then I want to describe the
> customers' sets of rules.
> I want a table of CustomerID, RuleID, Value
> Example:
> Rule 1 = tax rate (Currency)
> Rule 2 = Fiscal Year Start (Date)
> Rule 3 = Have website (Boolean)
> Then, my table might look like this: (CustomerID, RuleID, Value)
> 123,1,7.5
> 123,2,#1/1/5#
> 123,3,True
> Obviously, I can't have a single column in SQL Server 2000 that holds
> variant types. One solution is to have a column for each type and keep
> Null
> values in all of them except the correct type column. That doesn't seem
> right.
> One suggestion was to cast the value to Binary, store it as such and then
> cast the Binary value back to the expected data type when we need to use
> it.
> Would that work in all cases?
> Any thoughts are appreciated.
>|||I looked at the first search and at the first thread that came up. OTLT was
advocated (amongst others) by a guy from HP Openview. I have worked with HP
Openview and I though it was a pile of junk*. I now understand why.
* clearest memory of that symptom: having a dialog box with 3 buttons, 2 of
which had the same keyboard shortcut
Jacco Schalkwijk
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eftQlTVzFHA.2076@.TK2MSFTNGP14.phx.gbl...
> To add to Peter's reply, what you're doing is creating what is known as an
> OTLT (One True Lookup Table) or EAV (Entity Attribute-Value). This is a
> very common mistake that developers new to databases make, and it can
> cause severe data integrity problems. Please refer to:
> http://groups.google.com/groups?hl=...tlt&qt_s=Search
> http://groups.google.com/groups?hl=...eav&qt_s=Search
> http://groups.google.com/groups?hl=...orm&qt_s=Search
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "JohnMSyrasoft" <JohnMSyrasoft@.discussions.microsoft.com> wrote in message
> news:6EFAB117-BD1F-48FF-B58F-C7DD61B13A86@.microsoft.com...
>|||Hi Adam,
wow, it even has a name? And I've not heard of it? That's something.
We have done things like encode large numbers of miscellaneous codes
into one lookup table to save ourselves the paid of having large
numbers of lookup tables...and one operational system I used to use at
IBM was the Common Table Management System where very large numbers of
code tables were stored....but these were not attributes of a defined
entity...or they were used to decode code stored on the original
entity when we wanted to save space....not an issue any more..
Peter
store business rules information for my customers. Basically, I define a se
t
of rules. For each rule, I know the type. Then I want to describe the
customers' sets of rules.
I want a table of CustomerID, RuleID, Value
Example:
Rule 1 = tax rate (Currency)
Rule 2 = Fiscal Year Start (Date)
Rule 3 = Have website (Boolean)
Then, my table might look like this: (CustomerID, RuleID, Value)
123,1,7.5
123,2,#1/1/5#
123,3,True
Obviously, I can't have a single column in SQL Server 2000 that holds
variant types. One solution is to have a column for each type and keep Null
values in all of them except the correct type column. That doesn't seem
right.
One suggestion was to cast the value to Binary, store it as such and then
cast the Binary value back to the expected data type when we need to use it.
Would that work in all cases?
Any thoughts are appreciated.John,
these are not 'business rules'...these are attributes of customer and
would most effectively be stored as separate attributes....overtyping
fields based on other fields is something we did in languages like
cobol to save space and coding.....not really something we should do
in an environment where the end user is supposed to query the data.
Best Regards
Peter Nolan
www.peternolan.com|||To add to Peter's reply, what you're doing is creating what is known as an
OTLT (One True Lookup Table) or EAV (Entity Attribute-Value). This is a
very common mistake that developers new to databases make, and it can cause
severe data integrity problems. Please refer to:
http://groups.google.com/groups?hl=...tlt&qt_s=Search
http://groups.google.com/groups?hl=...eav&qt_s=Search
http://groups.google.com/groups?hl=...orm&qt_s=Search
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"JohnMSyrasoft" <JohnMSyrasoft@.discussions.microsoft.com> wrote in message
news:6EFAB117-BD1F-48FF-B58F-C7DD61B13A86@.microsoft.com...
> Hi, I have a best question practice about database design. My idea is to
> store business rules information for my customers. Basically, I define a
> set
> of rules. For each rule, I know the type. Then I want to describe the
> customers' sets of rules.
> I want a table of CustomerID, RuleID, Value
> Example:
> Rule 1 = tax rate (Currency)
> Rule 2 = Fiscal Year Start (Date)
> Rule 3 = Have website (Boolean)
> Then, my table might look like this: (CustomerID, RuleID, Value)
> 123,1,7.5
> 123,2,#1/1/5#
> 123,3,True
> Obviously, I can't have a single column in SQL Server 2000 that holds
> variant types. One solution is to have a column for each type and keep
> Null
> values in all of them except the correct type column. That doesn't seem
> right.
> One suggestion was to cast the value to Binary, store it as such and then
> cast the Binary value back to the expected data type when we need to use
> it.
> Would that work in all cases?
> Any thoughts are appreciated.
>|||I looked at the first search and at the first thread that came up. OTLT was
advocated (amongst others) by a guy from HP Openview. I have worked with HP
Openview and I though it was a pile of junk*. I now understand why.
* clearest memory of that symptom: having a dialog box with 3 buttons, 2 of
which had the same keyboard shortcut
Jacco Schalkwijk
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eftQlTVzFHA.2076@.TK2MSFTNGP14.phx.gbl...
> To add to Peter's reply, what you're doing is creating what is known as an
> OTLT (One True Lookup Table) or EAV (Entity Attribute-Value). This is a
> very common mistake that developers new to databases make, and it can
> cause severe data integrity problems. Please refer to:
> http://groups.google.com/groups?hl=...tlt&qt_s=Search
> http://groups.google.com/groups?hl=...eav&qt_s=Search
> http://groups.google.com/groups?hl=...orm&qt_s=Search
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "JohnMSyrasoft" <JohnMSyrasoft@.discussions.microsoft.com> wrote in message
> news:6EFAB117-BD1F-48FF-B58F-C7DD61B13A86@.microsoft.com...
>|||Hi Adam,
wow, it even has a name? And I've not heard of it? That's something.
We have done things like encode large numbers of miscellaneous codes
into one lookup table to save ourselves the paid of having large
numbers of lookup tables...and one operational system I used to use at
IBM was the Common Table Management System where very large numbers of
code tables were stored....but these were not attributes of a defined
entity...or they were used to decode code stored on the original
entity when we wanted to save space....not an issue any more..
Peter
Monday, March 12, 2012
Generating a personalised report for every vendor and mailing out the same
I have the following reporting scenario.
I have a table which contains a list of customers/vendors and each
record in this table as the customer/vendor number, name and email
address of the vendor and also the record as the value of the business
done by the vendor/customer for this month, year to date, last
year,etc our business wants us to generate a report for each
customer/vendor from this table and email the resulting output in a
.pdf report to the email address on this record. It is almost like the
account statement for each customer or vendor for that month.
Please clarify how I can implement this solution with DTS. I can see a
stored procedure can go through all the customers but how do I
generate the statement for each customer into a .pdf output and then
email that file to that vendor/customers email address from SQL+DTS
Thanks
KarenKaren
Start with www.sqldts.com
declare @.sql varchar(255)
set @.sql='bcp "select * FROM northwind.dbo.orders WHERE employeeid = 1"
queryout "c:\temp\1.csv" -c -T -SSERVER -Usa -Ppass'
exec master..xp_cmdshell @.sql
"Karen Middleton" <karenmiddleol@.yahoo.com> wrote in message
news:a5fd468a.0504111747.90b8491@.posting.google.com...
> I have the following reporting scenario.
> I have a table which contains a list of customers/vendors and each
> record in this table as the customer/vendor number, name and email
> address of the vendor and also the record as the value of the business
> done by the vendor/customer for this month, year to date, last
> year,etc our business wants us to generate a report for each
> customer/vendor from this table and email the resulting output in a
> .pdf report to the email address on this record. It is almost like the
> account statement for each customer or vendor for that month.
> Please clarify how I can implement this solution with DTS. I can see a
> stored procedure can go through all the customers but how do I
> generate the statement for each customer into a .pdf output and then
> email that file to that vendor/customers email address from SQL+DTS
> Thanks
> Karen
I have a table which contains a list of customers/vendors and each
record in this table as the customer/vendor number, name and email
address of the vendor and also the record as the value of the business
done by the vendor/customer for this month, year to date, last
year,etc our business wants us to generate a report for each
customer/vendor from this table and email the resulting output in a
.pdf report to the email address on this record. It is almost like the
account statement for each customer or vendor for that month.
Please clarify how I can implement this solution with DTS. I can see a
stored procedure can go through all the customers but how do I
generate the statement for each customer into a .pdf output and then
email that file to that vendor/customers email address from SQL+DTS
Thanks
KarenKaren
Start with www.sqldts.com
declare @.sql varchar(255)
set @.sql='bcp "select * FROM northwind.dbo.orders WHERE employeeid = 1"
queryout "c:\temp\1.csv" -c -T -SSERVER -Usa -Ppass'
exec master..xp_cmdshell @.sql
"Karen Middleton" <karenmiddleol@.yahoo.com> wrote in message
news:a5fd468a.0504111747.90b8491@.posting.google.com...
> I have the following reporting scenario.
> I have a table which contains a list of customers/vendors and each
> record in this table as the customer/vendor number, name and email
> address of the vendor and also the record as the value of the business
> done by the vendor/customer for this month, year to date, last
> year,etc our business wants us to generate a report for each
> customer/vendor from this table and email the resulting output in a
> .pdf report to the email address on this record. It is almost like the
> account statement for each customer or vendor for that month.
> Please clarify how I can implement this solution with DTS. I can see a
> stored procedure can go through all the customers but how do I
> generate the statement for each customer into a .pdf output and then
> email that file to that vendor/customers email address from SQL+DTS
> Thanks
> Karen
Generating a personalised report for every vendor and mailing out the same
I have the following reporting scenario.
I have a table which contains a list of customers/vendors and each
record in this table as the customer/vendor number, name and email
address of the vendor and also the record as the value of the business
done by the vendor/customer for this month, year to date, last
year,etc our business wants reporting services to generate a report
for each customer/vendor from this table and email the resulting .pdf
report to the email address on this record.
Please clarify how I can implement this solution can reporting
services do this.
Thanks
KarenPlease look up data-driven query in Books Online. You will need to create a
subscriptions table with a column for each piece of data that is different
among all the report subscribers'; e-mail, vendor number, etc.
Charles Kangai, MCDBA, MCT
"Karen Middleton" wrote:
> I have the following reporting scenario.
> I have a table which contains a list of customers/vendors and each
> record in this table as the customer/vendor number, name and email
> address of the vendor and also the record as the value of the business
> done by the vendor/customer for this month, year to date, last
> year,etc our business wants reporting services to generate a report
> for each customer/vendor from this table and email the resulting .pdf
> report to the email address on this record.
> Please clarify how I can implement this solution can reporting
> services do this.
> Thanks
> Karen
>
I have a table which contains a list of customers/vendors and each
record in this table as the customer/vendor number, name and email
address of the vendor and also the record as the value of the business
done by the vendor/customer for this month, year to date, last
year,etc our business wants reporting services to generate a report
for each customer/vendor from this table and email the resulting .pdf
report to the email address on this record.
Please clarify how I can implement this solution can reporting
services do this.
Thanks
KarenPlease look up data-driven query in Books Online. You will need to create a
subscriptions table with a column for each piece of data that is different
among all the report subscribers'; e-mail, vendor number, etc.
Charles Kangai, MCDBA, MCT
"Karen Middleton" wrote:
> I have the following reporting scenario.
> I have a table which contains a list of customers/vendors and each
> record in this table as the customer/vendor number, name and email
> address of the vendor and also the record as the value of the business
> done by the vendor/customer for this month, year to date, last
> year,etc our business wants reporting services to generate a report
> for each customer/vendor from this table and email the resulting .pdf
> report to the email address on this record.
> Please clarify how I can implement this solution can reporting
> services do this.
> Thanks
> Karen
>
Friday, March 9, 2012
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.
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.
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<
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<
Subscribe to:
Posts (Atom)