Use of Visio Services as a monitoring dashboard

Recently I read a case study where data driven Visio diagrams were used for monitoring some data centers using System Center Operations Manager. Visio 2010 Add-In for System Center Operations Manager was used for this purpose. Briefly, this add-in generates visio diagram which are fuelled by data from Operations Manager and these helps to monitor the entire Data Center or an enterprise network.

In the BI development lifecycle, typically we have ETL cycles loading data from OLTP to Relational data warehouse. And then we process the cubes and/or partitions. This lifecycle is not automated mostly in the development phase and in the production phase this is made automated on schedules. I feel that by using some house-keeping config tables that host data about the statistics of such cycles, and using a data-driven visio diagram powered by this data, one can easily create a monitoring dashboard.

Now if this diagram is shared in Sharepoint 2010, one can use Visio Services which would allow to view this diagram in a browser without requiring visio client installation. So without opening up the security of the servers, one can easily monitor these lifecycles over the web, making it possible to remotely monitor these schedules in an intelligent manner. This would save the effort of bringing PPS into picture for such internal house-keeping task or checking your Pager / Inbox for alerts / informational emails which can get either too concise or too verbose.

How to learn SSIS / SSAS / SSRS / Data Warehousing and start a career in Microsoft Business Intelligence

I often get queries regarding career guidance, from my blog readers or from readers who follow articles authored by me. These readers are mostly BI aspirants or those who feel saturation in their present working area like .NET or DBA or SQL Developer work profile. Also some female readers do ask me that they have not been working after starting a family for a while (couple of years) and now they are in need to start a job and they want to start a career in microsoft business intelligence.

Some of the common questions that I often get to hear from readers are as below, and in public interest I would try to answer these questions. I remain / remained on the interview panel at Capgemini Mumbai for screening candidates on my relevant skills, but the below views are strictly and completely my personal views and has nothing to do with the view of my employer.

1) How to start a career in MS BI and what to learn - SSIS / SSAS / SSRS ?

If you are facing this question when you are starting your fresh career, it's not in your hand. The reason is that when one joins an organization as a fresher, one does not have enough liberty to choose the technology one wishes to work as the company is investing on the candidate to train him/her and then assign a project. Also freshers generally are not billed to the client, so the billing productivity is almost zero for at least an year. Still, I suggest to keep learning these technology and always keep your resource management aware of your desire and readiness to work on these technologies.

If you are already an experienced professional, things would be even harder. After a level of experience on generally acquires some stiffness in attitude and aptitude.

One needs to show readiness to work and treated again as a novice, and leave all the fat of your experience behind to make an entry into a complete new field. You might be an experienced DBA or .NET Developer / Designer or some module / team / project lead, but that doesn't change the fact that you are a novice in MS BI and the first project you land in MS BI would be a beginner's level work. Then you slowly grow in the field with experience. There is no shortcut to hardwork.

In terms of aptitude, any knowledge is never wasted. Almost all MS technologies integrate in one or other way with MS BI upto an extent. In the order of increasing complexity, one can start with SSRS, then SSIS and then SSAS. Data Warehousing is more a process than just an analytical database. So once you are clear of the concepts of DW, one can take a specialized area of it using any of the MS BI Technologies. So anytime is good to start a career in MS BI or Data Warehousing, but don't rush to shed off your existing knowledge. Make a smooth and gradual transition, one cannot suddenly come in the skin of MS BI Developer / Architect. We are IT Professionals not X-Men :)

Books are your best friends. Start with foundation level books, seek guidance of some experienced BI Professional in your organization or over the web and then pick up a direction of your comfort.

2) Where to find books or training material to learn SSIS / SSAS / SSRS ?

If you work with at CMM Level 3 or plus kind of organization, you can always access your corporate library. If not, most of the organizations have books24x7.com subscription for employees, try searching on the same. Try searching downloads section on my blog, you would find quite a couple of books which are free from Microsoft or Red-Gate courtesy. Still if you have not found book you need, I suggest to make some investment in your career and buy the book. Investing some money is worth than wasting time to find a free ebook over the web. Remember, time is money and wasting time is equivalent to wasting money if you can envision it.

3) Which certifications to take up for MS BI and why to get certified ?

As of the draft of this post, the latest version is SQL Server 2008 R2 and Microsoft has announced that there would be no new certifications for R2 version. One can start with MCTS track, then on passing more exams one can earn MCITP and then final level is Microsoft Certified Master. These are online exams and can be appeared at any authorised Prometric or Vue testing centre. Passing score mostly for any exam remains 70%. Check out this URL for more information.

Most IT Professionals have the image that getting certified can be listed as one point during the annual review process and can be a glittering gem for justifying a good rating during the appraisal meeting. This is a myth, in fact if you get certified by resorting to wrong means, you are backbiting your career and interviewer like me would be more interested to grind you more to test your skills. Get certified to gain knowledge, to prove and benchmark your knowledge ONLY.

4) How to pass an interview for a MS BI work profile, when one has got no experience in MS BI ?

I would suggest that manage to get a project in MS BI with your present employer and after gaining experience, do think of making a change for right reasons. If you are making a change, do not expect your prospective employer to hire you for a technology which is not your primary strength. Join the company based on your primary skills, prove your technical strength on your primary skills and then based on your image as a professional and your knowledge in MS BI, try to get yourself landed in a MS BI Project.

5) How should I prepare for MS BI i.e. SSIS / SSAS / SSRS Interview and where can I get interview questions ?

This is one question that I encounter the most. If you are looking for interview questions over the web, then I would say that you are definitely not 100% ready to appear an interview. The level of readiness you have of taking someone's interview, is the level of readiness you have of appearing for an interview. In my views, the best way is to join MSDN Forums for respective technologies like SSIS Forum, SSRS Forum or SSAS Forum. A lot of real life questions are discussed and answered everyday by professionals of all kinds of maturity level. Be a part of it, help others to help yourself. Beware, that you would find some MVP hungry folks on those forums who would answer any and every question to gain points in whatever way they could. Ignore such people, and focus on your objective of learning the technology by studying problems and devising solutions for the same.

6) Where can I get training on MS BI / SSIS / SSAS / SSRS ?

Many companies do conduct corporate training, one can always attend those ones. If you do not have that facility available, I would not suggest to attend some classes as that definitely costs some huge money and when you do not have idea of what you should get trained on, it doesn't make sense to get trained in something when you are not aware of it's significance.

When I started my blog, I was not aware of an army of workshippers, whom I term as Blog / MVP workshippers. Approach any famous blogger or MVP or any author with the intention of acquiring knowledge or getting guidelines to move in the right direction. And mostly such people won't charge you anything for such help. If they ask fees for even such guidelines, they are not worth consulting, avoid them. Keep in view that everything that glitters is not gold, and every famous IT Professional is not knowledgeable in the proportion of his/her popularity. Use your head, seek guidelines, study the trend, read books and DO NOT be a dumb workshipper or follower of some popular IT figure.

This is by far the lengthiest post on my blog, and I was able to stretch this long as I can see many people burning in the skin of their present career, waiting or confused to make a start in Business Intelligence or Data Warehousing. If you have read till here, I hope you have found this information useful. All the best with your Quest !!

Unconventional use of SSIS

Use of SSIS with SQL Server, DB2, Teradata, Oracle, Excel, Flat files, and other such relational source is quite known among the developer community. But SSIS has it's use in a variety of areas, and some of them are so unconventional that one would not even think of using SSIS with such areas. I have worked with some of such areas, and I thought of sharing a list of such areas in my vision.

1) SSRS - SSIS can be very well used with SSRS and vice-versa too. SSIS can hook into SSRS to control reporting side and SSRS can access SSIS as a data source thru it's SSIS data processing extension. An article authored by me on the same topic can be read from here.

2) Sharepoint - SSIS can read and write data from Sharepoint lists and an article on the same topic can be read from here.

3) Windows Management Instrumentation - SSIS has got features to read data using WMI, and those who have worked with WMI and/or WMI Script would know that it can generate any possible information about any system ranging from a stand alone machine to an entire network topology. WMI can also be seen as the reporting face of OS kernel.

4) SAP - SSIS has got connectors for use with SAP, but a great deal of support still lacks for this area. Connectors are available to hook into SAP but generally the security policy of any enterprise does not allow such access. Still by exporting data from SAP to a sandboxed environment, data can be moved from and to SAP tables. This approach helps quite a lot in a Systems Integration programme or a data migration project.

5) Peoplesoft / JD Edwards - This can be seen similar to the SAP area. Biztalk and SSIS both are considered as possible options to use with it, but to the best of my knowledge, peoplesoft runs on Oracle or DB2 and both gel well with SSIS. So this again falls in the territory of SSIS comparing the cost options and developer expertise of SSIS versus Biztalk.

6) Microsoft Assessment and Planning Toolkit - This toolkit also generate a lot of asset management data from the enterprise network. It can been seen as a scaled down version of WMI, but this can generate tonnes of information and reports and SSIS can provide a helping hand to it.

7) Maps Services - Web based maps services like Bing Maps, Yahoo Maps, Google Maps are some of the frequently used third party services for developing spatial intelligence. These services use different file formats, generate responses in a variety of formats and SSIS has a big role to play in synchronizing and standardizing the data generated by the same.

Above is a brief list in my vision. If you have come across such unconventional use of SSIS, please feel free to add to this list by commenting on this post.

List of my articles published on community websites till 24 May 2010

I am not a very popular, big or well connected name in the industry, but I am trying to be at least a respected name :) I am well respected among my friends and colleagues at Capgemini for my work in my technical area, and now I am trying to put my knowledge to test and use for the entire technical community by the medium of my blog and other community websites. I am always up for sharing something new, that has not been published a million times on blogs, books and websites. Today my article on how to achieve spatial data support in ssis got published on sql-server-performance.com, and on this day I thought of summarizing a list of all the major articles that I have authored till date. Four of these articles also got featured on MSDN SSIS Community Buzz, and a few of them on multiple community websites. Below is the list is no specific order.

In case if you have read any of my articles, and have any feedback for me, please feel free to share it with me.

• Different Ways to Search for SQL Server Database Objects


• Using Microsoft Quadrant for data analysis and modeling


• Create a Simple SQL Server Database Server Object Report with Minimum Effort


• Decouple context specific logic from SSRS reports using facades in SQL Server


• How to enable SSIS as data source type on SQL Server Reporing Services SSRS 2008 R2


• How to use SSIS package as a data source for SQL Server Reporting Services 2008 R2


• How to connect to SQL Azure using SQL Server Reporting Services 2008 R2


• How to read and write data from SQL Azure using SQL Server Integration Services 2008 R2 (Featured on MSDN SSIS Community Buzz)


• How to make an SSIS Merge Join transformation fail-safe from sorting irregularities


• How to serialize error logging in SSIS


• Planning the SQL Server ETL implementation strategy using SSIS for Extracts


• Profiling SSIS packages for component level change tracking (Featured on MSDN SSIS Community Buzz)


• Trick to search a SQL Server SSIS package for tasks and transforms


• Achieve Spatial Data Support in SSIS


• Working with Spatial Data Part IV - Developing a Geospatial Dashboard (GIS)


• Working with Spatial Data Part III - Reporting Spatial data using Reports Builder 3.0


• Working with Spatial Data Part II - Plotting Shapes and Storing Geocoded Data (Featured on MSDN SSIS Community Buzz)


• Working with Spatial Data Part I - Geocoding Text-Based Spatial Data for Use In SSIS Packages (Featured on MSDN SSIS Community Buzz)


• Microsoft Business Intelligence Project Booster Kit

Introduction of search capabilities for business intelligence in Sharepoint

Search is one of the most needed and used service when you have abundant content created and used by a variety of user base. I have been a big admirer of search capabilities and even BIDS lacks this features to some extent. For example there is no facility to search anything within SSIS package in BIDS. I authored an article explaining a trick to solve the same issue which can be read from here.

Consider in case of SSRS Reports, there is as such no search capabilities in SSRS. Even using Report Manager, one cannot actually search for reports and searching for data that would be generated in those reports is like imagining some science fiction concept. A business user who might not have the idea of which report to use which might provide relevant data, would like to use search to figure out relevant data or report. As of the draft of this post, I do not have any out-of-box capability in Reports Manager to cater this requirement. If SSRS is configured in Sharepoint Integrated mode, sharepoint has somewhat nice search capabilities compared to Reports Manager, still it cannot solve this requirement.

Microsoft FAST Search Server 2010 for Sharepoint is deployed over Sharepoint 2010 to empower search capabilities on Sharepoint environment and increase business productivity by enabling users to search relevant content more efficiently and easily. In order to understand more about FAST Server, you can download FAST Search Server 2010 for Sharepoint Evaluation Guide.

Recently release of Microsoft Business Intelligence Indexing Connector was announced. This connector comes in two parts, backend and frontend. Backend component is installed only on FAST Server and Frontend Component is installed on all the sharepoint servers in any server farm. For frontend components, installation of backend component is a prerequisite. This connector crawls excel and powerpivot files, as well as it reads RDL (SSRS Report file) and searches the data sources / datasets used in reports. The advantage is that without executing each and every report which might be in Excel, Powerpivot or RDL Report output, one can easily preview the result of reports and find relevant information. On a face value, this has a lot of potential of helping users reach the right content helping user's to save wasting time in figuring out right information, as well as save unnecessary hits on data sources. This is definitely worth installing a trial edition and having a evaluation of user experience and productivity gains.

I wish that such search capabilities should be added to Reports Manager also. SSRS Webservice has two endpoints, one of which handles execution and other handles server management. A third service should be added which should facilitate search capabilities similar to FAST Server. With microsoft's acquisition of FAST and introduction of such connectors that plugs into BI territory, I do anticipate that SSRS webservice would flourish and bring such search capabilities in-built with SSRS itself.

Implement Kerberos Authentication in SSRS 2008 - Whitepaper

One of the most implemented authentication mechanism in SSRS (in my experience) has been Windows Integrated Authentication. But this is not the only authentication mechanism available in SSRS. Kerberos Authentication is one of the another available authentication mechanism available with SSRS.

In a typical federated server environment, where one would require a challenge/response kind of authentication mechanism without passing authentication credentials over the wire, Kerberos might be an ideal choice. Especially financial domain clients implement this kind of mechanism as the nature of the industry makes this authentication mechanism more suitable from a security point of view.

Most of us (including myself) do not get many chances to configure, test and experience the implementation of this kind of authentication mechanism. Also one would not want to do a trial-error while implementing such complex authentication mechanism. Download and keep this whitepaper in your kit, and develop readiness for challenging Kerberos Authentication Implementation in SSRS 2008 for your enterprise.

SSIS Performance Testing , Measurement and Optimization Video Tutorials

SSIS package development is not that challenging, as is the tuning of performance. Performance is one such topic that everyone thinks only after the entire solution is built of the physical design starts getting implemented. Pre measuring implementation strategies for performance measurement and performance testing, is one of the most undervalued aspect in a ETL development life cycle. Data Flow is the main task which takes the helm of data movement in a SSIS package, and hence the optimization of Data Flow itself, which acts as a container for the transforms within it, is very important. There are already abundant of websites, blogs and books filled up with tips on the same topic, but these short video tutorial (especially the measuring and understanding performance video) do have content that is not the same regular stuff and it comes from one of the senior most SSIS team members.

There are two new video tutorials on SSIS performance testing, SSIS performance measurement and SSIS performance optimization for Data Flow task, available for download on technet. Links to the same are listed below:

1) Measuring and Understanding the Performance of Your SSIS Packages in the Enterprise

2) Tuning Your SSIS Package Data Flow in the Enterprise

Powerpivot as data source for Performancepoint Services 2010 - Whitepaper

Performancepoint 2010 supports different kind of data sources that can be categorized into Analytical data sources and Tabular data sources. Analytical (Multidimensional) data sources include any data from SSAS cubes and Powerpivot, Tabular data sources include data from sources like Excel spreadsheets, Sharepoint lists, SQL Server tables, MS Access tables and finally custom data sources. For custom data sources, one needs to use PPS SDK.

Using powerpivot as a data source is not straight forward or I can say that it's different. The reason it's different is that, powerpivot is something that is neither completely multi-dimensional, nor completely tabular. It can be seen as an intermediate between the two types of data sources. So if you are trying out your hands-on for the first time using Powerpivot as a data source with Performancepoint, it can baffle your mind by the way data from powerpivot is classified in hierarchies, measures and dimensions in your PPS Dashboard Designer 2010.

Answer to the above prospective challenge is a whitepaper from Microsoft. This whitepaper explains how to use powerpivot as a data source in performancepoint services 2010 ( in sharepoint server 2010 ). This whitepaper can be downloaded from here.

SQL Server data types serialization format

This is the 200th post on my blog and I am blogging this at 2:00 AM in the morning, and see what a grand resource I have got to make this a grand post. When we look at data stored in spatial data types i.e. geometry or geography ( I know hierarchyid is also spatial data type, but I am ignoring it for the moment ), the binary format looks alien to us, but in fact is called as WKB (Well known binary) format. Also this format is very structured and if you know the serialization logic behind the same, you can easily decode this metadata just by looking at the binary values.

Though one would not be required to use these kind of manuals on a day to day basis, but this can help to deepen your fundamentals about SQL CLR Types, and spatial datatypes in particular. Keep it as a manual to understand storage and serialization of each and every SQL Server data type. It's a PDF available for public download from Microsoft and the same can be downloaded from here.

Difference between SSIS 2005, SSIS 2008 and SSIS 2008 R2

There was no difference architecturally in SSIS 2008 and SSIS 2008, and there is no difference or enhancements in SSIS 2008 and SSIS 2008 R2 version except one. In the ADO.Net destination adapter, an additional property has been made available to bulk insert records when possible. And probably this is more of a move to support inserting data into SQL Azure. I had authored an article on the same subject which can be read from here. Those who are keen to learn difference between SSIS 2005 and SSIS 2008, check out the "SSIS New features in SQL Server 2008" series from here.

Theoretically, when you consider migration there should not be any issue between 2008 and 2008 R2, and I was under the same impression too. But as Einstein said, "In theory, theory and practice are same, but in practice they are not". Recently there was a migration issue reported on migration of packages from 2008 to R2, but this issue is limited to ADO.Net destination adapter control only. And just by modifying the XML of the package, the same can be resolved easily. John Welch explains this issue and the solution to the same in a very elaborate manner, which can be read from here.

Difference between SQL Azure and SQL Server - Whitepaper

There was a time when I used to wonder what is SQL Azure and just by the name I used to think that it's not the stuff that I would into which I would want to peep my head. Then finally when the trial version was out, I opened up my account and tried out everything that I wanted to break my curiosity. Also I authored two different articles on How to read and write data from SQL Azure using SSIS 2008 R2 and How to connect to SQL Azure using SSRS 2008 R2.

As soon as SQL Azure went RTM, all trial accounts were freezed out and got converted to production DBs. And if you want to access SQL Azure now, you need to be ready with your credit card, though whatever small may be the amount. So now if someone want to make a start and check out what SQL Azure is capable of, there no way to do it without paying for the same. Though it's not possible to practically get a hands-on SQL Azure to study the difference between SQL Azure and SQL Server, but this whitepaper can be quite handy and pilot quickly through each details of differences between SQL Server and SQL Azure. Cloud computing is the change in the making, and potential of SQL Azure should not be underestimated. If one day you find yourself in a situation where you need to migrate or develop applications based on SQL Azure instead of SQL Server, this is the first whitepaper that you would open out of your kit.

Microsoft needs to provide a SQL Express version of SQL Azure, I mean a free limited trial version of SQL Azure so that users can at least evaluate the product.

Download best of SQL Server 2008 R2 Feature Pack

SQL Server 2008 R2 Feature Pack is available for download and below is the list of features that are very attractive in my views. For those who are now aware of what is a feature pack, it's a collection of separate independent packages that can be used as tools or components with different services of SQL Server including database engine, SSIS, SSAS, SSRS and related technologies.

1) Download Microsoft SQL Server Report Builder 3.0 for Microsoft SQL Server 2008 R2: This is top highlight as it brings the Map control and Bing Maps capabilities, Sparklines, Databars, and Report Parts Gallery.

2) Download Microsoft SQL Server PowerPivot for Microsoft Excel: Business users would not want to wait and download it ASAP and try out the potential of Powerpivot.

3) Download Microsoft SQL Server 2008 R2 Books On-line: My personal favourite. It's always advantageous to stay up-to-date with the latest reference material.

4) Download Microsoft SQL Server 2008 R2 Best Practices Analyzer: This latest best practice analyzer is available now. So stop playing with the old 2000 / 2005 version analyzer and get this shiny new analyzer for your shiny new R2 version of SQL Server.

5) This is not a part of the Feature pack, but I thought of mentioning it as it's a very valuable resource. It's a introductory level " Free SQL Server 2008 R2 Ebook ".

Above list is not the exhaustive list. Entire feature pack can be downloaded from here.

Enterprise Data Quality and SSIS

A general perception of Business Intelligence is Data Warehousing and using Analytical solutions. But this is a 10,000 Ft picture. If an end-to-end BI solution is considered, ETL, Operational Reporting, Relational and Analytical Data Marts or Data Warehouse development, Dashboard Development and deployment over a collaborative platform like Sharepoint becomes visible from a 1,000 Ft. I find a major part of the MS BI Community busy in taking steroids of T-SQL Performance Tuning or engrossed in wrestle mania of re-blogging content that has been published tonnes of times and is available even on MSDN, many community websites are even busy in saving the legacy of SQL Server 7.0 / SQL Server 2000 and entertaining blind community challenges on the same. If you are working with a solution provider where you get all different kinds of projects from a vast client base, you would find that the world is big enough and there is more spectrum in BI and more sensible exercises to carry out than the typical ones or the obsolete ones. When you take a 100 Ft level closer look at any of the development aspect like ETL for example, you would find that non-microsoft players would also come into picture that would integrate with MS BI technology stack, aspects like Data Quality, Data Cleansing, Systems Integration would start popping up. This would feel like I am describing a wonderland, but the fact is that you realize this only when you work in that role.

As usual, enough of theory and coming down to the subject of this post, I just have two points to mention. Firstly, Data Quality is a very important aspect of any Data Migration or Data Cleansing or Systems Integration Programme. Even when compliance or audits becomes the main objective, more than 80% of the project depends upon data quality. Perception of compliance is limited to auditing like C2, SOX, HIPPA and others. But auditing is one of the aspect of these compliance and there's more to it which also includes data quality compliance. Secondly, in SSIS, Data Profiling task is one of the best improvements after 2005 version. It can be used to make intelligent decisions based on the quality of the data, when the same is processed.

If you really intend to learn more on data quality, I suggest to read this whitepaper on Enterprise Data Compliance. This whitepaper explains the legal complexities and implications of data quality, business process that affects data quality, and technical architecture and components needed to establish enterprise data quality. After you are able to grasp this architecture, you would definitely find that SSIS can be used exhaustively as the primary tool to craft a data quality solution. Though just the Data Profiling task would fall short and rest of the requirements would have to be tailored manually using different tasks and transforms, but I am very sure that SSIS still has a large scope to evolve and the present potential is reasonable enough to consider it's use for data quality exercises.

Free SQL Server Ebook : Defensive database programming with SQL Server

It's been raining free content and resources for SQL Server these days. Every next day when I think I would blog about a technical topic and I get hit by a new free resource on SQL Server, good enough to make me hold my technical post and blog about this free content. Today it's the favourite food for developers, a free ebook on SQL Server.

Most people look out for interview questions and answers ebooks to cross technical interviews. In my views, if you really want to cross a technical interview, read such books and check yourself whether you are up-to-date with your concepts and viewpoints by matching it with the viewpoint and guidelines presented in such books. I have not read this entire book completely, as the moment I came to know about this book, I took a 10-15 min glance of this book and made up my mind to blog about it. It's a nice ebook and the subject is relevant itself to reveal what the book is about. For dummies, this book mostly discusses how NOT to do database programming from a long and/or short term vision. Book contains many code examples which is like a feast for SQL Developers. Click here to download this book.

Reference: SQL Aloha

Free SQL Azure Videos and Guide

Free downloads of developer resources is one of the most sought after thing in any technology. Not to mention anyone is specific, but I have even seen many sites posting lots of free content for download like Free SQL Server 2008 Book / Ebook and Trial download of SQL Server 2008 and other such contents, which pumps up their readership exponentially. But when you see the stats of pure technical content that is posted free on such sites, it would be shocking to know that it would have comparatively far lesser hits. And nope, I am not discussing about my blog, neither I am trying to discuss how to increase your sql server blog traffic nor I am going to reveal traffic stats of any community websites, but I am trying to give a viewpoint on how much crowd is after the quest of knowledge and how much is after just gathering free download like books, tools and other such stuff.

I agree that I could have just posted link to the free sql azure resources without the lecture in the above paragraph, but I thought I can make you forcibly read this lecture as you too came for free stuff on my blog :) So below is the prize for visiting my blog.

• Download Free SQL Azure FAQ Whitepaper


• Download Free SQL Azure Video on RDBMS Support from MSDN


• Download Free SQL Azure Video on Security Model from MSDN

Free Visio Viewer

IT cannot work in Isolation, and after a certain level in one's career, one cannot just stick with routine code business. Whether from a modeling perspective or from a data processing perspective, Visio finds it's tiny space in SQL Server ecosystem in the form of Visio as a tool for modeling diagrams or Visio services for strategy maps in Sharepoint 2010.

Even for our day to day purpose, usage of Visio file formats like .vsd among the developer community are as frequent as .gif or .jpg in the office users community. Microsoft Visio is not a free tool, but viewing should not cost anything. Visio diagrams are much more packed with data and information compared to other file formats and for the same a more intelligent viewer is required. Fortunately Microsoft has released a free viewer, and I feel this is one of the must have additional tools to keep in your kit. Visio 2010 viewer is a free download available from Microsoft and can be downloaded from here. This viewer can view files of .vsd, .vss, .vst, .vdx, .vsx, or .vtx extension.

Lack of support for spatial data movement in SSMS and SSIS

Spatial data has definitely got some level of support in SSMS, but that is more limited in terms of visualization of data in the results tab. When I consider to move the same data out of SQL Server i.e when I want to import and/or export this data using the Import Export Wizard, it breaks up as it is not upgraded with a corresponding data type in SSIS.

Even if you modify the config file that Import Export Wizard uses to map it to any data-type and facilitate the data movement, it does not help. Using varchar(max) or varbinary(max) also helps for some destination and breaks for others. To the best of my knowledge, I am not able to figure out a generic datatype that can take the helm of pulling or pushing any spatial datatype on it's back for a smooth import - export.

The same issue is faced in SSIS. As of date, there is no straight mapping for spatial data types. Though spatial datatypes got introduced in SQL Server 2008, SSIS has not been upgraded even in the R2 version to accommodate smooth movement of spatial data. DT_IMAGE is the best option as of now to move the same in SSIS, or the best approach is to convert spatial data in WKT format before it leaves SQL Server and read the same in SSIS. Converting in WKT makes it easier to analyze and manipulate this data in SSIS and then use a Script task or the same WKT to push data back into the spatial columns.

Acceptance of spatial datatypes and it's use would grow gradually, but R2 would have been good release to incorporate the support for spatial data movement in and out of SQL Server as well as in SSIS too.