Report Builder Vs BIDS

Many people ask this Question they also wonder how different it is from Business intelligence Development studio (BIDS), when to use, which one to use

So this is how it works-

Report Builder

Report builder is a Client tool, Web based interface. It comes with SQL SERVER reporting Services and integrated with Report manager. It allows End User to create, modify and deploy reports on Report Server.

BIDS

is a development tool and should only be used while development process.

S.No	Report builder	BIDS
1	Report builder is client tool for End Users	BIDS is a Development tool , for development purpose or  for developers
2	Use it when you have to dynamic Query. Example - Like  when you already have some reports on Report server and while viewing those reports your end user might think of a new kind of report or want to add something in excising reports than he can click on Report builder Link and create/modify a report and deploy it to the Report Server.	Use it when your development phase is going on. Its development tool, developer does unit testing from his side before deployment. It can be also used to deploy bunch of reports
3	Only one report at a time concept.	Many reports open together and worked with
4	Report Parts can be utilized here and published as well.	Report parts cannot be utilized. But can only be published
5	create all kind of chart, gauges, maps etc	create all kind of chart, gauges, maps etc
6	No Installation required for users.	Separate installation is required.
7	Available only if you have permission	Permission is not applicable here as its for development purpose only. So if you doing the development and you have SQL Server license you can install this tool in your machine.

OLE DB Command Transformation – Bottleneck for the Performance.

In many cases, Most of the time when we use SCD, OLEDB command transformation comes into the pictures.
Well as long as you have 100 – 10,000s records it doesn’t gives you any problems.
But the problem with this task is that it does row by row operation means if you have 10K rows are coming into its input, it’s going to pickup one row perform the operation or say SQL command you have in this task and then it will take another one. Well as it sound it is that lengthy.

In many of my project I found it bottleneck for the performance. And removing it makes my day.
Question here is what to use inserted of it –

In case of SCD –
Use OLEDB destination instead of update “OLE DB Command Transformation”. Insert rows into a temporary table and then use “Execute SQL Task” and do the update using your temporary table.

Remember to drop - create or truncate the temporary table after or just before the start of ETL execution.

Other cases -
Well same thing, use temporary tables, Execute SQL task to do the same.


Now where to use it – well when you have very less records for which you have to run OLEDB command or when you are rejecting and while rejection you might want to update some flag.

Thumb Rule here is Avoid this task when you have Bulk data.

Secondary Axis in SSRS Chart

Secondary Axis Chart

From SSRS 2008 we have Secondary Axis option, ie we can have two measures on two different axis

To do so on your normal chart Drop another Measure/ Numeric Field in “Data Filed” of chart.

Just like following

Then Right click on the second data filed , click on change cart type and then select line chart.

Result will be something like following

After that right click on 2^nd data filed which you made it line chart and go to the series property

è On property window Now go to” Axes and chart Area tab”  à select value axis as a secondary

Press ok and preview your chart.

Now you have two axis in one chart for different data filed like following

ToolTip in SSRS Charts

Well many of us had problem for not having tool tip option in SSRS.

From SQL SERVER 2008 version you have tooltip options in chart.

Just right click on the series and go to series property

On series property you will have Tooltip property option, where you can define what do want to show on tool tip

This is it, set the property according to your requirement and you are good to go. You can see some tool tip just like following -

SSRS 2008/2008 R2 New Features

Data Source

Microsoft SQL Azure data source – connects to SQL SERVER database in cloud.

Microsoft SQL Server Parallel Data Warehouse data source – Connect SQL SERVER parallel data warehouse.

Haven’t really worked with this one. ;).so not much clue on this one.

Microsoft SharePoint List data source – Connects to SharePoint List and pull information from there.

Lot of clients ask for this one and is really is a charm.

Secondary Axis Charts:

Now SSRS Reports can have two Axis. Which allow us to create 2 types of chart in a single chart area, Like combination of bar and Line chart. That has been asked by many clients in past, I am glad now this can be easily achieved.

Tool tip functionality:

