Month: August 2012

OLEDB Destinations in SSIS and the dilemma with triggers!!

So I was sitting happily at my desk, loading data from some flat files into SQL Server tables using SSIS and minding my own business. I set up my OLEDB source. I set my OLEDB destination. I did all the transformations very carfully and I was excited because I thought this might be my first package which would run successfully the first time. And it did. I was a happy bunny. But you see, good things don’t just happen in life like that. There is always a caveat. You see, this particular destination table had insert/update triggers on it. Whenever a row is added/updated in this table, the trigger would fire and log the changes in the audit table. Sort of maintaining a history. However, and this is where it gets interesting, when I was populating it from SSIS, no trigger would get fired.  Normal insert would fire the trigger but not SSIS. Try it yourself. 

The culprit (or “solution” whichever way you look at life) was the Data Access mode in OLEDB destination. If you are using “Table or view – fast load”, the triggers won’t get fired. This is the default mode selected so this can be very easily missed. So for reference purpose, I have created the following table which lists all the Data Access modes and which one’s support triggers. This table is valid for SQL Server 2008, 2008 R2 and 2012. 

Data Access Mode  Supports Triggers
Table or view Yes
Table or view – fast load No
Table name or view name variable Yes
Table name or view name variable – fast load No
SQL command Yes

Be aware of those triggers next time!! 😀

Microsoft Surface 199$ gamble

So if the news on the web is true then the new tablet kid on the block AKA Microsoft Surface is going cheap. Engadget and CNet are reporting that it will be around $199. Surprised! I know I am. When it was launched my gut estimate was around $499 for lowest Windows RT model going up $1500 for professional level high end model. $199 look really puny and I look like a fool. So what might the reason behind the magical $199 mark (magical because Nexus,Kindle & Nook are at same price range). Most will agree that Microsoft will not make much money at $199 price tag. Google hardly makes anything from 7″ Nexus 7 and Surface is going to have 10.1″ LCD. So profits on hardware seem to be out of question. Which means there must be a long term motive and here’s what I think it is. 

Microsoft is new in the already crowded market. That includes both hardware and software. So to take a slice out of it, the viable option for them would be to sell their wares at cheap. The target would be the large not-so-niche market. This would increase the user base. If the user base increase, other manufacturers can follow the suite and launch their own versions of Windows tablets. That means more options for end users and more Windows tablets in the market. Microsoft can licence the Windows copy on each of them and earn revenue. Just like what it does currently. IMHO, that’s the ultimate goal. Besides you always have the app store revenues from selling apps, music, movies, games etc. If down the line Surface starts making profits, that’s an added bonus. Launching Surface at cheap is just to kick-off things.

Let’s see how this thing shapes up. 😀

Retrieving SSIS packages from SQL Server

SSIS packages, when deployed in SQL Server, are stored in the MSDB database. This is a very simple query to retrieve the package definition from SQL Server MSDB database. You would obviously need permissions on MSDB database to run it.

SQL Server 2012 , SQL Server 2008, SQL Server 2008 R2

SELECT [Name] = pckg.[name]
     , [Description] = pckg.[description]
     , [CreatedDate] = pckg.[createdate]
     , [DisplayFolder] = pckgfolder.foldername
     , [PackageXML] = CAST(CAST(packagedata AS VARBINARY(max)) AS XML)
FROM [msdb].[dbo].[sysssispackages] pckg
INNER JOIN [msdb].[dbo].[sysssispackagefolders] pckgfolder
ON pckg.folderid = pckgfolder.folderid

SQL Server 2005

SELECT [Name] = pckg.[name]
     , [Description] = pckg.[description]
     , [CreatedDate] = pckg.[createdate]
     , [DisplayFolder] = pckgfolder.foldername
     , [PackageXML] = CAST(CAST(packagedata AS VARBINARY(max)) AS XML)
FROM [msdb].[dbo].[sysdtspackages90] pckg
INNER JOIN [msdb].[dbo].[sysdtspackagefolders90] pckgfolder
ON pckg.folderid = pckgfolder.folderid

Copy the PackageXML column value for the required package (or you can just click on it to open in new window), paste in any text editor and save as .dtsx file.

Apple,Google or Microsoft. Pick up your partner.

Reading about all the smartphone news these days, I cannot help to come to conclusion that ultimately it’s coming down to three major players Apple, Microsoft and Google. I mean it’s not that hard to see, is it? And I am not talking about the hardware. No sir. I am talking about the more lucarative software side of things : the OS, third party applications, media, cloud etc. Why Microsoft? You might be thinking. Read along to find out why I think so.

Let’s begin with the big daddy of all, the Apple. It is indeed in a very safe position right now. What with 68% tablet market share ,cool profit margins on its hardwarehealthy iPhone sale and awesome app system. It’s going to be here for long time.Period. Get over with it. But Apple’s ecosystem is tightly integrated with its hardware. So unless Apple allows third party access to its ecosystem,there will always be room for other players. I mean not everybody is going to buy Apple products suddenly. Also, Apple products are expensive which gives chance to other players to get in the not-so-niche markets.  RIM is somewhat in the same situation. Software tightly coupled with hardware. Except that people are not buying into Blackberry promise.

