Recent SSRS books?

I will be starting a new project using SS2005 Reporting Services. Can
someone recommend 2 or 3 books on the subject? I am interested in a
book beyond the basics. (I believe I don't need the hand holding, or
"step by step," as that most of that can be found online.)
We will be applying report security and integrating with SharePoint in
the near future as well.
I found these so far.
1) Mastering SQL Server 2005 Reporting Services Infrastructure Design
(Joseph L. Jorden - Sybex, Published 2007)
2) Microsoft SQL Server 2005 Reporting Services (Brian Larson - McGraw-
Hill, Published 2005)
The first book listed above has a section discussing role based
security and SharePoint deployment. Are these mostly a slam dunk and
I don't need this in a book?
Has much changed since 2005? There are many books from 2005/2006 and
a few published more recently.
Thanks in advance!
DanI can recomend :-
http://www.amazon.co.uk/Hitchhikers-Guide-Visual-Studio-Server/dp/0321243625/ref=sr_1_1?ie=UTF8&s=books&qid=1196846573&sr=8-1
It covers most topics and starts at a level where you are expected to have
had a play with reporting services and mastered some of the basics of
creating reports.
Cheers
Dale
"ZCSI" <danzemmels@.gmail.com> wrote in message
news:f07e2e87-d3f8-4cd4-a9db-db259766e31d@.y43g2000hsy.googlegroups.com...
>I will be starting a new project using SS2005 Reporting Services. Can
> someone recommend 2 or 3 books on the subject? I am interested in a
> book beyond the basics. (I believe I don't need the hand holding, or
> "step by step," as that most of that can be found online.)
> We will be applying report security and integrating with SharePoint in
> the near future as well.
> I found these so far.
> 1) Mastering SQL Server 2005 Reporting Services Infrastructure Design
> (Joseph L. Jorden - Sybex, Published 2007)
> 2) Microsoft SQL Server 2005 Reporting Services (Brian Larson - McGraw-
> Hill, Published 2005)
> The first book listed above has a section discussing role based
> security and SharePoint deployment. Are these mostly a slam dunk and
> I don't need this in a book?
> Has much changed since 2005? There are many books from 2005/2006 and
> a few published more recently.
> Thanks in advance!
> Dan|||I have the Brian Larson book on my desk, and it's really helpful, but I'm not
a developper but a DBA. So I'm more interested in the technical en security
side of RS.
But it's a good book, and it does the trick for me.
"Dale" wrote:
> I can recomend :-
> http://www.amazon.co.uk/Hitchhikers-Guide-Visual-Studio-Server/dp/0321243625/ref=sr_1_1?ie=UTF8&s=books&qid=1196846573&sr=8-1
> It covers most topics and starts at a level where you are expected to have
> had a play with reporting services and mastered some of the basics of
> creating reports.
> Cheers
> Dale
>
> "ZCSI" <danzemmels@.gmail.com> wrote in message
> news:f07e2e87-d3f8-4cd4-a9db-db259766e31d@.y43g2000hsy.googlegroups.com...
> >I will be starting a new project using SS2005 Reporting Services. Can
> > someone recommend 2 or 3 books on the subject? I am interested in a
> > book beyond the basics. (I believe I don't need the hand holding, or
> > "step by step," as that most of that can be found online.)
> >
> > We will be applying report security and integrating with SharePoint in
> > the near future as well.
> >
> > I found these so far.
> > 1) Mastering SQL Server 2005 Reporting Services Infrastructure Design
> > (Joseph L. Jorden - Sybex, Published 2007)
> > 2) Microsoft SQL Server 2005 Reporting Services (Brian Larson - McGraw-
> > Hill, Published 2005)
> >
> > The first book listed above has a section discussing role based
> > security and SharePoint deployment. Are these mostly a slam dunk and
> > I don't need this in a book?
> >
> > Has much changed since 2005? There are many books from 2005/2006 and
> > a few published more recently.
> >
> > Thanks in advance!
> > Dan
>
>

Really new at this