Now we can have tooltip on the data point’s gives end user to get the exact number at any data point of a chart.

Sparklines, Data Bars and Indicators

Sparklines and data bars are charts that can be used within table or matrix. This will help to compare information at same time.

Indicators are red, yellow and green light or symbol to visualize data in row.

Text Rotation

Text Rotation Functionality is available, now we can rotate text to 270 degree make it fit into our text boxes.

New Charts and Gauges

Charts and gauges have been improved. They look more attractive.

Map and Spatial data functionality

New Map features allow you to connect your spatial data with Map. Mapping was the key element missing from SQL Server reporting. Including it made SSRS a complete Reporting package.

Integration with SharePoint 2010

Is more effective. Now subscriptions and drill-through links will work directly. End User can access new report builder from SharePoint and create and deploy reports.

Well that’s all for now, will be working on this individual point and publish individual example for every one of this features. Thanks.

Refresh a View

Many time we use “Select *” in our view definition or say in view query.

 Well although it’s not a good practice but some time it can be handy as well.

If In your table column are changing and you have view on it than having “SELECT *” is not a bad idea,

As views are static in SQL SERVER   having “SELECT *” will not reflect the changes you made to your tables.  So whenever column changes (removed/addition) you will get an error while retrieving data from Views.

To fix your views for this problem quickly You can use Stored Procedure Sp_RefreshView to refresh your views.

sp_refreshview [ @viewname= ] 'viewname'

This will refresh the view defination and you will be able to pull data from your view. 

Use SharePoint List Data Source and Destination

There are many scenarios when we need to pull data from SharePoint List.

I have done this many times but by Querying SharePoint Database.

Curtsy to following blogs –

http://weblogs.sqlteam.com/jhermiz/archive/2007/08/15/60288.aspx

Now we have something called SharePoint List Source and Destination available. This makes my job quit easy. Following is reference for the same -

http://msdn.microsoft.com/en-us/library/dd365137(SQL.100).aspx

Good thing is same kind of source are available at Reporting level as well so you can create reports on SharePoint List Data .. Following is reference for same

http://msdn.microsoft.com/en-us/library/ee633650.aspx

MSI File for SSIS Solution Part 1

Well as many developers wondered whether it is possible to have .msi file for SSIS solution.  

Yes we can do it, how …

So here we go –

When you are done with your SSIS development, In Visual Studio go to File à AddàNew project.

Select Project type à Setup and Deployment à Setup Project.

Give it a proper name.

Please remember to perform this activity you need to have Visual Studio as well, only BIDS will not give you this project type.

This will open a File System page. Where you will have different folder where you can add files.

Right click on Application Folder àAddà File

Please browse your .dtsx packages. You can select multiple packages. And click on Open this add all your selected packages in to Setup project.

That’s it.

Once you are done please build the solution and go to Bin Directory.

Here you will see .Msi file when you run this msi file this will extract the all your dtsx packages to the location you provide.

Let me say one more thing “It just work in the way zip files works.”

Denali CTP 1

Denali ,

Microsoft SQL Server’s new project code name Denali ‘s CTP 1 is available to download.

Following is URL for the same.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9&displaylang=en

Check MSDN Reference for it as well

http://msdn.microsoft.com/en-us/library/bb500435(SQL.110).aspx

Using Single Configuration file for all Packages

Update : 03 July 3, 2012 : you need to do delay validation=true in all the packages to make this thing work.

In many case we have different connections in our packages and then we end up doing 2 or more configuration file for a projects.

This is way around by which you can have all configuration string in to one single package say in master and then pass this string to child packages.

Following are steps for it

1.       Keep all connection strings in SSIS variables in you master packages.

      

2.  To pass this connection string to child packages use parent package variable settings of SSIS.

3.       Go to Package configuration of child package and click on add new configuration. Select configuration type as Parent Package variable. Give the parent variable name which is storing connection string. Press next to continue

4.       It will ask for a target property to select. Please scroll throw it and select your connection’s property connection string

5.       Press next, give this configuration a name and then press finish.

And we done here. Now whenever you change the connection string in the variable and run packages with the help of master it will load new connection string,

