SQL Server 2005 Integration Services and Report Builder April MSDN Q and A

If you have a question that you didn't get around to asking or thought of afterwards from the SQL Server April 05 MSDN Update then please feel free to post it against this blog item and I'll do my best to answer it. 

I'll also post the answers to questions I couldn’t answer on the day, so the first of these questions are:-

Q1) Can a Report Builder report "Prompt" a user for input at run time?
A1) Yes, when set up a filter using a Field (not an Entity) you can click on the field name you dragged on to the filter surface and there you'll spot an option called "Prompt"

Q2) How do you control the date formatting?
A2) You control the format of the date displayed in a Report Builder report from the Data Model you set up from within Business Intelligence Studio

Q3) How can I embed a Report Server Report in my Web or Windows Forms Application
A3) There are two new "ReportViewer" controls released with Visual Studio 2005 Beta 2 (all versions except Express), one for ASP.NET V2 and the other for Windows Forms Apps.  These controls won’t ship in V2 of the .NET Framework or with SQL 2005 so you will need to deploy them with your client or web application.  You don’t need a SQL 2005 License to use them, they have a local mode that is not dependent on Reporting Services or SQL, but when you use them against a Report Server it must be the version that ships with SQL Server 2005. (Caveat - this may change but this is the story for now)

So if you created a report with Report Builder and saved it to the default location and called it "Pie" then you'd set the ReportServerURL to http://localhost/reportserver and the ReportPath to /Pie

There’s a great blog on using the ReportViewer controls at http://odetocode.com/Articles/128.aspx

Q4) How would you get access to an sql mail object's properties from within ssis 2005 to change dynamically for example the TO or SUBJECT line?
A4) This works in a very similar fashion to the Foreach example I posted at http://blogs.msdn.com/dglover/archive/2005/04/14/408098.aspx.  You'd just set package or container variables (depending on scope requirements) with the address info (to, cc, bcc), subject info etc, then expand the Expressions property of the "Send Mail Task" and set the TO and Subject to point at these variables.  Hope that makes sense?


Comments (7)

  1. Mike says:

    Hi, how would you get access to an sql mail object’s properties from within ssis 2005 to change dynamically for example the TO or SUBJECT line?

    This was easily accomplished in DTS 2000.


  2. Jessica says:

    I was interested in the version control features of SQL Server 2005, if any.

  3. Dave Glover says:

    Have added the question about the To and Subject to the main Q&A on this blog item.

  4. Dave Glover says:

    Hi Jessica, could you please be a bit more explicit about what you’re wanting to version control. Is it a SSIS package, in which case Visual Source Safe or Team System, pretty sure the same for Stored procs, UTD etc… Regards, Dave

  5. Jessica says:

    Hi Dave,

    We’re looking to version control our stored procedures, triggers, functions, DTS packages and tables. To prevent changes made to any of these being overwritten. So it would need to be comprehensive version control. I believe Yukon provides this feature for DTS but was unsure of the other capabilities…

Skip to main content