404: Page not found

I got to work on something cool today. Windows Installer XML toolset (WiX) is a set of tools and specifications that help you build Windows installation packages (.msi and .msm) from an XML document. WiX is released by Microsoft under the Common Public License.
WiX brings into setups, the traditional compile and build model to create executables from a source code file. It is written in C# and requires the .NET Framework to be installed on your machine.

A google search on WiX yields a couple of good resouces that tell you what exactly it is, so I am not going to delve much into that. I spent quite a bit of time today trying to get this thing to create an installer that would execute a TSQL during the installation when I found that WiX has a built in support for executing TSQL scripts directly without you having to invoke the sqlcmd utility. (In normal circumstances you would include a custom action in your setup project that would execute scripts using sqlcmd). Obviously, you can do a lot more in WiX than just that.

A good practical scenario of this requirement is when you have to create patches and bugfixes to an existing hosted build of your application. The advantage of creating a patch in the form of an msi file instead of an exe is that an msi is an installer database file which is tracked by your operating system. Later on, you can easily find out what patches you have applied simply by going to you add/remove programs menu.

Anyway, coming back to my purpose of writing this post; there are hardly any good resources online that give you a complete working example and/or tell you what exactly to do when you have to deal with SQL scripts while using WiX. Here is a sample working .wxs file that creates an msi. When the msi is installed, the TSQL script in two .sql files are executed.

<?xml version=”1.0″ encoding=”UTF-8″?>
<Wix xmlns=”
http://schemas.microsoft.com/wix/2003/01/wi“>
<Product Id=”24B6D4E2-2FC2-448F-B4E3-C7FBC8790C50″ Name=”MyProduct” Language=”1033″ Version=”1.0.0.0

” Manufacturer=”kvsoft”>

<Package Id=”3B54E578-A304-437E-91A8-4299246848DE”
Description=”Description of your product” Comments=”This will appear in
the file summary stream.” InstallerVersion=”200″ Compressed=”yes” />
<Media Id=”1″ Cabinet=”Product.cab” EmbedCab=”yes” />
<User Id=”SqlUser” Name=”sa” Password=”somepassword”></User>
<SqlDatabase Id=”MySqlDatabase” Database=”TryOuts” Server=”krishnamurthyk” User=”SqlUser” /><Directory Id=”TARGETDIR” Name=”SourceDir”>
<Directory Id=”ProgramFilesFolder”>
<Directory Id=”INSTALLLOCATION” Name=”MyAppDir” LongName=”My Application Directory”>

<Component Id=”ProductComponent” Guid=”AA0225A7-F189-47CC-9D22-D84A577BB431″><SqlScript Id=”CreateTableScript” BinaryKey=”DatabaseScriptBin”
ExecuteOnInstall=”yes” SqlDb=”MySqlDatabase” />
<SqlScript Id=”SecondScript” BinaryKey=”SecondScript” ExecuteOnInstall=”yes” SqlDb=”MySqlDatabase”>< /SqlScript>
</Component></Directory>

</Directory>
</Directory>
<Binary Id=”DatabaseScriptBin” src=”Test.sql” mce_src=”Test.sql” />
<Binary Id=”SecondScript” src=”SecondScript.sql” mce_src=”SecondScript.sql”/><Feature Id=”ProductFeature” Title=”Feature Title” Level=”1″>
<ComponentRef Id=”ProductComponent” />
</Feature>
</Product>
</Wix>

Today I was repeatedly getting an error:

Unresolved reference to symbol ‘CustomAction:ConfigureSql’ in
section ‘Product:0780EA99-9329-47AD-8CF5-C2274939FC85′.

This error disappears when I comment the SqlDatabase tag. The problem, as it turned out was funny: a missing reference. In order to get this working, you need to include a reference to sca.wixlib if you are using the Visual Studio WiX project.

If you are working with the command line utilities, then this is what you do:

> candle.exe Product.wxs
At this step, you will have an intermediate file called Product.wixobj.

> light.exe -o MySetup.msi Product.wixobj “E:/WiX/sca.wixlib”

Voila, you have the require msi file. I hope this helps all the developers out there.
Maybe when I learn more of this, I would do a more descriptive tutorial.

Reporting

Information is supposed to be the second most important resource of any business (the most important resource being “people”). Having relevant information at the right time makes all the difference while making important business decisions. When the size of the business organization is small, maintaining business data and extracting the required information is a fairly simple task. But consider an organization the size of a huge corporate, with businesses spread across the world, working in different domains, maintaining several huge databases containing millions of records.
So, there you go; you have all the data you need, but there is this small catch: the data is spread across multiple databases, flat files, excel sheets, CSV files. The databases don’t have a uniform architecture, as they were designed to serve a very specific purpose: store some very relevant business data.
What I mean by this, is that it takes a whole lot of re-organization of the raw data so you can put it to use, and derive any sensible conclusions regarding your business statistics. Most business databases store data directly related to the business operations, and any report generation has to extract the data from these databases and convert them into a suitable format before the reports can be generated.
The reports are the end product of the entire report generation process, which gives the business enterprise all the facts and figures that enable them to take the appropriate business decisions.
One might ask, “why is that such a big deal? Isnt that why the databases are there? Just run an SQL query and you get all the data that you want”
Point well taken, and for a simple enterprise with small busines operations, this might work fine. But we are talking about a different situation here. What works for a small organization, in this case does not scale well when the case is extended to a huge business enterprise like the one described above. It may not be fasible to run a distributed query across multiple databases, data files, excel sheets, and CSV files. The kind of processing power and time required to complete such an operation would be simply not feasible. Moreover, any small change in any of the data sources will bring the entire process crashing down.
So, the point is that all the data needs to be consolidated into a central repository, into a suitable format, from where it can be queried, in order to improve the processing and time efficiency. Aha, we are talking about a data warehouse.
Another important consideration is that delivering reports is more than just processing data through queries and making it available. It is providing information when you need it, on any platform, in any format. A common solution is generating reports in HTML format and viewing it on an internet browser. This is fine if you have a machine that can connect to a server. But you may need reports when you are on a flight, when you are with the customer, when you are in your car. This includes platforms like your pager, your PDA, you fax machine, your laptop.
What if you need those reports in a suitable format like PDF, CSV, TIFF or XML for further analysis. You may even need the reports in some custom extension/format. It is not enough. Even now, a single format and a single delivery channel may not be enough. How would you know which ones you will need at a particular point of time?

Enter Reporting Services

To take care of all these issues, we have Reporting Services. Reporting Service provides you a layer of abstraction and hides all these details from you. With a reporting server, all the reports may be rendered in any of the above specified formats and may be delivered on any channel.
Reporting services provide a clear separation between the report design and the rendering of the reports in a desired format and the way in which it will be consumed.
Report Designing and Generation
Thus the entire process of report generation and delivery consists of two separate processes: Report designing, and report generation & delivery. Both these processes are independent of each other. Hence, a report designer need not worry about the format report will be rendered in, or the channel/platform where it will be delivered.
Similarly, the report generation process does not need to know how the reports are designed. All it needs to do is pass the relevant parameters that are required as inputs for the report generation process. This report generation process renders the report in the desired format and finally delivers it to the end user.

SQL Server Reporting Services

SQL Server Reporting Services, also known as SSRS comes as a part of the SQL Server 2005 suite free of cost. The SSRS includes all the development and management modules needed to create/define reports, publish them on to a reporting server, and render them in HTML, PDF, EXCEL and CSV formats. Whats more, you can even develop your own custom format and register it with the SSRS.

As you can see from the diagram, the SSRS installs a dedicated web service on your machine. This web service has an interface which can be accessed from http://reportServerName/Reports
The SSRS provides a full-featured set of APIs that you can use to integrate Reporting Services with custom business applications. This includes the full featured web service that provides access to the complete functionality of the reporting server. The SSRS can use any data source like an SQL Server database, or an OLE DB data source as the primary database from which the reports will be generated.
:To understand all this better, lets just consider the entire reporting life cycle:
Report Authoring: Developers can define reports to be published on to the Reporting Server using a report design tool. One popular report design tool is proided by the Business Intelligence Development Studio (BIDS) which is installed along with the full suite of SQL Server 2005. These report design tools use the Report Definition Language (RDL) which is an XML based industry standard. The term report in this case might sound a little ambiguous, as this is not really a business report; but simply an XML file which specifies the connection string to the datasource to be used, the query to be executed, and other metadata. There is a corresponding Report Data Source (RDS) file which has the connection string to the real data source. The RDS and RDL files are then deployed on the reporting server. To deploy these files, you can use the BIDS tool, or do it programmatically using the web methods provided by the report server web service.
Report Generation and Delivery: Once the RDL and RDS files are deployed, the reports can be generated on request or as a response to a particular event. Reports can be viewed using the web interface provided for the report server i.e. http://reportServerName/Reports . Reports can also be generated and rendered in the desired format using the web methods provided by the report server web service.
Report Management: Administrators can use the Management Studio to organize reports and data sources, schedule report execution and delivery, and track reporting history. Reports can be executed on demand or on a specified schedule.
In order to get reporting services up and running on your machine, do a default installation of SQL Services 2005 and let the installation configure the reporting service for you. Once the installation is completed, point your browser to: http://localhost/Reports, and you will be greeted by something like this:

Click on the project1 folder and you will see the reports deployed. Clicking on a report runs it and the report generated can been seen on the page. You can then export it in any of the supported formats.
An introduction to SSRS programming can be found at:
http://msdn2.microsoft.com/en-us/library/ms155076.aspx