Sending an Email - Use Email ID which is not existed

In SharePoint, you can configure the outgoing Email by the user ID which is not a valid one or say not existed on SMTP server.
If you want to do same here is how you can go for it.

You can’t use SSIS “Send Mail task” for it as “Send Mail Task” use “Send As” not “Send”. “Send As” required a valid user ID (FROM in your Email). But if you send an email just by “Send” it just uses a SMTP server as connection and sends Email without verifying “FROM Email ID”
So to achieve the same you have to use SSIS Script task.

I found following link very useful for creating a SSIS Script task for Sending Email.

http://consultingblogs.emc.com/jamiethomson/archive/2006/07/03/SSIS_3A00_-Sending-SMTP-mail-from-the-Script-Task.aspx

Many Thanks to them; you made this very easy guy.
Just customize it for following to use an unidentified user.

NewEmailName = New MailAddress("ABCDEF@YourDomain.com", "ABCDEF@YourDomain.com")
myHtmlMessage = New MailMessage(NewEmailName.Address.ToString(),"ReceiverEmailID", "ETL Execution Completed Successfully", "ETL Execution Completed Successfully")

And you are good to go. Nice and easy.
Now you don’t required a specific user id for sending SSIS notifications. 

Use SSIS Script component to pull data from MS Office Word File

Well some time we face some requirements which really frustrate you..

Same scenario I got end up few days back when I have to pull data from a Word Files.

Yup our Microsoft Office Word Files. I have no choice to make it work so I have started looking into it,

Finally I was manage to write a Script task which can pull data from MS Word.

So here we are.. Following is script to pull data from MS Office Word Files…

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Collections.Generic;

using System.Windows.Forms;

using Microsoft.Office.Interop;

using System.Reflection;

using Office = Microsoft.Office.Core;

using Microsoft.Office.Interop.Word;

using Microsoft.VisualStudio.Tools.Applications.Runtime;

using Excel = Microsoft.Office.Tools.Excel;

using Word = Microsoft.Office.Interop.Word;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

public override void PreExecute()

{

base.PreExecute();

}

public override void PostExecute()

{

base.PostExecute();

}

public override void CreateNewOutputRows()

{

Microsoft.Office.Interop.Word.ApplicationClass wordApp = new Microsoft.Office.Interop.Word.ApplicationClass();

object file = "D:\\AESMetricsforMarch2010.doc"; // Specify path for word file

object nullobj = System.Reflection.Missing.Value;

Microsoft.Office.Interop.Word.Document doc = wordApp.Documents.Open(ref file, ref nullobj, ref nullobj,

ref nullobj, ref nullobj, ref nullobj,

ref nullobj, ref nullobj, ref nullobj,

ref nullobj, ref nullobj, ref nullobj,

ref nullobj, ref nullobj, ref nullobj, ref nullobj);

int count = 0;

int i = 1;

foreach (Microsoft.Office.Interop.Word.Table table in doc.Tables)

{

Microsoft.Office.Interop.Word.Table firstTable = doc.Tables[i];

foreach (Microsoft.Office.Interop.Word.Row row in firstTable.Rows)

{

List<string> cellValues = new List<string>();

foreach (Microsoft.Office.Interop.Word.Cell cell in row.Cells)

{

string cellContents = cell.Range.Text;

// add the cell contents to the array, but remove the strange termination character on the end of the data

cellValues.Add(cellContents.Remove(cellContents.Length - 1));

}

// the first row is the column header

if (count == 0)

{

// form.AddHeader(cellValues);

}

else

{

Output0Buffer.AddRow();

Output0Buffer.Column = cellValues[0].ToString();

Output0Buffer.Column1 = cellValues[1].ToString();

Output0Buffer.Column2 = cellValues[2].ToString();

Output0Buffer.Column3 = cellValues[3].ToString();

Output0Buffer.Column4 = cellValues[4].ToString();

}

count++;

}

i++;

}

}

}

Please note that you have to include some of the office assembly in your SSIS Script Component reference.