(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

• consist of several data sources which have to be combined in one dataset
  • you cannot use linked server for various mentioned reasons
  • you cannot use other connection technologies like OPENROWSET/OPENDATASOURCE
  • you do not have SQL Server at all as a data storage for your data source, e.g. source is a text file
  • (…)
• use external resources like .Net Assemblies
  • you cannot use or want to use .Net Assemblies in your SQL Server as installed assemblies
  • The level of access you need does not allow you to use the assembly in SQL Server (as per company restrictions)
  • (…)
• mimic calculations / conversions or other stuff which already exists in SSIS
• you need to do adhoc calculations or want to display data which are not persisted and you cannot achieve in SSRS

You should not use the solution if:

• you don’t want to use SQL Server Integrations Services (please tell me why ! :-) )
• you have reports that do not allow any latency of executing a Integration Service package and you would be quicker with easier solutions

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

-Jens