SQL Express 2005 – A Basic ETL Trick

For anyone using the SQL Express 2005, and going over or got over the frustration of not having the DTS… and more over not  able to use SSIS as some type of runtime that you distribute with your packaged software application…

Well, i thought i would share what i did, it is not rocket science, but works to extract the data without writing another module to maintain, rather using only command line or a batch file using sqlcmd.

If you haven’t explored sqlcmd, please do so, just go to the command line, and type sqlcmd/?, you can all kinds of options, and particularly we can look at the options that will let you execute the sql directly or better, you can pass a file with set of sql or stored procedure to execute.

I could have done this creating a stored procedure, but i didn’t want to modify the 3rd party application’s database, so i wrote a  getxmldata.sql  which outputs a results in xml (you can find everywhere how to output data in xml format using tsql)

then i wrote a batch file getxmldata.bat

sqlcmd -Slocalhost\MyDatabaseInstanceName -E -d MyDatabaseName -i getxmldata.sql -o output.xml

Something more to explore..

You can pass parameters to the your batch file and pass it to your sql file .. to make it more dynamic..

and if you want to schedule this bat file to run, frequently say every one hour or every 15 minutes..  you can use the windows scheduler to run this batch file..

you know where to find the windows tasks scheduler? it has a nice wizard to do what you want to do… if you can’t find it.. ask me…

 

IceRocket Tags: SSIS,ETL,SQL Express 2005,schedule tasks

Media Servers / Service

Have you used Windows Media Server or Apple TV? Have you heard about TVersity or used myorb or mionet or used any media servers you can get from Fry’s?

Well I installed the new Google Media server last week.. but had to uninstall, I think it is still very new..

Well.. I am looking for a service… where I can go personalize any information I want – audio/video/rss feeds/you tube/msn video/ movies/music etc and access it through my TV or mobile phone.. I don’t want to run a computer to host any server software… may be I am okay with a small $200 box like a dvd player with network connect to connect to my TV.. or I want to use my SmartPhone or iPhone or iPod or Zune to connect to my TV or home entertainment system

Dream – I don’t want to carry too many .. but I want to get what I want wherever I want whenever I want

Let’s say I go to my vontlin.MyMediaService.com and setup all the data including the one in my computer or any computer in the world that I have access to or internet feeds, and I can go anytime to change or add more… it may be a radio service from India or Movie from Vongo… or a pay per movie from movies.com or youtube video.. or rss feeds from mynews.com or It could be from my home DVR or photos or videos from my computer.. it is all there in the internet somewhere so I can access from anywhere. And Now I want to use my smartphone to see all anything I want from my favorites or I want to turn my home theater system and use my TV remote control….

Now.. what If when I travel somewhere.. i have a small mediagate device, it has 120GB laptop hard drive, and the enclosure has both optical/digital, RCA/analog audio video connections and usb connectino.. so anywhere i go, i can connect to any tv and watch movies or listen to songs …

now with iPOD or Zune.. you can do the same.. and what if they have wireless builtin (some already have)…  and connect to my media services.. and i can watch directly on a television.. of my fav lists from the internet… on  a TV…

SaaS as a Service – OEM / VAR/ branding ? Hierarchical Multi-tenant design

This may not be a new concept or a new idea, but many of use wouldn’t have noticed.. or at least when I try to see in internet search, I didn’t read much, so I thought I will blog what I know.

What is this – SaaS as a Service or why?

Imagine, being a SaaS company, where does a situation comes you will need someone to resell your product in SaaS model with a different brand or same, or with different features or capabilities or may be more localized. So this is more of a situation, as it appears the value added resellers are the service providers, who have been using your product to manage their clients and services, and now when you want to move from On-premise to a SaaS model, you still want to carry over your client base of value added resellers(VAR).

Another situation, being a ISV, you may not want to develop all the necessary building blocks by yourself, but you may want to include some other services like Licensing, billing, integration, maps, storage, computing services from another vendor in your SaaS delivery but at the end it is delivered to the end user as one solution. This is typically called mash-ups, but I am trying to extract the scenario where ISV needs to use the mash-ups, and those services are available to the ISVs enabling the customization and present themselves as a OEM or VAR Channel partner.

So what is this Hierarchical Multi-tenant design?

Well I can read lot about the SaaS and how-to , all over the web. So I don’t need to explain here again in detail. So if we all understand, (SaaS) Multi-tenant is all about every data we store in the database, can now identify which tenant it belongs to, instead of maintaining one database per tenant rather share the database server, computing power and the application code for all the tenants. And if you are in a early stage development and if you are considering the multi-tenant database, you will need to consider including another element so that your data can now identify the hierarchy of your accounts .

Let’s say the company “SAASIFY”, wants to sell their services all over the world, and instead of selling directly, it wants to sell through OEM/VAR Channels. So their product is delivered through SaaS model all their customers (VAR channels) or OEM.

SAASIFY can focus on developing the software, hosting and RASP (Reliability, Availability, Scalability, Performance), may not sell the services directly, but may generate collaborative marketing effort for all the customers. Each of those partners again, they can just focus on selling the services not to worry about maintaining or administering the software. When they login, they can see all their customers, and allow each of their customers to login in to the same system to manage/administer themselves.

 

 

 

SaaS in the Clouds for a Newbie

Software as a Service:

Software … if you install Microsoft word and create documents.. and then uninstall.. the documents stays there on your computer.. the software is the “Microsoft Word” not the data it creates.

Now if you want to use this “Microsoft Word” Software, from many computers, anywhere in the world,  and access to the documents you create…  you can notice two things:

1.  You click an Icon, you always have a latest “Microsoft Word”, it is always available to you no matter which computer you use (assume you do login and pay subscription fees) without installing any software in any of the computers you use.

2. When you start using the software, it knows what are your data, it gives access to only your files (data).

 Both Software and Data is available somewhere in the Internet – Cloud,  to run the Software in the Cloud,  imagine it is not just going to be one software, and you are not the only person going to use the software, imagine the computer with  thousands of computing power.

 

Cloud Computing:

For all software developing companies, if they want to provide their software as a service,  they need to host their software somewhere in the cloud and provide storage to store and access the user’s data.  Also they will have to store all the user’s data in the same place, but the data identifies itself whom it belongs to (Multi-tenancy) think of like a apartment number, if you are living in apartments complex sharing the same address.

 

If I own a software company, I want to worry about the software and value I deliver to my customers, and not to worry about the where to host or the infrastructure, billing, licensing, storage, connectivity…  and also my software may use some other software available in the internet as a service, say I want to show stock prices in my software or show Maps from google for the addresses

 

So I can go host my server in Amazon EC2, use storage as a service , database as a service, use Opsource’s bus or integrating enterprise applications  service or use google’s map service… now delivering my application in the clouds combined with many other services – Cloud computing

In simple terms, if you can imagine the benefits now..  when i release a software, i can make it available to all my customers, and create more opportunities to generate more revenue..  and for the customers, no hassle of installing, maintaining a software and pay only for what they use for.