I guess Google understood that long before I did (I suppose that’s why they are Google). Giving the hardware manufacturers a free OS that can compete with Apple’s iOS, painting Google over it and generating revenue from it not only by way of Ads but also from Apps & Media, is ingenious. That seemed to have paid off well. Andriod is doing well. It is second only to iOS  Infact it is doing better than iOS. Couple that with low cost and variety of Andriod phones and tablets and I think Google should be pretty happy with itself. I am no expert but I think Nokia missed a big opertunity with Symbian there. Can you imagine if Symbian OS would have been modified quickly and efficiently enough for touch based devices and rolled out to handset manufactureres. Most of them already used Symbian the switch could have been smoother I suppose. Nokia could have been where Google is today with Android. Anyway.

Coming to the point of why Microsoft then? First and foremost I personally admire what they have done with the Metro UI changes. Second, all the services that an mobile ecosystem needs are already there from Microsoft. They just need to take these and integrate it into a platform in a seamless way. Take email for example. We always had hotmail and now the new There is skydriveXbox Live for media & games and office online for documents. Third, number of PC user’s are much more than of any other users. Yes, I know, the recent Mac sales are much more than PC’s but average Joe still has a PC and is conversant with using it. So if all his devices start talking to each other over the air, seamlessly and he doesn’t have to pay a fortune for it, wouldn’t that be cool?Fourth, Microsoft has a large developer community. If the user base increases, the number of apps will surely increase. Fifth, enterprises all over the world use Windows and Microsoft solutions. It’s inevitable that they will start offering these on mobile platforms one way or another.

As you would have guessed I am really hopefull that Microsoft platform picks up. Surface looked awesome and turned out what I predicted it to be. Apple started it all, still leads it but comes at a premium price. Google took the idea and opened it to the mass market. Now I think its Microsoft’s turn to make use of it’s strong user base and create a very unique offering. I, for one, am with them.

Where are the reports coming from!!

All the operational systems eventually need some kind of analytic functionality. It can be static reports or ad-hoc analysis using an OLAP tool. This facet is often overlooked at the beginning of the project and rears its head after data has grown in the system. Suddenly everybody wants reports, summarised by x & y, with slice and dice capability and all the colourful charts you can think of. The schedule is very tight by this time and requirements very high. I can think of three approaches in such situations and ,in his blog, I would like to briefly compare them. Each one has its own merits and which one to use depends on many external factors such as budgets,resources, schedule, skills etc. I must say that the approaches discussed are heavily influenced by my experience with the SQL Server stack and there can be alternatives to them on other platforms. Lets first define what I mean in each approach.

1. The Traditional Route : This is the tried and trusted route. In a nutshell, we load data from operational source systems into a common staging area, run the Extract-Transform-Load (ETL) routines and load it into a data warehouse. The data warehouse can use Kimball, Inmon, Data Vault or any hybrid methodology. There is an analytics layer (OLAP) on data warehouse and reports run off this layer.

2. The Views Route : In the views route, we create views on the operational database. They can be SQL Server views or can be materialized as actual tables and populated periodically. I will not call it a data warehouse or even virtual data warehouse because we don’t combine data from various source systems or there is no extensive data cleaning and transformation. If you think about it they are simply a different view on the source data. OLAP cubes can built on the top of this layer.

3. The Direct Reports Route : In this approach, we create reports which run directly off the source database. There is no OLAP functionality. The reports are very static in nature and with very limited ad-hoc analysis capacity. The report queries are directly run on the source system. Mirroring/replication can be used to reduce the stress on the source system.

Lets now compare the three routes. Please see table below.

Factors Traditional Route Views Route Direct Reports Route
Data Latency High Latency Low latency. Can be made near real time. Real time.
Database size For large data volume, this is preferred approach because of the batch processing mode. Not preferred for large data volumes. Not preferred for large data volumes.
Business Logic, control and governance Centrally maintained Somewhat centrally maintained Resides in each individual report
Effort & project resources required More resources will be required Comparatively less resources are required Depends on the number of reports and complexity of reports
Effect on source system Less pressure on the source system Queries get complex and stress source system. This can be mitigated by using mirroring or replication Stress on the system is high
Ad-hoc analysis Possible using an OLAP tool on the data warehouse Possible using an OLAP tool on views Very limited
Flexibility Changing the cube structure is difficult because we would have to change ETL Changing the cube structure is comparatively simple Reports can be changed easily
Data Integration Multiple data sources can be integrated Difficult to integrate data from different sources Not possible to integrate different data sources
Data History History can be maintained in DW No history is maintained except in the source system No history is maintained except in the source system
Data Cleaning Extensive data cleaning can be done in ETL Elementary data cleaning such as checking empty values, data types etc. can be done Elementary data cleaning such as checking empty values, data types etc. can be done
Lookups Lookups can be done for data from various sources Lookups are limited to within same data source only Not possible outside of data source
Source Systems Data Structure Doesn’t matter Have to have business keys, foreign key relationships, constraints, indexes etc well defined Have to have business keys, foreign key relationships, constraints, indexes etc well defined

I am very sure there are a number of other factors which should be taken into consideration when deciding what route to take. As said earlier the choice depends on each individual scenario. As usual comments are always welcome.