Monday, October 31, 2011

Cursor Data Type in SQL Server

The CURSOR data type is used only for stored procedure output parameters and variables. It cannot be used within the CREATE TABLE statement. The CURSOR data type should be used to pass a reference to a resultset returned by a cursor. Variables declared by the CURSOR data type can be used with statements regularly used for cursors (DECLARE, OPEN, FETCH, CLOSE, DEALLOCATE), as well as stored procedure output parameters and CURSOR_STATUS function. Some of the system procedures that describe resultsets within cursors could also be used with variables declared with CURSOR data type.

The following rules pertain to cursor output parameters when the procedure is executed:

1. For a forward-only cursor, the rows returned in the cursor's result set are only those rows at and beyond the position of the cursor at the conclusion of the stored procedure executed, for example:

    •    A nonscrollable cursor is opened in a procedure on a result set named RS of 100 rows.
    •   The procedure fetches the first 5 rows of result set RS.
    •   The procedure returns to its caller.
    •    The result set RS returned to the caller consists of rows from 6 through 100 of RS, and the cursor in the caller is positioned before the first row of RS.

2. For a forward-only cursor, if the cursor is positioned before the first row upon completion of the stored procedure, the entire result set is returned to the calling batch, stored procedure, or trigger. When returned, the cursor position is set before the first row.

3. For a forward-only cursor, if the cursor is positioned beyond the end of the last row upon completion of the stored procedure, an empty result set is returned to the calling batch, stored procedure, or trigger.

4. For any type of cursor, if the cursor is closed, then a null value is passed back to the calling batch, stored procedure, or trigger. This will also be the case if a cursor is assigned to a parameter, but that cursor is never opened.

In the following example, a stored procedure is created that specified an output parameter, @currency_cursor using the cursor data type. The stored procedure is then called in a batch

First, create the procedure that declares and then opens a cursor on the Currency table.
USE AdventureWorks2008R2;
IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.uspCurrencyCursor;
CREATE PROCEDURE dbo.uspCurrencyCursor 
    SET @CurrencyCursor = CURSOR
      SELECT CurrencyCode, Name
      FROM Sales.Currency;
    OPEN @CurrencyCursor;

Next, execute a batch that declares a local cursor variable, executes the procedure to assign the cursor to the local variable, and then fetches the rows from the cursor.
USE AdventureWorks2008R2;
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;
     FETCH NEXT FROM @MyCursor;
CLOSE @MyCursor;

LOOKUP Transformation in SSIS 2008

in SSIS 2008, we are going to use “LOOKUP Transformation” task between our source and destination. LOOKUP will do the job of checking source and destination. It can return match/not match data as per your need.

Follow bellow steps:

1. Open BIDS and create new project for Integration Services and drop “Data Flow Task” in your work area from toolbox. To configure it, double click on that so that you will be redirected to the “Data Flow” tab.
Now, on dataflow tab, please drag “ADO NET Source” task from toolbox and drop it to your work area.
Double click on it to configure and set your database and table of ADV1 database there, for more information look at below screen shot.

2. Drag “LOOKUP”  transformation task to your work area and join LOOKUP task with ADO NET SOURCE. Now, double click on “LOOKUP” to configure it.

Since, we want to copy only those rows which doesn’t exists in destination table, we have to select “Redirect rows to no match output” option from LOOKUP Transformation Editor and click on  “Connection” tab at left hand side.

3. Select your destination database and table. For more information, please look at the below given screen shot.
Now, please understand this concept, “ADO NET Source” returns row from ADV1.ChemicalConsume table which will be “Input” for our “LOOKUP” task. In the connection tab of LOOKUP task, we have made connection to “ADV2.ChemicalConsume” table as a destination so that LOOKUP will compare source and destination. 

4. Now from “LOOKUP Transformation Editor”  click on “Column” tab to set on which columns we are going to make comparison. Our comparison will be on “ChemID” and “ConsumeDate” fields of both the tables. So let us do it.

5. Now, take “ASP NET Destination” from tool box and join it with green arrow of “LOOKUP” task.
As soon as you will try to join green arrow of “LOOKUP” with “ASP NET Destination” you will get on small dialog box named “INPUT OUTPUT SELECTION”. Set the drop down value as shown in below screen.

