SQL 2005

Hi All,
It’s been awhile since I promised to post more about SSIS and SSAS and I haven’t done so :(…been a bit busy lately in the office dealing with Reporting Services issue.
I picked up few tips that I thought worth to share:

  • When you have multiple datasets in your report, make sure you tick “Single Transaction” option in your dataset, otherwise you will receive a call from your DBA telling that you have too many connections 🙂
    So, if you don’t think that option, SSRS will establish database connection as many as your dataset, even you ONLY open the report (not preview it).
  • If you have an expression like below:
    =CountDistinct(Fields!myField.Value, “myDataset”)
    , just keep in mind that the red color part is case-sensitive.

Now back to my promise about SSIS and SSAS:
I am aiming to post example of each task in SSIS 2008 and some explanation with my best knowledge.
As for SSAS – I will try to post some mini example but reflected on real world scenario with SSAS 2008 and exploring new feature.

Till next time.


Hellow…just received email from Microsoft download about long-await SQL Server 2005 SP3, but it still CTP at the moment. Download it from here.

Yesterday, a colleague came to me and said he can run MDX query but can’t see the OLAP database itself on the server via Management Studio. I thought this is strange since he belongs to one of the role on the database. After awhile, another came with the idea which later on solved the issue. Apparently in the role, under “Data Sources” section, the “Read Definition” option is uncheck.
This option is important because without it, the role member can’t read full definition of OLAP database metadata.