I have MSDE running on my system and would like to make a new project or
import an mdb file.
When I attempt to make a new project, I enter the Administrator user name
and password and then after hitting next, I get the following error:
Connection failed:
SQLState:'28000'
SQL Server Error 18456
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user
'Administrator'.
I'm using the Administrator user name and password that I use to login in to
this Win2000 box.
What do I need to do?
Thanks.
I just selected to connect from a secure connection and now I have the
project, but the New button is grayed out for tables, view, stored
procedures, etc.
What should I do?
Is there a book or something on line I can read to get me started?
Thanks.
"SAC" <someone@.microsoft.com> wrote in message
news:e3CUbJZhEHA.904@.TK2MSFTNGP09.phx.gbl...
> I have MSDE running on my system and would like to make a new project or
> import an mdb file.
> When I attempt to make a new project, I enter the Administrator user name
> and password and then after hitting next, I get the following error:
> Connection failed:
> SQLState:'28000'
> SQL Server Error 18456
> [Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user
> 'Administrator'.
> I'm using the Administrator user name and password that I use to login in
to
> this Win2000 box.
> What do I need to do?
> Thanks.
>

Really dumb Stored Proc Question. But I need help and Im lost.

Right now, I'm working on the database of a new project. The project is basically a keyword tracking and referral tracking website directory. My plan is to give the website some java code that will allow me to grab the http_referer and request_uri per page load. (everytime they get traffic)

Right now, I've got some tables I think will work fine. One is the storage table, where I'll store what the javascript grabs.

table.http_ref (columns will be)
uid (Unique ID)
http_referer
request_uri

Then I got the results page I want to parse the http_ref table into. For example.

Here we have a google results page which is a search for baby shoes. This would be the http_referer

http://www.google.com/search?sourceid=navclient-ff&ie=UTF-8&rlz=1B2GGGL_enUS177US177&q=baby+shoes
And if someone clicks on a link get this page, which let's say I'm tracking in my project.
http://www.thebabymarketplace.com/securestore/c54581.2.html
in my http_ref table I will have
uid
(whatever)
http_referer
http://www.google.com/search?sourceid=navclient-ff&ie=UTF-8&rlz=1B2GGGL_enUS177US177&q=baby+shoes
request_uri
http://www.thebabymarketplace.com/securestore/c54581.2.html
so I want a stored proc to grab the http_referer column and parse it for the refering search engine
http://www.google.com
and the key words
search?sourceid=navclient-ff&ie=UTF-8&rlz=1B2GGGL_enUS177US177&q=baby+shoes
Then grab the uri_request, which should be the web page visited,
and INSERT into another table where I will hold
keywords
referer
refered page
Any suggestions? I've been going nuts over this.

Then the key point is to parse the http_referer. You should gurantee that all http_referer strings are in a same format, for example the search string begins with '/search?' and the keywords start with '&q=' and locate the end of the string. Then you can use such procedure:

CREATE PROC sp_ParseHttpRef
AS
INSERT INTO Tbl_Parsed
SELECT SUBSTRING(HttpReferer,1,CHARINDEX('/search?',HttpReferer)-1),
RIGHT(HttpReferer,LEN(HttpReferer)-(CHARINDEX('&q=',HttpReferer,CHARINDEX('/search?',HttpReferer))+LEN('&q=')-1)),
Request_URI
FROM Tbl_SourceHttp
go

|||

Thank you very much.

So, can I take this proc and use it as a template to track other search engine referer strings?

For instance:

MSN

http://search.msn.com/results.aspx?q=baby+shoes&FORM=SSRE

or Yahoo

http://search.yahoo.com/search?p=baby+shoes&sm=Yahoo%21+Search&fr=FP-tab-web-t&toggle=1&cop=&ei=UTF-8

and create a Proc for each search engine I'm tracking?

Seems as if I would then have to Schedule the proc's to run, our just call them when I grab the http_referer and request_uri?

|||

Sure you can use the sample as a template, just keep in mind to parse the http_referer correctly. You do not need to schedule the proc to run, you can call it when you grab the http_refer and request_uri in your application, or you can make the proc as an insert trigger on the table which stores the http_refer and request_uri. For example:

create trigger trg_ParseHttp on Tbl_SourceHttp for insert
as
insert into Tbl_Parsed
SELECT SUBSTRING(HttpReferer,1,CHARINDEX('/search?',HttpReferer)-1),
RIGHT(HttpReferer,LEN(HttpReferer)-(CHARINDEX('&q=',HttpReferer,CHARINDEX('/search?',HttpReferer))+LEN('&q=')-1)),
Request_URI
FROM inserted
go

real time progress bar

The ETL processing for my project will be deployed as part of a larger application which is operated by non-technical users. Therefore I want to provide real-time feedback to the user on progress, errors, etc., using windows standard GUI interface objects and style- for example a progress bar. The "designer" of course does some of this but there won't be any designer in our deployed application (and SSIS designer is neither intended to be used by, nor appropriate for, non-technical users anyway).

Now that I have the ETL logic working, it's time to tackle this UI requirement. I am thinking one way to do this is to start a script task to run in parallel with the main ETL processing, open a winform in that task that has various GUI objects whose "state" is based on package scoped variables, update the package level variables via the "on progress" event (or other events as needed) of various SSIS tasks and components, and refresh the winform's display regularly via a timer event.

Does this approach seem like it would be effective? Has anyone tried maintaining an open winform via a script task and updating it's objects via package variables in parrallel with the package is running other tasks?

Thanks,

Ken

This is definitely NOT the way to go. Launching a GUI from a Script Task would be very cumbersome and error prone and would require that SSIS is installed on the end user machines.

A much better solution is to execute the package from your own program. Books On-line has documentation on how to do this, look up "Managing Packages Programmatically".

<shameless_plug>You can also check out Professional SQL Server 2005 Integration Services. It has a chapter dedicated to manipulating SSIS from external applications.</shameless_plug>

[Edit]
After reading your post again, you definitely can't do it from a Script Task. Your say your requirements are for the end-user to not have the designer. However, if you launch any GUI elements from a script, they will load on the machine the script is run on. So if you're running on a server, it will get the screens. You'll also probably be running it under a system account so no one will see the GUI at all. I alluded to this in the first paragraph but I wanted to make it clear as to why.
|||

Hi Jason,

Thank you for you input. These SSIS packages are hosted on the same machine as the application that the users are running. BIDS is not deployed on those boxes and we have already been successfully opening winforms from SSIS on these boxes in order to retrieve some parameters for the ETL from the users at runtime. So far no problems. It is convenient for our project to provide one box to the remote sites which houses all required software to run the application.

re:<<GUI from a Script Task would be very cumbersome and error prone >>

This seemed easy to me when I programmed it. All I did was create a pretty winform in VB.net express edition using the wsywig designer, and copy and paste all the generated code directly into a script component in SSIS. Then I only had to point the GUI component's source properties at my package variables.

Since I already have this GUI running successfully, I am thinking the easiest way to provide run time feedback to the user is to have that winform stay open and have it reference additional package variables that are updated via SSIS events.

I suppose another way to do this would be to have the SSIS package persist progress information to an external data store, and run a seperate winform process to query that data and display to the user. This approach has the advantage as you point out of being hosted on a different box, independant of the SSIS box, but would be a more complex solution than just having a winform access variables directly from within the package. Then again it has another advantage that all the progress info for an ETL run would be persisted for auditing and archival reference. Theoretically if that data is all persisted along with the time that each event was logged, you would then have the ability to also "replay" the runtime display at any time. That sounds interesting...

real time progress bar

The ETL processing for my project will be deployed as part of a larger application which is operated by non-technical users. Therefore I want to provide real-time feedback to the user on progress, errors, etc., using windows standard GUI interface objects and style- for example a progress bar. The "designer" of course does some of this but there won't be any designer in our deployed application (and SSIS designer is neither intended to be used by, nor appropriate for, non-technical users anyway).

Now that I have the ETL logic working, it's time to tackle this UI requirement. I am thinking one way to do this is to start a script task to run in parallel with the main ETL processing, open a winform in that task that has various GUI objects whose "state" is based on package scoped variables, update the package level variables via the "on progress" event (or other events as needed) of various SSIS tasks and components, and refresh the winform's display regularly via a timer event.

Does this approach seem like it would be effective? Has anyone tried maintaining an open winform via a script task and updating it's objects via package variables in parrallel with the package is running other tasks?

Thanks,

Ken

This is definitely NOT the way to go. Launching a GUI from a Script Task would be very cumbersome and error prone and would require that SSIS is installed on the end user machines.

A much better solution is to execute the package from your own program. Books On-line has documentation on how to do this, look up "Managing Packages Programmatically".

<shameless_plug>You can also check out Professional SQL Server 2005 Integration Services. It has a chapter dedicated to manipulating SSIS from external applications.</shameless_plug>

[Edit]
After reading your post again, you definitely can't do it from a Script Task. Your say your requirements are for the end-user to not have the designer. However, if you launch any GUI elements from a script, they will load on the machine the script is run on. So if you're running on a server, it will get the screens. You'll also probably be running it under a system account so no one will see the GUI at all. I alluded to this in the first paragraph but I wanted to make it clear as to why.|||

Hi Jason,

Thank you for you input. These SSIS packages are hosted on the same machine as the application that the users are running. BIDS is not deployed on those boxes and we have already been successfully opening winforms from SSIS on these boxes in order to retrieve some parameters for the ETL from the users at runtime. So far no problems. It is convenient for our project to provide one box to the remote sites which houses all required software to run the application.

re:<<GUI from a Script Task would be very cumbersome and error prone >>

This seemed easy to me when I programmed it. All I did was create a pretty winform in VB.net express edition using the wsywig designer, and copy and paste all the generated code directly into a script component in SSIS. Then I only had to point the GUI component's source properties at my package variables.

Since I already have this GUI running successfully, I am thinking the easiest way to provide run time feedback to the user is to have that winform stay open and have it reference additional package variables that are updated via SSIS events.

I suppose another way to do this would be to have the SSIS package persist progress information to an external data store, and run a seperate winform process to query that data and display to the user. This approach has the advantage as you point out of being hosted on a different box, independant of the SSIS box, but would be a more complex solution than just having a winform access variables directly from within the package. Then again it has another advantage that all the progress info for an ETL run would be persisted for auditing and archival reference. Theoretically if that data is all persisted along with the time that each event was logged, you would then have the ability to also "replay" the runtime display at any time. That sounds interesting...

real time progress bar

The ETL processing for my project will be deployed as part of a larger application which is operated by non-technical users. Therefore I want to provide real-time feedback to the user on progress, errors, etc., using windows standard GUI interface objects and style- for example a progress bar. The "designer" of course does some of this but there won't be any designer in our deployed application (and SSIS designer is neither intended to be used by, nor appropriate for, non-technical users anyway).

Now that I have the ETL logic working, it's time to tackle this UI requirement. I am thinking one way to do this is to start a script task to run in parallel with the main ETL processing, open a winform in that task that has various GUI objects whose "state" is based on package scoped variables, update the package level variables via the "on progress" event (or other events as needed) of various SSIS tasks and components, and refresh the winform's display regularly via a timer event.

Does this approach seem like it would be effective? Has anyone tried maintaining an open winform via a script task and updating it's objects via package variables in parrallel with the package is running other tasks?

Thanks,

Ken

This is definitely NOT the way to go. Launching a GUI from a Script Task would be very cumbersome and error prone and would require that SSIS is installed on the end user machines.

A much better solution is to execute the package from your own program. Books On-line has documentation on how to do this, look up "Managing Packages Programmatically".

<shameless_plug>You can also check out Professional SQL Server 2005 Integration Services. It has a chapter dedicated to manipulating SSIS from external applications.</shameless_plug>

[Edit]
After reading your post again, you definitely can't do it from a Script Task. Your say your requirements are for the end-user to not have the designer. However, if you launch any GUI elements from a script, they will load on the machine the script is run on. So if you're running on a server, it will get the screens. You'll also probably be running it under a system account so no one will see the GUI at all. I alluded to this in the first paragraph but I wanted to make it clear as to why.
|||

Hi Jason,

Thank you for you input. These SSIS packages are hosted on the same machine as the application that the users are running. BIDS is not deployed on those boxes and we have already been successfully opening winforms from SSIS on these boxes in order to retrieve some parameters for the ETL from the users at runtime. So far no problems. It is convenient for our project to provide one box to the remote sites which houses all required software to run the application.

re:<<GUI from a Script Task would be very cumbersome and error prone >>

This seemed easy to me when I programmed it. All I did was create a pretty winform in VB.net express edition using the wsywig designer, and copy and paste all the generated code directly into a script component in SSIS. Then I only had to point the GUI component's source properties at my package variables.

Since I already have this GUI running successfully, I am thinking the easiest way to provide run time feedback to the user is to have that winform stay open and have it reference additional package variables that are updated via SSIS events.

I suppose another way to do this would be to have the SSIS package persist progress information to an external data store, and run a seperate winform process to query that data and display to the user. This approach has the advantage as you point out of being hosted on a different box, independant of the SSIS box, but would be a more complex solution than just having a winform access variables directly from within the package. Then again it has another advantage that all the progress info for an ETL run would be persisted for auditing and archival reference. Theoretically if that data is all persisted along with the time that each event was logged, you would then have the ability to also "replay" the runtime display at any time. That sounds interesting...

readxml doesnt work :(

hello!
i'm working on a project for pocket pc, which is an app that registers customers, suppliers, stocks and orders. all this is stored in the ppc itself using the SQL CE server.
i'm programming with VS2005, which is great, the tableadapters make the work lot easier.
i'm just having some problems importing and exporting data from the sql tables to files.
the export function works quiet well, i just load the data from the tableadapter into a table and then use the WriteXml(file) function.
Now, for importing I do more or less the same thing:
DataSetrTableAdapters.Customers cta = new ... etc etc
DataSetr.CustomersDataTable cdt = new DataSetr.CustomersDataTable();
cdt.ReadXml("\\My Documents\\customers.xml");
cta.Update(cdt);
result: empty table! :(
i also tried adding cta.Fill(cdt) before the update, but the result is the same....
any suggestions?
thank you very much

Hi,

The problem is that WriteXml writes the entire state of the dataset, including row state (added, modified, unchanged, etc.). When you call Fill, you add all rows to the DataSet from the table and Fill will automatically call AcceptChanges to mark all the rows as unmodified. So when you read it back from the file and call Update nothing happens - that's because the data-adapter doesn't see any row that needs processing.

I'm not sure what you're trying to achieve when reading from a file. Do you expect that those rows are *added* to the database, or you expect something more like a "merge" operation between the contents of the file and the contents of the database?

If you want to add the rows, then you have to options:

1) Avoid calling AcceptChanges on Fill. There is a property in the adapter called AcceptChangesDuringFill that you can use for that.

2) Alternatively, after loading the file into a DataSet, you can iterate over the rows and call SetAdded() on each row.

Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corporation

|||i've been searching for this problem , but every page i read its about datasets.
i'n not using datasets, but datatables. the readxml is in the datatable class, not dataset. did you notice?
i want to import data to the sql server, and wipe out everything that is there. so i'll probably clean all the tables first, if necessary. after that i read from the xml and save everything again to the tables.

|||

Hi,

Actually, everything I mentioned applies to DataTable as well, including both approaches to ensure that the rows are marked as added.

If your goals is to wipe the table in the database and import the contents of the file, then you can simply first clean up the table and then use any of the options I mentioned in the previous post.

Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corporation

|||

I have the same problem.

I have a dataset, I want to fill one table in the dataset using the xml data and other table is filled by the adapter from DB. Actually the DataTable.ReadXml(filename) is not working. Am I missing anything in the XML data file. Pablo, Can you give me an example. Thanks.

Steven

readxml doesnt work :(

hello!
i'm working on a project for pocket pc, which is an app that registers customers, suppliers, stocks and orders. all this is stored in the ppc itself using the SQL CE server.
i'm programming with VS2005, which is great, the tableadapters make the work lot easier.
i'm just having some problems importing and exporting data from the sql tables to files.
the export function works quiet well, i just load the data from the tableadapter into a table and then use the WriteXml(file) function.
Now, for importing I do more or less the same thing:
DataSetrTableAdapters.Customers cta = new ... etc etc
DataSetr.CustomersDataTable cdt = new DataSetr.CustomersDataTable();
cdt.ReadXml("\\My Documents\\customers.xml");
cta.Update(cdt);
result: empty table! :(
i also tried adding cta.Fill(cdt) before the update, but the result is the same....
any suggestions?
thank you very much

Hi,

The problem is that WriteXml writes the entire state of the dataset, including row state (added, modified, unchanged, etc.). When you call Fill, you add all rows to the DataSet from the table and Fill will automatically call AcceptChanges to mark all the rows as unmodified. So when you read it back from the file and call Update nothing happens - that's because the data-adapter doesn't see any row that needs processing.

I'm not sure what you're trying to achieve when reading from a file. Do you expect that those rows are *added* to the database, or you expect something more like a "merge" operation between the contents of the file and the contents of the database?

If you want to add the rows, then you have to options:

1) Avoid calling AcceptChanges on Fill. There is a property in the adapter called AcceptChangesDuringFill that you can use for that.

2) Alternatively, after loading the file into a DataSet, you can iterate over the rows and call SetAdded() on each row.

Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corporation

|||i've been searching for this problem , but every page i read its about datasets.
i'n not using datasets, but datatables. the readxml is in the datatable class, not dataset. did you notice?
i want to import data to the sql server, and wipe out everything that is there. so i'll probably clean all the tables first, if necessary. after that i read from the xml and save everything again to the tables.

|||

Hi,

Actually, everything I mentioned applies to DataTable as well, including both approaches to ensure that the rows are marked as added.

If your goals is to wipe the table in the database and import the contents of the file, then you can simply first clean up the table and then use any of the options I mentioned in the previous post.

Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corporation

|||

I have the same problem.

I have a dataset, I want to fill one table in the dataset using the xml data and other table is filled by the adapter from DB. Actually the DataTable.ReadXml(filename) is not working. Am I missing anything in the XML data file. Pablo, Can you give me an example. Thanks.

Steven