6. Now finally double click on “ADO NET Destination”  to configure it and set your ADV2 database along with appropriate table.
7. Now, run your package by hitting F5 and see output of the package.

Online Course: Introduction to SSIS

Online Course: Introduction to SSIS

As we build larger and more powerful data centric applications, the need to bring information together will become more important. Building efficient and intelligent ETL processes will be both a requirement and a skill that will be found to be in high demand. SQL Server Integration Services (SSIS) is one such ETL tool, and because it comes with Microsoft SQL Server, it will also continue to rise in popularity.

This virtual, online course, delivered by Microsoft SQL Server MVP Eric Johnson, will guide you into the world of SSIS. The class will start at the very basics of making a new package, and will progress towards advanced topics such as debugging and deployment. The entire goal of the course is to take a course attendee from never having used SSIS to being a competent SSIS developer in twelve sessions delivered over four weeks.

Click here for more information.

Lookup Function in SSRS 2008 R2

Today, I would like to give the information and an example of using the Lookup feature in SSRS 2008 R2.


Lookup function returns the first matching value for the specified name from a dataset that contains name/value pairs.

Lookup(source_expression, destination_expression, result_expression, dataset)

I am going to just create a simple Reporting Services project with a shared data source to the database.

However, please note that I have two separate data sources pointing to two totally different databases.

So now let’s create a report with a couple of datasets in it. We are going to print the Sales information in one database while we would have all of our Customer information in another database. So I will create one dataset for Sales based off of the SalesLT.SalesOrderHeader table and the other one of Customer details off of the SalesLT.Customer and associated tables.

So now the problem is that since the data for the customers is in a totally separate dataset .How do we relate the two in order to remove CustomerID from our table and replace it with say the customer’s company name?

That’s where the Lookup function comes in. I will add a column on the left of my report and set the header to Company. Next I will delete my CustomerID column from the report since I won’t need that anymore. Lastly, I add a lookup function as an expression for the field under the Company column.


So in order to use the Lookup function I need to pass it four things.


(Variant) An expression that is evaluated in the current scope and that specifies the name or key to look up. For example, =Fields!CustomerID.Value.
(Variant) An expression that is evaluated for each row in a dataset and that specifies the name or key to match on. For example, =Fields!CustomerID.Value.
(Variant) An expression that is evaluated for the row in the dataset where source_expression = destination_expression, and that specifies the value to retrieve. For example, =Fields!CompanyName.Value.
A constant that specifies the name of a dataset in the report. For example, "Customers".

Now if we run our report we can see that everything turned out exactly as it should have.
Now it should be stated that I could have used an expression for any of the values passed as parameters to the function. This allows us to do matching on things like complex keys(ie more than one column to denote a match) or bringing back complex values like the full name of the customer.

Thursday, October 27, 2011

How to implement Conditional Split in Control Flow of SSIS

As we don't have a conditional split in control flow of SSIS. We need to follow below steps to achieve Conditional split in Control Flow.
1. Define a Package Variable (i.e. UserName).
2. Add a dummy script task and set the TaskResult property to success.As shown in Figure 1 & Code.

Figure 1: SSIS Package with Dummy Script Task

public void Main()
            Dts.TaskResult = (int)ScriptResults.Success;

2. After that define a precedence constraint between script task and data flow task. Precedence constraints link the individual executables together and determine how the workflow moves from one executable to the next.

3. Add an expression, double-click the precedence constraint to open the Precedence Constraint Editor dialog box, as shown in Figure 2. (The editor shown in this figure is the one for the precedence constraint that connects the first and second Execute SQL tasks.)

4. Data will flow on the basis of Package Variable value. As shown in Figure-3

Exporting a report to multiple Excel WorkSheets

To render a report to Excel on multiple sheets, be sure to use page breaks after the different sections of the report. If a section doesn't specifically allow page breaks, then you'll need to wrap the controls inside a rectangle and set the page break property on the rectangle.Let's say you have a report with two table regions, as shown in Figure 1.

Figure 1: A report with multiple table regions
When you export the report to Excel, you'll find that both the table regions display on the same worksheet, as shown in Figure 2. This makes it hard to make modifications to the Excel file.

Figure 2: Two table regions rendered to the same Excel worksheet
To make the table regions display on different worksheets, you can check Add a page break after check box, as shown in Figure 3.

