SSIS Row Count Component...

Recently I used the Row Count component in SSIS toolbox. I used it in a Data Flow to set the row count of a Lookup match output. When I tried to get that set value in a Conditional Split component in the same Data Flow, I found that the variable has the default value and the row count was not set for that variable.

But when I get the variable value using Script Task, I found that the variable has the row count.
Actually the issue is the row count value is available after the completion of the Data Flow task. We cannot access the variable inside the same data flow task which sets the row count value.

Due to that I have to use two Data Flow tasks. In the first data flow task I set the row count value to a variable using Row Count component and in the other data flow task, the variable value is checked and do the required processing.

Replace double quotes in SSIS...

Recently I had a requirement to load csv file data which is generate from FoxPro.
But when I checked the csv file, found that it has additional double quotation mark at the start and the end of the value. As an example if the actual branch value is Branch1, the csv file has that as "Branch1".

Therefore to get the actual value I had to remove those double quotation mark and able to do it using REPLACE function.

I added a Derived Column component to the package and in that replace the branch column value after removing double quotes. For that I used the below expression.

   1: LTRIM(RTRIM(REPLACE([branch],"\"","")))

That is it, It will give you the actual branch value correctly as Branch1.

How to show the All Member hierarchy value in a SSRS Filter...

For some filters we may need to select all the values at once. As an example if the SSRS parameter is a single valued one and user should be able to filter all the values if needed, then we need to provide All member value in the filter as well. We can achieve this easily by retrieving the all member value to the data set of the filter.

For an example we can write the query for the Branch data set, which loads the all the branches available in the [Sales-Branch] hierarchy as

   1: WITH MEMBER [Measures].[ParameterCaption] AS [Sales-Branch].CURRENTMEMBER.MEMBER_CAPTION

   2: MEMBER [Measures].[ParameterValue] AS [Sales-Branch].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Sales-Branch].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , {[Sales-Branch].[All],[Sales-Branch].[Branch].MEMBERS} ON ROWS  FROM [Sales]

In that you can see the ParameterLevel for the top record as 0 and indicates it is the parent level. Therefore it is the all branches record and it is given by the [Sales-Branch].[All] in the mdx query.