Sep 27, 2011

Convert Physical Machines to Virtual Machines

VMware Converter is designed to do the following:
  • convert local and remote physical servers with NO DOWNTIME
  • convert many P2V conversions at the same time with a centralized console
  • convert third party VMs to VMware. For example- MS Virtual PC, MS Virtual Server, Backup Exec LiveState, & Ghost.
  • Clone and backup physical machines to virtual machines as part of a DR plan.
VMware Converter comes in two flavors. They are:
  • VMware Converter Starter (FREE Edition)
  • VMware Converter Enterprise
Currently, VMware Converter is only supported to convert Windows systems from physical to virtual


Using VMware Converter

When VMware Converter starts, you will be asked if you want to enter a license (to use the Enterprise version) or move into Starter mode. To go into Starter mode, click Continue in Starter Mode.
There are two purposes for VMware Converter:
1) Import a Virtual Machine from a physical machine or other type of virtual machine
2) Configure Virtual Machine to make an existing image bootable
In our case, we are going to demonstrate how you can import a hard drive from a physical machine (while that machine is running), change the drivers on it to be VMware drivers, and boot it in VMware. The first step of this is importing the virtual machine.

Importing Virtual Machines

To Import a virtual machine, just click on the Import Machine button the top left side of the interface.

Next, you'll see the Import dialog box come up. Click Next twice. Select the type of source to import from:

Sep 21, 2011

Event Handler Using SSIS Designer

To create an event handler
  1. In Business Intelligence Development Studio, open the Integration Services project that contains the package you want.
  2. In Solution Explorer, double-click the package to open it.
  3. Click the Event Handlers tab.
  4. In the Executables list, select the executable for which you want to create an event handler.
  5. In the Event handler list, select the event handler you want to build.
  6. Click the link on the design surface of the Event Handler tab.
  7. Add control flow items to the event handler, and connect items using a precedence constraint by dragging the constraint from one control flow item to another.
  8. Optionally, add a Data Flow task, and on the design surface of the Data Flow tab, create a data flow for the event handler.
  9. On the File menu, click Save Selected Items to save the package.

OnError
The event handler for the OnError event. This event is raised by an executable when an error occurs.
OnExecStatusChangedThe event handler for the OnExecStatusChanged event. This event is raised by an executable when its execution status changes.
OnInformationThe event handler for the OnInformation event. This event is raised during the validation and execution of an executable to report information. This event conveys information only, no errors or warnings.
OnPostExecuteThe event handler for the OnPostExecute event. This event is raised by an executable immediately after it has finished running.
OnPostValidateThe event handler for the OnPostValidate event. This event is raised by an executable when its validation is finished.
OnPreExecuteThe event handler for the OnPreExecute event. This event is raised by an executable immediately before it runs.
OnPreValidateThe event handler for the OnPreValidate event. This event is raised by an executable when its validation starts.
OnProgressThe event handler for the OnProgress event. This event is raised by an executable when measurable progress is made by the executable.
OnQueryCancelThe event handler for the OnQueryCancel event. This event is raised by an executable to determine whether it should stop running.
OnTaskFailedThe event handler for the OnTaskFailed event. This event is raised by a task when it fails.
OnVariableValueChangedThe event handler for the OnVariableValueChanged event. This event is raised by an executable when the value of a variable changes. The event is raised by the executable on which the variable is defined. This event is not raised if you set the RaiseChangeEvent property for the variable to False. For more information, see Integration Services Variables.
OnWarningThe event handler for the OnWarning event. This event is raised by an executable when a warning occurs.



Sep 20, 2011

Charts and VBA in Excel

We have here the data of four good IT companies, namely, Wipro, TCS, Infosys and HCL. It's actually their balance sheet of the last 5 years. Now let's say we wish to analyze their sales and net profit over the years. We also wish to have charts so that we don't have to browse through many pages of data.
The first thing we did was to extract the relevant data and sort it. Next the data was transferred to another sheet called data. Now from this consolidated data we created charts and placed them in a new sheet called charts. We felt that the whole process could be automated using VBA. But how to get a solution that even a beginner could implement relatively easily. Instead of writing the code we recorded a macro for each step. Then the macros were tweaked a little bit and the complete process is now automated. The charts look like dashboards!
The first macro is recorded as follows:

·         Select the years, sales and net profit/loss rows (A3:F4, press CTRL and select A21 to F21)
·         Now click on the copy icon on the toolbar
·         Next click in Cell J3, click on the arrow next to paste and finally click on transpose
·         Now select the years data 2009 to 2005 and click on sort ascending icon
·         Now select the transposed and sorted data, click on the cut icon and paste the data on the 'data' worksheet.
·         Now select the data of 'Wipro' from the data worksheet and create a chart. Do this for all the four data of the four companies one by one.
All the time you are recording your actions in different macros.
                      

