Archive for May, 2009

I copied and pasted an SSIS 2005 dtsx package into a different folder then opened it in the MS Visual Studio/BIDS 2005. I then changed the connection managers to point to different data source/destination. The package was executed successfully, but it ended up updating data in the previous data source/destination. This happened because the package was still using the previous global configuration file. To change it, go to Control Flow of the package, right click anywhere empty then choose "Package Configuration.." option. Point the global configuration file to the correct one. Executed the package again and it updated the correct data in the data mart. Note: if the package is still pointing to incorrect data source, check the XML configuration file to ensure that it is in fact pointing to the correct data source; if not change it.

Read Full Post »

I encountered this error thrown by Microsoft Visual Studio while trying to switch from Control Flow to Data Flow while running one of the SSIS dtsx packages on a client data mart. I clicked OK to close the error window and switched back to Control Flow to see the progress of the ETL. I saw that the last step has turned green in the Control Flow but most transforms were still yellow in the Data Flow. The Data Flow task was yellow in the Control Flow and the 2 tasks after the Data Flow task didn’t turn yellow or green but the last task was already green. How odd!. The package said "Running" but the row count remained the same in the data flow. It looked like Visual Studio had freezed and therefore failed to refresh .`
I checked the number of rows in the Error bucket table and the destination table and discovered that there were about 8 rows in the Error bucket table and over 2,5 millions of rows in the destination table, which were expected.  I concluded that most likely the ETL process has finished. I then killed the Visual Studio process to stop the package. Check the data again and it looks fine.

Read Full Post »

Let say I have the following table that has 2 table groupings and has the ability to drill down.

The formula to get the Total Variance value:
              Total Variance = Total Dip Variance for MOBILETANK + Total Dip Variance for TANK
,which is  -9,186 + (-4097) = -13,283. This is the result that I would like to achieve.

I used the following expression to get the total dip variance value for each type, which produced correct values.

=IIF(Fields!Type.Value = "TANK"
        , Sum(Fields!Last_Day_Dip_Reading_Level1.Value) – (Sum(Fields!Previous_Day_Dip_Reading.Value) + Sum(Fields!Deliveries.Value) – Sum(Fields!Transfers.Value) – Sum(Fields!Issues.Value))
        , Sum(Fields!Last_Day_Dip_Reading_Level1.Value) – (Sum(Fields!Previous_Day_Dip_Reading.Value) + 
Sum(Fields!Transfers.Value) – Sum(Fields!Issues.Value))

I then used the above expression to get the Total Variance value but it produced incorrect value. The reason behind this is that the Total Variance value is placed under table footer, which adds the whole items within the table. I also tried using calculated field on dataset; however, you cannot use any aggregrate functions such as Sum() on calculated field and hence cannot be used in this scenario. The other option that I could think of would be using VB functions in the custom code under Report Properties.

To resolve this:
I decided to put the above expression in custom code as follows:

— Declare global variables —
Public Shared Dim TotalVariance As Decimal
Public Shared Dim TankVariance As Decimal
Public Shared Dim  MobileTankVariance As Decimal

Public Shared Function AddVariance(ByVal Type As String, ByVal LastDayDipValue As Decimal, ByVal  PreviousDayDipValue As Decimal, DeliveriesValue As Decimal, TransfersValue As Decimal, IssuesValue As Decimal) As Decimal
     If Type = "TANK" Then
                    TankVariance =  LastDayDipValue – (PreviousDayDipValue + DeliveriesValue – TransfersValue – IssuesValue)
        Return TankVariance
           Else If Type = "MOBILETANK" Then
                      MobileTankVariance =  LastDayDipValue – (PreviousDayDipValue + TransfersValue – IssuesValue)
        Return MobileTankVariance
           End If
End Function

Public Shared  Function GetTotalVariance()
    TotalVariance =  TankVariance + MobileTankVariance
    Return TotalVariance
End Function

The Total Dip Variance for each Type field will have the following expression:
=Code.AddVariance(Fields!Type.Value, Sum(Fields!Last_Day_Dip_Reading_Level1.Value), Sum(Fields!Previous_Day_Dip_Reading.Value), Sum(Fields!Deliveries.Value), Sum(Fields!Transfers.Value), Sum(Fields!Issues.Value))

I then copied and pasted the same table just to display the Total Variance value on the report and the Total Variance field will have the following expression. Remember to hide table headings and remove table groupings as necessary.


Read Full Post »

I came across this error very often especially when deploying reports into the client environment. The error happens when one or more report parameters have default values that are empty.

To resolve this, unhide them and the report should prompt the user to select the report parameters before the report can be executed.

Read Full Post »

I am using a dundas chart with 3 series of data. When I click on the first series, it should pass the report parameters for the first series to the relevant subreport.
To do that, right click on the chart then choose Properties. Select "Data" tab then select the first series. Click the "Edit" icon on top then select "Actions" tab. You will see that the "Jump to report:" option is ticked. Click on "Parameters.." button. Change the value of the multi-value report parameter to use the following generic format:
               Join(Parameters!Field.Value, "\#\")

Read Full Post »

I came across this error while drilling through a report on client environment. My subreport failed to run when a dundas chart was clicked. Executing the relevant stored procedure producing this error. Running another SP for other report worked perfectly fine. I then compared the two SPs to see what the differences were and discovered that I have used 2 temporary tables in the report SP that produced this error.
This error happens when a temporary table is used and the TEMPDB database has different collation to the database.
To get a list of collation lists on SQL Server:


* FROM ::fn_helpcollations()  WHERE [name] LIKE ‘SQL_Latin%’;


Go to http://www.databasejournal.com/features/mssql/article.php/3302341/SQL-Server-and-Collation.htm to find out more about collation in SQL Server.

To fix this error, I explicitly forced the collation on the CREATE TABLE and comparison statement on the field(s) of CHAR/VARCHAR/NVARCHAR type in my query. Alternatively, change the default collation on the SQL Server, which requires master database to be rebuilt (http://www.sp-configure.com/change-sql-server-default-collation/).
CREATE TABLE #CostCodes (     
           CostCode          NVARCHAR(50)         COLLATE SQL_Latin1_General_CP1_CI_AS           NOT NULL )



   CASE WHEN c.CostCode IS NOT NULL THEN e.ActivityEventID

            ELSE NULL


FROM dm_date d

LEFT OUTER JOIN Core.fact_Equipment_events e ON e.DateId = d.date_key

AND e.SiteId = @SiteId

AND e.MeasureID = @Measure_CostRef

LEFT OUTER JOIN #CostCodes c ON c.CostCode COLLATE SQL_Latin1_General_CP1_CI_AS = e.TextValue COLLATE SQL_Latin1_General_CP1_CI_AS

WHERE d.date between @StartDate AND @EndDate


Read Full Post »

Say I have the following resultset and the Delivery No. depends on the Fuel Docket No. and the date the fuel was delivered. The Delivery No would be an incremental number for each delivery that is listed in the report. As you can see the row number next to the first Fuel Docket No is 35, and there were only 9 dockets in that month. Adding the following into the query will not produce the correct number.
                 row_number() OVER ( ORDER BY [Fuel Delivery Date]) AS [Delivery No]
Resolution: Only select fields that relate to the Fuel Delivery (i.e. Fuel Delivery Date, Fuel Docket No, Litres Received) from the query then add the row_number() OVER ( ORDER BY [Fuel Delivery Date]) AS [Delivery No] as [Delivery No] field. Remember also to filter the query so that any rows with NULL Fuel Delivery Date are not included. Place the resultset into a table variable then do a LEFT OUTER JOIN back to the main query.

— Get the Delivery No for each Fuel Delivery Date and Docket No.

INSERT INTO @ResultTemp3

SELECT DISTINCT row_number() OVER ( ORDER BY [Fuel Delivery Date]) AS [Delivery No]

,[Fuel Delivery Date]

,[Fuel Docket No]

,[Litres Received]

FROM @ResultTemp t

WHERE [Fuel Delivery Date] IS NOT NULL


— Final Result —-



,@TotOpeningDip AS [Opening Dip]

,t3.[Delivery No]

,t.[Fuel Delivery Date]

,t.[Fuel Docket No]

,t.[Litres Received]

,t.[Equipment Energy Category]

,t.[Report Category]

,t.[Total Litres Issued]

,t.[Litres On-Site]

,t.[Litres Off-Site]


,t.[Purchase Name]


,t.[Litres Sold]

,@TotClosingDip AS [Closing Dip]

FROM @ResultTemp t

LEFT JOIN @ResultTemp3 t3 ON t3.[Fuel Delivery Date] = t.[Fuel Delivery Date] AND t3.[Fuel Docket No] = t.[Fuel Docket No]

ORDER BY [Fuel Docket No] desc



Read Full Post »

Older Posts »

%d bloggers like this: