Wednesday, November 30, 2011

SSIS LookUp Task...

In SSIS one of the tasks used for incremental data loading/updating is Lookup task, of course depending on the scenario. In the look up task one of the properties/settings that can be customized is the cache mode. A good understanding of the cache mode helps one design better packages utilising lookup tasks. Here is an article though published in 2008 gives a very good insight into the different cache modes. This article is by Matt Masson,http://blogs.msdn.com/b/mattm/archive/2008/10/18/lookup-cache-modes.aspx. It breaks down different cache modes and explains the use of each of them.

Monday, November 21, 2011

Isolation Levels...

When working with a relational database management system , it is very important to understand the concept of isolation levels. The isolation levels become even more important while working on transactional database systems. The isolation levels might seem very basic but it effects are more paramount as one starts to view the big picture especially a critical transactional database. It is very important to provide consistency and concurrency for the users of the transactional system. There are some basic isolation levels which are provided in SQL Server:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITEDSET TRANSACTION ISOLATION LEVEL READ COMMITED (the default setting in SQL Server)SET
SET
There are some newer isolation levels provided in more recent versions of SQL Server. I visted a older article but very relevant as one dealing with transactions, here is the link: The article has been written by Kalen Delaney, she is a sql server expert:http://www.sqlmag.com/article/tsql3/transaction-isolation-levels


I would encourage one to try out the examples in the article, it gives a good understanding of how isolation works.
TRANSACTION ISOLATION LEVEL SERIALIZABLE.
TRANSACTION ISOLATION LEVEL REPEATABLE READ

Tuesday, November 15, 2011

Database Master

The main tool i typically use is the SQL Server Management studio for working with queries. Recently i came across a tool which i needed since i had to work with SQLLite/MySql databases. The name of the tool is called Database Master which has been developed by Nucleon Software, the web site is http://www.nucleonsoftware.com/. The software is available for download with a 30 day free trail. One of the neat things about this tool is the ability to connect to variety of data sources, they include MongoDB, a document based database,
SQL Server,Oracle,MySQL,PostgreSQL,SQLLite,Netezza,db2,Ingres,dbase,XML,Firebird,Foxpro and dbase. As one can see that this is a pretty exhaustive list of databases. Once a connection profile is established, it provides a Object Explorer/query window type interface just like the one in SSMS. It is a pretty light weight tool. The tool has options for working on LINQ based qery as well. There is a snapshot of the UI given below.



One of the USP of this tool is the ability to connect to a variety of data sources, i found this useful in my ETL project where i need to connect to a variety of data sources and need to examine the data.

Thursday, November 10, 2011

SSIS-Error Messages

There are situations/circumstances where one runs/debugs a SSIS package in the BIDS/Visual studio environment, one would encounter errors. In the progress tab we can see what steps have been performed in the SSIS package and at time we see lenght error  messages. The error messages tend to be not visible   entirely in the Progress tab. This could make it difficult for one to understand what the message is and proceed with the debugging process. Here is a snapshot of the progress tab within BIDS.

In order to get the full text of the error message , highlight the message and do a right click, the option Copy message text shows up, click on it and one can paste it from the clipboard into a editor like notepad/word to get the full message.


This has helped me in lot of situation when i had to deal with sometimes cryptic error messages.

Wednesday, November 9, 2011

Ethics & Presentations

In the SQL Server Community today there are lot of presentations being made in conferences, as part of learning and teaching courses. One of the main aspects of presenting to a technical audience such as SQL Server requires adhering to certain discipline and ethics while presenting content. At times i have heard about folks using profanity in presentations to get attention of the crowd or to sell themselves as very cool in front of audience, well this doesn't work in a technical audience i think. Thanks to http://www.sqlservercentral.com/, i am posting a link to Scott Hanselman's blog:
 http://www.hanselman.com/blog/ProfanityDoesntWork.aspx
I felt that the content presented here is very relevant and brings out good points on what needs to be and what needs to be avoided in presentations.Hope this provides an enjoyable read. I feel that one needs to well research the material and be prepared fo questions from the audience. When the content being delivered is good, the audience will be drawn into the speaking event.

Thursday, November 3, 2011

XML-XSLT

In continuation with the blog post on XML, there is an another method to extract data from a XML document, the method is XSLT. XSLT is Extensible Style Sheet Language with a Transformation. The XML document is fed to a XSLT processor along with the XSLT file, the XSLT file is applied on the XML document to produce the desired XMl document. A XSLT file has a set of match and replace commands, it is also possible to recursively match and replace using a XSLT file. An XSLT file helps one to produce a more user friendly XML document. In a XSLT file one can also perform loops and iterations which makes XSLT a powerful means of extracting data from a XML Document. Here is a sample XSLT file listed below:

<xsl:stylesheet xmlns:xs="http://www.w3.org/2001/XMLSchema"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">
<xsl:output method="xml" indent="yes" omit-xml-declaration="yes" />

<xsl:template match="Textbook[@Price > 90]">
<xsl:copy-of select="."/>
xsl:template>

<xsl:template match="text()" />

<xsl:template match="NewsMagazine">
<xsl:copy-of select="."/>
xsl:template>

xsl:stylesheet>

A sample For each construct in XSLT:
<xsl:for-each select="countries/country">
      XSLT Commands...
xsl:for-each>



In the Kernow utility as mentioned in the Previous blog post on XML, one can work with XSLT and run transformations on XML in the XSLT Sandbox tab.