Code of Macro1
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/27/2010 by Family Computer Club
' Selecting, copying, paste special, sorting
'
Sheets(Array("Wipro", "TCS", "Infosys", "HclTecnologies")).Select
Sheets("Wipro").Activate
Range("A3:F4,A21:F21").Select
Range("A21").Activate
Selection.Copy
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("J4:J8").Select
Sheets("Wipro").Select
Application.CutCopyMode = False
Range("J3:L8").Sort Key1:=Range("J4"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("TCS").Select
Range("J3:L8").Sort Key1:=Range("J4"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Infosys").Select
Range("J3:L8").Sort Key1:=Range("J4"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("HclTecnologies").Select
Range("J3:L8").Sort Key1:=Range("J4"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Code of Macro2
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 4/27/2010 by Family Computer Club
' cutting and pasting
'
Sheets("Wipro").Activate
Range("J3:L8").Select
Selection.Cut
Sheets("Data").Select
Range("B2").Select
ActiveSheet.Paste
Range("B10").Select
Sheets("TCS").Select
Range("J3:L8").Select
Selection.Cut
Sheets("Data").Select
ActiveSheet.Paste
Range("B18").Select
Sheets("Infosys").Select
Range("J3:L8").Select
Selection.Cut
Sheets("Data").Select
ActiveSheet.Paste
Range("B26").Select
Sheets("HclTecnologies").Select
Range("J3:L8").Select
Selection.Cut
Sheets("Data").Select
ActiveSheet.Paste
Sheets("Data").Select
End Sub

VBA code of Macro3
Sub Macro3()
'
' Macro3 Macro
' Macro recorded 4/27/2010 by Family Computer Club
' charts and tweaking of chart size and position
'
Sheets("Data").Activate
Range("B2:D7").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Data").Range("B2:D7"), PlotBy:= _
xlColumns
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).XValues = "=Data!R3C2:R7C2"
ActiveChart.SeriesCollection(2).XValues = "=Data!R3C2:R7C2"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Wipro"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "year"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Rs."
End With
With ActiveChart.Parent
.Left = 50
.Width = 300
.Top = 15
.Height = 180
End With
'ActiveSheet.Shapes("Wipro2").ScaleWidth 0.61, msoFalse, msoScaleFromTopLeft
'ActiveSheet.Shapes("Wipro2").ScaleHeight 0.5, msoFalse, msoScaleFromTopLeft
'ActiveSheet.Shapes("Wipro2").IncrementLeft -185.25
'ActiveSheet.Shapes("Wipro2").IncrementTop -86.25
'ActiveSheet.Shapes("Wipro2").ScaleHeight 1.46, msoFalse, msoScaleFromTopLeft
'ActiveWindow.Visible = False
Windows("charts-and-vba-in-excel.xls").Activate
Range("I2").Select
End Sub

The VBA codes for Macro4, 5 and 6 are almost the same. Now these chart macros don't work. So need to need to deactivate the relevant lines by placing an apostrophe before them. The extra code added does the job of resizing and placing the charts in their proper positions. You can play around with the program to see how it works when you change the title or width of the chart.

Import data into Excel from another Excel file

The import of data is implemented as follows:
  • Click on the data menu in the ribbon
  • Then click on 'Get External Data' and from the sub menu select 'from other sources
  • From the drop down options select 'From Microsoft Query'
  • In the 'Choose Data Source' window click on 'Excel files*' and 'OK'.
  • Then select the file from which you wish to import the data. Click 'OK'In the 'Query Wizard' window you will see the sheets of the file listed only if you have selected 'Tables' and 'System Tables' via the 'Options...' feature.
  • Next you can select the fields that you wish to import, filter the data based on criteria and also sort the data
  • Now you can save tha query with an appropriate name and then select 'Return Data to Micrososft Excel' option button
  • Finally click finish to import the data starting from a cell address of your choice
  • You can also save the file with the imported data as an Excel template file which will be filled with data when you open it again and run the saved query!

Sep 13, 2011

Creating a Basic Drilldown Report in SSRS 2005

A drill down report starts with a higher level set of data (overview) and allows you to “drill down”, or move through, the data into lower levels, getting into more specific details as you go.
So to start, we’ll need a set of data. Just so that we’re on the same page lets create the following stored procedure.
tsql

CREATE PROCEDURE dbo.DrilldownExample
    -- Add the parameters for the stored procedure here
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for procedure here
   
DECLARE @TABLE TABLE (ProductType NVARCHAR(20), ProductName NVARCHAR(50), ProductCost DECIMAL(20,4), CustomerName NVARCHAR(50), Quantity INT, PurchaseDate DATETIME)

INSERT INTO @TABLE VALUES ('Video Game', 'Halo 3', 50, 'Bob Dylan', 4,'4/1/09')
INSERT INTO @TABLE VALUES ('Video Game', 'Halo 3', 50, 'George Allen',1,'4/1/09')
INSERT INTO @TABLE VALUES ('Video Game', 'GTA4', 50, 'George Allen',1,'4/7/09')
INSERT INTO @TABLE VALUES ('Video Game', 'Super Smash Brothers', 25, 'George Allen',1,'1/28/09')
INSERT INTO @TABLE VALUES ('Console Accessorie', 'Xbox 360 Controller', 50, 'Bo Peep',3,'4/27/09')
INSERT INTO @TABLE VALUES ('Console Accessorie', 'PS2 Memory Card', 15, 'Steve Hobs',2,'4/27/09')
INSERT INTO @TABLE VALUES ('Music CD', 'Psychostick - We Couldnt Think of a Title', 10,'Dave Westcalf',1,'4/27/09')
INSERT INTO @TABLE VALUES ('Music CD', 'Now 1', 5, 'Gill Bates',1,'3/22/09')
INSERT INTO @TABLE VALUES ('Music CD', 'Now 2', 5, 'Gill Bates',1,'3/22/09')
INSERT INTO @TABLE VALUES ('Music CD', 'Now 3', 5, 'Gill Bates',1,'3/22/09')
INSERT INTO @TABLE VALUES ('Music CD', 'Kidz Bop 1', 5, 'Gill Bates',1,'3/22/09')
INSERT INTO @TABLE VALUES ('Network Gear', 'Ethernet Cable - 05ft', 10, 'William Bonk',10,'4/27/09')
INSERT INTO @TABLE VALUES ('Network Gear', 'Ethernet Cable - 10ft', 20, 'William Bonk',15,'4/27/09')
INSERT INTO @TABLE VALUES ('Network Gear', 'Ethernet Cable - 20ft', 35, 'Bob Dylan',8,'4/1/09')
INSERT INTO @TABLE VALUES ('Network Gear', 'Ethernet Cable - 20ft', 35, 'Bob Dylan',8,'2/18/09')
INSERT INTO @TABLE VALUES ('Network Gear', 'Ethernet Cable - 05ft', 10, 'Karen Chase',2,'4/27/09')
INSERT INTO @TABLE VALUES ('Network Gear', 'Linksys 4 Port Router', 55, 'Bob Dylan',2,'4/1/09')
INSERT INTO @TABLE VALUES ('Network Gear', 'Linksys Wireless Network Adaptor', 68, 'William Bonk',5,'4/27/09')
INSERT INTO @TABLE VALUES ('Network Gear', 'NIC Card', 15, 'William Bonk',7,'4/27/09')
INSERT INTO @TABLE VALUES ('Computer Hardware', '80GB Harddrive', 60, 'Lisa Smith',2,'4/27/09')
INSERT INTO @TABLE VALUES ('Computer Hardware', '20in LCD Monitor', 120, 'Lisa Smith',1,'4/26/09')

SELECT
    ProductType,
    ProductName,
    ProductCost,
    CustomerName,
    Quantity,
    PurchaseDate
FROM @TABLE
ORDER BY
    ProductType,
    ProductName,
    CustomerName,
    PurchaseDate
END
GO

 
I like to run my reports off of stored procedures. To me it’s easier because if I need the same data for a different report I can just use the same sp.
Alright, now that we know where we’re pulling the data from, let’s open up Visual Studios. Create a new report project (file – new – project. Business Intelligence Projects, Report Server Project). You can name it whatever you want, I’m going to name it BasicDrillDownExample.
Now we need to create our Data Source. The data source is the database and server we’re going to use to access our data. Right click the Shared Data Sources folder in the Solution Explorer Window and click Add New Data Source.
Click the Edit Button
In the first dropdown list, select the server you saved the sp on. In the database dropdown list, select the Database where the sp is. Click ok.
Now at this point I would set up the Credentials so that when I deploy it the users that I want to use it can. Right now we’ll just deal with creating the report and deal with deploying reports to SSRS at another time.
Don’t forget to give your data source a meaningful name. I usually put the Server Name and Database name as the name.
Click OK. Your data source should now show up in the Solution Explorer.
Right click the Reports folder – click add – click new item. We’re doing it this way because we don’t want to go through the wizard. Click on Report and name the report Drilldown Example. Click Add.
The report should now show up in the solution explorer window, and the report should be open now (if not, double click the report in the solution explorer window)
If not selected, click the Data tab. Click the Dropdown Box next to Dataset and click New Dataset.
Name the Dataset dsDrilldownData. The Data Source should be the one we just created. The command type needs to be stored procedure. In the Query string type dbo.DrilldownExample. Click Ok.
Click the Exclamation mark to execute the query to make sure it works.
Now we’re ready to get our hands dirty. Click the layout tab. Drag a table control from the Toolbox. Expand the dataset (dsDrilldownData) in the Dataset window. In the header of the first column type Product Type. In the header of the second column type Product Name. In the header of the third column type Customer Name.
From the dataset list, drag ProductType onto the detail box below the column header. Repeat this for ProductName and CustomerName. These will be are three groups of the report.
Click on the table to see the gray bars on the top and side of the table. Right click the gray box on the top left of the table, and click properties. The table properties box will pop up. Click the Groups Tab. Click Add.
Name the group grpProductType. In the expression list select =Fields!ProductType.Value. Click the Include group footer to deselect it. Click OK.
Repeat the same process for ProductName and CustomerName. Once all three are done, make sure they’re in the same order as the image.
Click ok. Three rows will have been added, one for each group you created. The group number corresponds with the order of the groups in the group list. Since the Product type should be the first group, click the box and drag the field to where it’s sitting in the row for group one. Product name belongs to group two, so drag it up to the second row. Customer Name is group 3 so drag it to the third row.
We aren’t going to use the bottom 2 rows, so highlight them by click the gray box next to them and hitting the delete buttom.
Let’s go ahead and preview button. As you can see we have data, although it’s very ugly and there’s not a lot there to really tell us anything. Let’s go back to layout view.
Right-click the gray box above the product name column and select Insert Column to Right. A new column will appear. Now we have room to add some data to the product name group. Click and drag the ProductCost field onto the group 2 row of the new column. As you can see, VS automatically adds a sum function to any number data when added to a group. Also notice that it went ahead and filled in Product Cost as the column header.
Product Cost doesn’t need to be summed; instead we want to return the first value for it since it will be the same for the same product. So to change this we will right-click the field and click Expression. Replace the word Sum with the word First. The First function will return the first value it finds. Click ok.
Add a column to the right of Customer Name. Add the quantity field to the CustomerName group. Keep the sum function because this time we want to total the number of products a customer has bought.
Right now if we preview the report we’d have a decent amount of data, but it would be very ugly and somewhat. We’re going to make it a bit easier to read. We’re also going to set up the actual drill down ability.
Click and drag on the gray boxes to the left to highlight all of the rows. In the Properties Window click Border Style and Click Solid. Select the Header row. In the Properties Window click BackgroundColor and click Gainsboro.
Click the Group One row and give it a color. Repeat this with the other two rows, so that you have distinguishing colors for each group.
You should end up with something like this:
Now to set it up so that we can collapse and expand the groups. Right click the table and go to properties. Click the groups tab. Select the grpProductName group and click edit. Click the Visibility tab. Click the hidden radio button. Click the Visibility can be toggled… check box. In the drop down list click ProductType (the name of the box the product type field is in, automatically named when we dragged the data onto the table). Click OK.
Repeat the same process for grpCustomerName, but select ProductName in the dropdown list.
Now our report should be ready! Click preview. The report should load. Next to each product type you should have a plus sign that you can click to drill down. Congrats, you’ve made your first drill down report. If the report doesn’t work, go back and make sure you followed every step.

Sep 8, 2011

Adding Custom Color in SSRS Chart Reports

Do you ever come across a requirement where you need to use user defined colors instead of default colors in a chart (for example Pie Chart).

In this article, I'll explain how to create a Custom Palette and how to use that palette in a Pie Chart.

In the above screen-shot, I am using color Blue, Red, Teal and Gold for the first four values of the Period column and same colors are used in Pie Chart as well.

Here are the steps to achieve this task:

STEP1:
Create your custome color palette in the report using Custom Code in your report. To do so, click Report => Report Properties => Code and copy below code:

Private colorPalette As String() = { "Blue", "Red", "Teal", "Gold", "Green","#A59D93", "#B8341B", "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}

Private count As Integer = 0
Private mapping As New System.Collections.Hashtable()

Public Function GetColor(ByVal groupingValue As String) As String
   If mapping.ContainsKey(groupingValue) Then
      Return mapping(groupingValue)
   End If
   Dim c As String = colorPalette(count Mod colorPalette.Length)
   count = count + 1
   mapping.Add(groupingValue, c)
   Return c
End Function

You can define your own colors in the above function.

STEP2:
In the Pie Chart, select Series Properties and select the Fill option from left side as shown below:



Now write following expression in the Color expression:

=code.GetColor(Fields!Year.Value)

Here Fields!Year.Value is a field of dataset which is used as Chart Category fields.

STEP3:
In the left most column of the tabular report, insert character and in the color property of the textbox write following expression:

=code.GetColor(Fields!Year.Value)

Here I am considing that tabular report is grouped on Fields!Year.Value field.




Thats it. Click on the preview and enjoy your custom colors in charting.

Incremental Load in SSIS

Problem Description: Perform Incremental Load using SSIS package. There is one Source table with ID (may be Primary Key), CreatedDate and ModifiedDate along with other columns. The requirement is to load the destination table with new records and update the existing records (if any updated records are available).

Soultion:
You can use Lookup Transformation where you compare source and destination data based on some id/code and get the new and updated records, and then use Conditoional Split to select the new and updated rows before loading the table.

However, I don't recommend this approach, specially when destination table is very huge and volume of delta is very high.

You can do it in simple steps:
  1. Find the Maximum ID & Last ModifiedDate from destination and store in package variables.
  2. Pull the new and updated records from source and load to a staging table using above variables.
  3. Insert and Update the records using Execute SQL Task
Here is the an step-by-step example to do this:
 
STEP1:
Create a new Package IncrementalLoad.dtsx and add following package variables:


VariableNameDescriptionExamle
DestinationConnStrConnection string for
destination server/db
Data Source=(local);
Initial Catalog=TestHN;
Provider=SQLNCLI10.1;
Integrated Security=SSPI;
Auto Translate=False;
MaxIDMax ID from
destination table
100
MaxModifiedDateMax Date from
destination table
2010:11:10 11:50:20.003
SourceConnStrConnection string for
source server/db
Data Source=(local);
Initial Catalog=TestHN;
Provider=SQLNCLI10.1;
Integrated Security=SSPI;
Auto Translate=False;

STEP2:
Create Source, Destination, and staging tables using below code:

-- Source Table (create in source DB)
IF OBJECT_ID('dbo.TestSource','U') IS NOT NULL
DROP TABLE dbo.TestSource
GO
CREATE Table dbo.TestSource
(
  [ID] [int] IDENTITY(1,1)
  ,[Code] [varchar](10)
  ,[Description]  [varchar](100)
  ,[CreatedDate]  [datetime] NOT NULL default GETDATE()
  ,[ModifiedDate] [datetime] NOT NULL default GETDATE()
)
GO
-- Destination Table (create in destination DB)
IF OBJECT_ID('dbo.TestDestination','U') IS NOT NULL
DROP TABLE dbo.TestDestination
GO
CREATE Table dbo.TestDestination
(
  [ID] [int]
  ,[Code] [varchar](10)
  ,[Description]  [varchar](100)
  ,[CreatedDate]  [datetime] NOT NULL
  ,[ModifiedDate] [datetime] NOT NULL
)
GO
-- Staging Table (create in destination DB)
IF OBJECT_ID('dbo.TestDestinationSTG','U') IS NOT NULL
DROP TABLE dbo.TestDestinationSTG
GO
CREATE Table dbo.TestDestinationSTG
(
  [ID] [int]
  ,[Code] [varchar](10)
  ,[Description] [varchar](100)
  ,[CreatedDate] [datetime] NOT NULL
  ,[ModifiedDate] [datetime] NOT NULL
)
GO
STEP3:
Create two OLE DB Connection Manager, one for Source Server and another for Destination Server.
In the connection manager properties, set the expression ConnectionString with respective variables as shown below:

STEP4:
Drag and drop Execute SQL Task and name it -
Execute SQL Task - Get Max ID and Last ModifiedDate.
Double click on EST and set  following properties:
ResultSet = Single row
Connection = Destination Server
SQLStatement =
SELECT
   ISNULL(MAX(ID) ,0) MaxID,
   ISNULL(MAX(ModifiedDate),'2000-01-01') MaxModifiedDate
FROM TestDestination (NOLOCK)

Drag and drop another Execute SQL Task to Truncate staging table. Rename the task - Execute SQL Task - Truncate staging Table and set following properties:
ResultSet = None
Connection = Destination Server
SQLStatement = Truncate Table dbo.TestDestinationStg

 
Drag and drop Data Flow Task and name it Data Flow Task - Pull New and Updated rows.
Double click on DFT or click on Data Flow tab.
Now drag and drop OLE DB Source and select Source Server in OLE DB connection manager, select SQL command in Data access mode, and write following T-SQL code in SQL command text:
SELECT [ID],[Code],[Description],
[CreatedDate],[ModifiedDate]
FROM TestSource
WHERE [ID] > ? OR [ModifiedDate] >= ?
 
Click on Columns to generate metadata and set Parameters for above query:
 

Now drag and drop OLE DB Destination task and select Destination Server in OLE DB Connection manager, Table or view - fast load in Data access mode, and dbo.TestDestinationSTG in Name of the table or view.
Now click on Mapping to map the metadat with source columns.

Drag and drop one more Execute SQL Task and rename it to Execute SQL Task - Insert and Updated new rows and set following properties:
ResultSet = None
Connection = Destination Server
SQLStatement =
-- INSERT New Records
INSERT INTO TestDestination
([ID],[Code],[Description],
[CreatedDate],[ModifiedDate])
SELECT
   [ID],[Code],[Description],
   [CreatedDate],[ModifiedDate]
FROM TestDestinationStg
WHERE ID > ?


--UPDATE modified records
UPDATE D
SET D.[ID] = S.[ID]
,D.[Code] = S.[Code]
,D.[Description] = S.[Description]
,D.[CreatedDate] = S.[CreatedDate]
,D.[ModifiedDate] = S.[ModifiedDate]
FROM TestDestination D
JOIN TestDestinationStg S
   ON S.ID = D.ID
WHERE
   S.ID <= ? AND
   S.ModifiedDate > D.ModifiedDate

Click on Parameter Mapping and set the parameters as shown below:

Finally package will look like below snapshot:
 

STEP5:
We are done with package development. Its time to test the package. I will test this package in three steps:
1. Insert few dummy records in Source table while keeping Destination table empty.
--CASE 1: First Time Execution
INSERT INTO dbo.TestSource ([Code],[Description])
VALUES ('AAA','American Automobile Association')
WAITFOR DELAY '00:00:01.100' -- delay between two rows
INSERT INTO dbo.TestSource ([Code],[Description])
VALUES ('ABC','Associated Builders & Contractors')
WAITFOR DELAY '00:00:01.150'
INSERT INTO dbo.TestSource ([Code],[Description])
VALUES ('RR','Road Runner')
GO

Now execute the package and check the destination data. You can add Data viewers in Data Flow to see the result at run time as shown below:

2. Insert few more records in Source table to check whether new records are inserted into Destination table.
--CASE 2: Only New Records
INSERT INTO dbo.TestSource ([Code],[Description])
VALUES ('TSL','Trina Solar Limited')
WAITFOR DELAY '00:00:01' -- delay between two rows
INSERT INTO dbo.TestSource ([Code],[Description])
VALUES ('FSLR','First Solar, Inc.')

Now execute the package and check for new records:

3. Insert few more records and update few existing records in Source table and check whether both table are in sync.
--CASE 3 -- New & Updated Records
INSERT INTO dbo.TestSource ([Code],[Description])
VALUES ('LDK','LDK Solar Co., Ltd')


UPDATE dbo.TestSource
SET [Description] = 'Associated Builders and Contractors',
ModifiedDate = GETDATE()
WHERE [Code] = 'ABC'

Finaly check source and destination tables for match. Since I have both Source & Destination tables in one server, I can use EXCEPT command (for this example) which retunrs no records i.e. both the tables are in sync.

ExecutionValue and ExecValueVariable in SSIS

Have you ever used ExecutionValue and ExecValueVariable in SSIS package?

The ExecutionValue property can be defined on the object Task and all tasks have this property. Its up to the developer to do something useful with this. The purpose of this property is to return something useful and interesting about what it has performed along with standard success/failure result.

The best example perhaps is the Execute SQL Task which uses the ExecutionValue property to return the number of rows affected by the SQL statement(s). This could be a useful feature which you may often want to capture into a variable and using the result to do something else. We cann't read the value of a task property at runtime from SSIS but we can use ExecValueVariable to get it.

The ExecValueVariable property exposed through the task which lets us select a package variable. When the task sets the ExecutionValue, the actual value is copied into the variable we set on the ExecValueVariable property and a variable is something we can access and do something with. So if you are interested in ExecutionValue property  then make sure you create a package variable and set the name as the ExecValueVariable.
Below are the steps to implement this:

STEP1:
Create a new package and add below variable.

STEP2:
Drad and drop Execute SQL Task and set the properties as per your requirement. I am using below query in SQLStatement to update Employee table:
UPDATE [TestHN].dbo.Employee
SET [Basic] = [Basic]*2
WHERE [Basic] < 5000

This query updates 4 records.

STEP3:
Set the ExecValueVariable with User::ExecutionValue variable as shown below:
STEP4:
Drag and drop Script Task to display the result of ExecValue variable. Now Execute the package.



Here is the list of few tasks that return something useful via the ExecutionValue and ExecValueVariable:
TaskDescription of ExecutionValue
Execute SQL TaskReturns the number of rows affected by the SQL statement(s).
File System TaskReturns the number of successful operations performed.
File Watcher TaskReturns the full path of the file found.
Transfer Jobs TaskReturns the number of jobs transferred
Transfer Error Messages TaskReturns the number of error messages transferred.
Transfer SQL Server Objects TaskReturns the number of objects transferred.

SQL Database Growth Tracking

In this article I am sharing a simple T-SQL code to track database growth for specific database. This could be a very simple query for SMEs but it can really help newbies:

/*************************************************
Purpose : Track Database Growth for a specific DB
Create By : Hari Sharma
**************************************************/
SELECT
   BackupDate =
   CONVERT(VARCHAR(10),backup_start_date, 111)
   ,SizeInMBs=FLOOR(backup_size/1024000)
FROM msdb..backupset
WHERE
   database_name = DB_NAME() --Specify DB Name
   AND type = 'd'
ORDER BY
   backup_start_date desc

Displaying Dynamic Columns in SSRS Report

Problem: How to display selected columns dynamically in SSRS reports.
Example: A report contains more than 30 fields. Some users want to see only 5 fields, some users 10 fields, and other may want to see 20 fields.
Solution: Add a Report Parameter having the values as the name of all the fields of dataset. Now set hidden expression for each column of the tabular report.

Here is the solution with an example:

STEP1:
Create a report with required dataset. Drag and drop table control and select dataset fields.
In my example, I have following fields in the dataset: Year, Quarter, Month, Date, Product Name, Customer Name, Sales Region, Sales Country, Order Number, Sales Amount.

STEP2:
Create a dataset dsColumns using below query:
SELECT 1 ID, 'Year' AS ColumnName UNION
SELECT 2 ID, 'Quarter' AS ColumnName UNION
SELECT 3 ID, 'Month' AS ColumnName UNION
SELECT 4 ID, 'Date' AS ColumnName UNION
SELECT 5 ID, 'Product Name' AS ColumnName UNION
SELECT 6 ID, 'Customer Name' AS ColumnName UNION
SELECT 7 ID, 'Sales Region' AS ColumnName UNION
SELECT 8 ID, 'Sales Country' AS ColumnName UNION
SELECT 9 ID, 'Order Number' AS ColumnName UNION
SELECT 10 ID,'Sales Amount' AS ColumnName


STEP3:
Create a new parameter with name pDisplayFields and Promt Display Columns as shown below:
In Available Values of Report Parameter Properties wizard, select Get values from a query, select dsColumns in Dataset, ColumName in value field and label field.

In Default Values of Report Parameter Properties wizard, select Get values from a query, select dsColumns in Dataset, ColumName in value field.

STEP4:
Now you have to set the expression to display the colummns which are selected in the pDisplayColumn parameter. Right click on First Column (Year in my example) and click Column Visibility...
Write following expression in Show or hide based on an expression of Column Visibility wizard:
=IIF(InStr(JOIN(Parameters!pDisplayFields.Value,","),"Year")>0,False,True)

Now repeat this expression for all the columns by modify the expression for the respective column name accordingly.

Thats all. Now preview the report. You will see all the columns by default.



Now select required columns in Display Column parameter to modify the report layout at run time.

SSRS Expressions

Reporting Services ExpressionsIn Reporting Services, Expressions are used frequently in reports to control content and report appearance. Expressions are used throughout the report definition to specify or calculate values for parameters, queries, filters, report item properties, group and sort definitions, text box properties, bookmarks, document maps, dynamic page header and footer content, images, and dynamic data source definitions.

Expressions begin with an equal (=) and are written in Visual Basic. Expressions can include a combination of constants, operators, and references to built-in values (fields, collections, and functions), and to external or custom code. Expressions can be one of the following two types:
  • Simple - An expression that is a single reference to an item in a built-in collection, such as, a dataset field, a parameter, or a built-in field. Simple expressions appear on the design surface and in dialog boxes in brackets, such as [FieldName], which represents the underlying expression =Fields!FieldName.Value. You can type simple expressions directly into a text box on the design surface and the corresponding expression text is set as the value of a placeholder inside the text box.
  • Complex - An expression that includes more than a simple reference. Complex expressions appear on the design surface as <>. You can create complex expressions in the Expression dialog box or type them directly into the Property pane.
Examples of SSRS Expression

Using Built-in Fields
Display  Report Execution Time in a textbox:
="Report Execution Time: " & Globals!ExecutionTime

Display Page No in a textbox:
="Page " & Globals!PageNumber & " of " & Globals!TotalPages

Similar way you can use other built-in Fields in expressions e.g. Report Folder, Report Name, ReportServerUrl, UserID, Language etc. as shown below:

Date & Time Functions
The Today() function provides the current date. The expression  =Today() can be used in a text box to display the date on the report, or in a parameter to filter data based on the current date. This function will return date in M/d/yyyy 12:00:00 AM format. You can use Format function to display required format. Some of the popular date formats are given below:
Expression
Output
=FORMAT(Today(),"M/d/yy")8/23/10
=FORMAT(Today(),"MM-dd-yyyy")08-23-2010
=FORMAT(Today(),"MMM-dd-yyyy")Aug-23-2010
=FORMAT(Today(),"MMMM dd, yyyy")August 23, 2010
=FORMAT(DateField,"MMM dd, yyyy hh:mm:ss")Aug 23, 2010 01:43:33
=FORMAT(DateField,"MMM dd, yyyy HH:mm:ss")Aug 23, 2010 13:43:33
=FORMAT(DateField,"MMM dd, yyyy HH:mm:ss.fff")Aug 23, 2010 13:43:33.587
=FORMAT(DateField,"MMM dd, yyyy hh:mm:ss tt")Aug 23, 2010 01:43:33 PM

Note: FormatDateTime function can also be used to format the date field e.g. =FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)

DateAdd - Returns a Date value containing a date and time value to which a specified time interval has been added. this function can be used in an expression to add/substract time(day, month, year, sec etc.) from given date field:
=DateAdd(DateInterval.Month, 6, Parameters!StartDate.Value)

DateDiff - Returns a Long value specifying the number of time intervals between two Date values.
=DateDiff("yyyy",Fields!BirthDate.Value,Today())


DatePart - Returns an Integer value containing the specified component of a given Date value.
=DatePart("q",Fields!BirthDate.Value,0,0)

=DatePart(DateInterval.Quarter,Fields!BirthDate.Value, FirstDayOfWeek.System, FirstWeekOfYear.System)


There are many other Date &Time functions which can be used expression:

String Functions
• Combine more than one field by using concatenation operators and Visual Basic constants. The following expression returns two fields, each on a separate line in the same text box:
=Fields!FirstName.Value & vbCrLf & Fields!LastName.Value

•Format dates and numbers in a string with the Format function.
=Format(Parameters!StartDate.Value, "M/D") & " through " & Format(Parameters!EndDate.Value, "M/D")

•The Right, Len, and InStr functions are useful for returning a substring, for example, trimming DOMAIN\username to just the user name. The following expression returns the part of the string to the right of a backslash (\) character from a parameter named User:
=Right(Parameters!User.Value, Len(Parameters!User.Value) - InStr(Parameters!User.Value, "\"))

The following expression results in the same value as the previous one, using members of the .NET Framework System.String class instead of Visual Basic functions:
=User!UserID.Substring(User!UserID.IndexOf("\")+1, User!UserID.Length-User!UserID.IndexOf("\")-1)


Join - Display the selected values from a multivalue parameter
=Join(Parameters!MyParameter.Value,",")

•The Regex functions from the .NET Framework System.Text.RegularExpressions are useful for changing the format of existing strings, for example, formatting a telephone number. The following expression uses the Replace function to change the format of a ten-digit telephone number in a field from "nnn-nnn-nnnn" to "(nnn) nnn-nnnn":
=System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(\d{3})[ -.]*(\d{3})[ -.]*(\d{4})", "($1) $2-$3")


There are many other function which can be used in expression as shown below:

Conversion Functions

You can use Visual Basic functions to convert a field from the one data type to a different data type.

  • The following expression converts the constant 100 to type Decimal in order to compare it to a Transact-SQL money data type in the Value field for a filter expression: =CDec(100)
  • The following expression displays the number of values selected for the multivalue parameter MyParameter: =CStr(Parameters!MyParameter.Count) 
Decision Functions

The IIF function returns one of two values depending on whether the expression is true or false. The following expression uses the iif function to return a Boolean value of True if the value of Total exceeds 100. Otherwise it returns False:
=IIF(Fields!Total.Value > 100, True, False)

Use multiple IIF functions (nested IIFs) to return one of three values depending on the value of PercentComplete. The following expression can be placed in the fill color of a text box to change the background color depending on the value in the text box.
=IIF(Fields!PercentComplete.Value >= 10, "Green", IIF(Fields!PercentComplete.Value >= 1, "Blue", "Red"))

A different way to get the same functionality uses the Switch function. The Switch function is useful when you have three or more conditions to test. The Switch function returns the value associated with the first expression in a series that evaluates to true:
=Switch(Fields!PercentComplete.Value >= 10, "Green", Fields!PercentComplete.Value > 1, "Blue", Fields!PercentComplete.Value = 1, "Yellow", Fields!PercentComplete.Value <= 0, "Red",)

A third way to get the same functionality uses the Choose function. The Choose function uses the first parameter as an index to one of the remaining function parameters. The first parameter must be an integer. If the background color of a text box in a table is set to this expression, the value of MyIndex controls the color.
=Choose(Fields!MyIndex.Value,"Red","Green","Yellow")

Check the value of the PurchaseDate field and return "Red" if it is more than a week old, and "Blue" otherwise. This expression can be used to control the Color property of a text box in a report item:
=IIF(DateDiff("d",Fields!PurchaseDate.Value, Now())>7,"Red","Blue")