Archive for June, 2009

I have a list of existing packages from the client that I would like to test. In order to read them, I have to add them into an SSIS project. Hence, I created a n ew SSIS project in MS Visual Studio. When trying to add one of the packages, it failed with the error that says :
    “Failed to save package file “C:\SSIS\…. .dtsx” with error 0xC001405E “The protection level of the package requires a password, but PackagePassword property is empty”.
I went back to check the properties of the SSIS package and found out that the ProtectionLevel security property of the package has been set to EncryptSensitiveWithUserKey by default and that the PackagePassword property is empty. Once the ProtectionLevel was changed to DontSaveSensitive (it means it won’t require a password to open or edit the package), I was able to open the package.


Read Full Post »

Say I have the following resultset and would like to get the LATEST distinct  value of the closing dip for each LocationId for December 2008.
As you can see, I only want to pick rows inside the red rectangulars and then sum them up to get the total of the Closing Dip for the December 2008. To do that, I use the window function feature in SQL Server 2005, RANK() OVER (PARTITION BY … ORDER BY … ). This function returns the rank of each row within the partition of a result set.


DISTINCT Date, LocationId, [Closing Dip], RANK() OVER (PARTITION BY LocationId ORDER BY Date DESC) AS Rank




BY Rank


As you can see now, each row has been ranked and the rows that I am interested in are those with rank = 1. So to get the total of the Closing Dip for December 2008, I wrote another query, which will give me 69,750 as the total of the Closing Dip in this example.


SUM([Closing Dip])


    ( SELECT DISTINCT Date, LocationId, WorkPeriodSeqNum, [Closing Dip], RANK() OVER (PARTITION BY LocationId ORDER BY Date DESC) AS Rank

      FROM #temp3

    ) tmp


Rank = 1


Read Full Post »

%d bloggers like this: