(Update: Due to security reasons the functionality retrieving data from SSIS packages has been deprecated and is no longer available in SQL Server 2008 R2)
There are several scenarios where you want to extract data from different data sources and combine them within a report.
Unfortunately if you have structure like a Table or Tablix, you can only bind the objects data source to only one dataset. This is fine for queries using one data source, but imagine you want to grab data from several data sources. You might think that this is also possible in SQL Server by using either OPENROWSET, OPENQUERY or even linked server. Yes - you are right, but beside the administrative hassles you would have to go through by convincing the admin to create those LINKED Servers and maintain them through the lifecycle of the database you sometime reach the limits of queries that can be done in TSQL like complex calculations including external DLLs like .Net Framework Assemblies. Sure you can also extend your database with these assemblies, but here were are again at the administrative part and the arguments you will have to pull up for the admin to install and manage them (and turn on the CLR integration on the server)
As a intermediate summary:
You can use the solution if you want to consume data in the report which
You should not use the solution if:
If you see yourself in these points mentioned or are just curious for the read watch out for the next post I will be doing about this.
Update: Here is part 2