Figure 3: Setting the PageBreakAtEnd property
When the report is exported to Excel, two worksheets will now be created, as shown in Figure 4.

Figure 4: Two table regions exported to two worksheets

Pagination in Reporting Services

One of the useful things you can do in SSRS 2008 is pagination. Pagination is useful if you want to display the page number in the footer, or other parts of the page.
If you drag a textbox onto the page you can right click in the textbox and click on Expression you can tell SSRS what you want to display in the text box.
Inside the Expression box in the bottom left you will find an area called Category. If you click on "Built-in Fields" you will see some useful things for us. PageNumber and TotalPages are what we are after.
So let's make a string that will display "Page 1 of 2". Where 1 is our current PageNumber and 2 TotalPages.
Simply use the following and you should get the desired result:

="Page " & Globals!PageNumber & " of " & Globals!TotalPages

November Free Training with Pragmatic Works

Pragmatic Works, SQL Server Central and Microsoft would like to invite you to a another month of Free SQL Server Training delivered by the people that write the books!
November Speakers and Topics
Speaker: Bradley Ball
Topic: SQL Internals, Recovery Models, and Backups! Oh My!
Date: November 1, 2011, 11:00AM EDT
In this session you will learn about the internal Structures, Recovery Modes, and Backups and be better prepared for Future Learning and Managing SQL!
Speaker: Adam Jorgensen
Topic: MDX Common Solutions
Date: November 3, 2011, 11:00AM EDT
Come ride along with Adam as he walks you through some common MDX solutions that you are going to use as you develop your Analysis Services solutions.
Speaker: Jose Chinchilla
Topic: Introduction to Master Data Services
Date: November 8, 2011, 11:00AM EDT
In this session, Lose will do an overview of Master Data Services and how it can be leveraged with SSIS and SharePoint 2010 to accomplish a Master Data Management framework. Jose will cover concepts like models, entities, attributes and attribute collections, explicit and derived hierarchies, subscription views and business rules. Jose will also demo how to load an MDS entity with SSIS and how to kick start a workflow process in SharePoint 2010 when a business rule is not met.
Speaker: Adam Jorgensen
Topic: Expert to Expert SSAS
Date: November 10, 2011, 11:00AM EDT
In this session, Adam will take another SQL Pro and have the build an SSAS solution just while he talks them though it. This is a great way to see the power of SSAS and solutions to some of the common stumbling blocks many of you might be facing.
Speaker: Brian Knight
Topic: Upgrading DTS Packages to SSIS
Date: November 15, 2011, 11:00AM EDT
The Microsoft "End of Life" policy for SQL 2000 has encouraged many organizations to seriously look at migrating their SQL2000 servers to SQL2005 or 2008. One of the biggest obstacles to a rapid migration is the DTS package issue. To gain the performance benefits and a supported path forward for the functionality in DTS, these packages should be converted into native SSIS. As we all know, the conversion is NOT straightforward. SSIS is more powerful and significantly different from DTS. Join us as Brian Knight shares his knowledge about why you should upgrade from DTS to Integration Services and options for converting DTS packages to native SSIS.
Speaker: Adam Jorgensen
Topic: Introduction to Dimensional Modeling
Date: November 17, 2011, 11:00AM EDT
Do you have star schemas in your eyes? Come and hang out with Adam as he walks trough the best condensed introduction to dimensional modeling that will get you building a warehouse in an hour.
Speaker: Mark Brown
Topic: Beginning Development for Business - Part II: Building the Foundation
Date: November 22, 2011, 11:00AM EDT
In this second session of the series that focuses on teaching people how to become a software developer, concentrating on business driven software development, we continue to learn the fundamentals of programming and start moving beyond basic data types and move towards custom data types and how they are used. We take a look at basic file creation and loading techniques and how they can be used to do anything from data storage to file generation.
Speaker: Chris Albrektson
Topic: Introduction to SSRS Expressions
Date: November 29, 2011, 11:00AM EDT
In this session, Chris will walk you through the basics of creating SSRS expressions. This will be an all demo session were we cover an array of examples in a short amount of time. This session is designed for someone who might need a crash course into the SSRS expression language or for anyone else that might need a refresher.