Change Environment Variables through Command Prompt

You can use the DOS Set command to change the Environment variables through Command Prompt.

If you wish to make the changes permanent use SetX command.

e.g. SETX ENV DEV

This will change the Enviornment Variable "ENV" to "DEV".

Delete logical duplicates using Ranking Function

A simple way to delete duplicate (not absolute duplicate records where records are completely identical) records from table using the ranking function and CTE is as follows.

The following SQL will remove logical duplicate records from the table tblTransaction.

If the table tblTransaction has multiple records for a combination of AccountId and RecordId the DELETE syntax will keep only the latest dates and delete all previous records for the combination.

WITH Transaction_CTE (Ranking, AccountId, RecordId, StartDate) AS (

SELECT Ranking = DENSE_RANK()

OVER(PARTITION BY AccountId, RecordId

ORDER BY StartDate ASC),

AccountId, RecordId, StartDate

FROM dbo.tblTransaction )

DELETE FROM Transaction_CTE WHERE Ranking > 1

Pass NULL String in the Conditional Expression

I have a SSIS package that reads data from a flat file and loads it into database table. The flat file should send valid values for all columns. However, I got the file with "NULL" written for integer values. The requirement was to store NULL values in the database column.

Passing NULL in SSIS is not straight forward. The MSDN article recommends using the expression NULL(DT_STR,10,1252) to return NULL.
http://msdn.microsoft.com/en-us/library/ms141758.aspx

This did not work for me. After some work, I figured that you have to again cast the NULL return value to string to make it work.
(DT_STR,10,1252)NULL(DT_STR,10,1252)

If you want to use this in Conditional expression, you can use the following format.
Pts_Earned == "NULL" ? (DT_STR,10,1252)NULL(DT_STR,10,1252) : Pts_Earned
The above expression means if it finds "NULL" string in the column Pts_Earned it will pass on NULL or else passes on the actual value.

Hope this helps.

New line in SSIS annotations

If you want a newline on your SSIS annotations, please hit Ctrl + Enter.

How it works : Analytics from IBMSocialMedia

IBM is investing a lot these days on Analytics.

The following video gives a very good overview of Analytics. Courtesy IBMSocialMedia.

Could not load file or assembly 'System.EnterpriseServices.Wrapper.dll

I have been struggling since a few days with the following issues on MS Analysis Services 2008. When I try and create a Data Source with the Provider "Native OLE DB\SQL Server Native Client 10.0" I was not able browse the list of databases. The list of databases always remained empty.

When I click on Test Connection I get the following error message "Test connection failed because of an error in initializing provider. Could not load file or assembly 'System.EnterpriseServices.Wrapper.dll' or one of its dependencies. The system cannot find the path specified".

After searching for the resolution on Internet, I found that I may have to reinstall my .Net Framework 2.0. Although, Windows will not allow me to remove my .Net Framework. It turned out that since I additionally had .Net Framework 3.0 and 3.5 installed, I had to remove them first before removing 2.0. And it worked..

After reinstalling .Net Framework 2.0 and all others I was able to browse the databases. Hope it helps...

Meanwhile, till the time I was not able to work with "Native OLE DB\SQL Server Native Client 10.0" I used ".Net Providers\SqlClient Data Provider"

Error "Unable to get the window handle for the 'ActionsAwarePivotTable' control."

I got the following error "Unable to get the window handle for the 'ActionsAwarePivotTable' control." while browsing the cube in SQL Server Analysis Services 2008.

It turns out that you get this error message when your Lotus Notes is up. The error went away after closing the Lotus Notes.

Wish there were better integration between IBM and Microsoft. :-)