SQL Server Solutions

Numeric gets converted to Text in Excel from SSIS

2011-10-20T14:13:00.002+05:30

This is with reference to a question asked in MSDN forum where a User was trying to fill an Excel sheet with one the Numeric Column and field was changing to Text.

Problem:
Lets see the problem with an example I have a simple table with following structure
CREATE TABLE Emp
( ID INT IDENTITY (1,1)
, [Name] VARCHAR (10)
, Salary NUMERIC (10,2)
);

Here ID and Salary are Integer and Numeric receptively and if we try to export them to Excel using SSIS, Excel will store the correct values but would convert them into Text as below( see the Green mark over cell)

Unforunatly there is no way that we can control or change the format/ datatype of a column of Excel sheet form SSIS.Even if we change the datatype using Advance Editor in Excel Destination , it wont reflect the change.

Solution:
So is there is solution to this, well not excatly a solution but a workaround Yes.
When Excel decide about format of cells in a column it takes sample from first few cells (other than first row-header) and keeps the same format for rest of cells down below. We can use this property of Excel and create one dummy row with the format as we are excepting for rest of the cells.

Now during actual writing of data Excel will pick the format from Dummy Row and keep it for rest of them. You can even hide Dummy Row after putting some values in it.

Cheers!!

SSRS Interview Questions

2011-06-28T14:07:00.004+05:30

Q1. WHAT is SQL Server Reporting Services(SSRS)?
SQL Server Reporting Services is a server-based reporting platform that you can use to create and manage tabular, matrix, graphical, and free-form reports that contain data from relational and multidimensional data sources. The reports that you create can be viewed and managed over a World Wide Web-based connection

Q2. Architecture of SSRS:

-Admin

Q3. What are the three stages of Enterprise Reporting Life Cycle ?
a. Authoring
b. Management
c. Access and Delivery

Q4. What are the components included in SSRS?
1. A Complete set of Tools that can be used to create, manage and view reports
2. A Report Server component that hosts and processes reports in a variety of formats. Output formats include HTML, PDF, TIFF, Excel, CSV, and more.
3.An API that allows developers to integrate or extend data and report processing in custom applications, or create custom tools to build and manage reports.

Q5. What is the benefit of using embedded code in a report?
1. Reuseability of Code: function created in embedded code to perform a logic can be then used in multiple expressions
2. Centralized code: helps in better manageability of code.

Q6. Which programming language can be used to code embedded functions in SSRS?
Visual Basic .NET Code.

Q7. Important terms used in the reporting services?

1. Report definition: The blueprint for a report before the report is processed or rendered. A report definition contains information about the query and layout for the report.

2. Report snapshot: A report that contains data captured at a specific point in time. A report snapshot is actually a report definition that contains a dataset instead of query instructions.

3. Rendered report: A fully processed report that contains both data and layout information, in a format suitable for viewing (such as HTML).

4. Parameterized report: A published report that accepts input values through parameters.

5. Shared data source: A predefined, standalone item that contains data source connection information.

6. Shared schedule: A predefined, standalone item that contains schedule information.

7. Report-specific data source: Data source information that is defined within a report definition.

8. Report model: A semantic description of business data, used for ac hoc reports created in Report Builder.

9. Linked report: A report that derives its definition through a link to another report.

10. Report server administrator: This term is used in the documentation to describe a user with elevated privileges who can access all settings and content of a report server. If you are using the default roles, a report server administrator is typically a user who is assigned to both the Content Manager role and the System Administrator role. Local administrators can have elevated permission even if role assignments are not defined for them.

11. Folder hierarchy: A bounded namespace that uniquely identifies all reports, folders, report models, shared data source items, and resources that are stored in and managed by a report server.

12. Report Server: Describes the Report Server component, which provides data and report processing, and report delivery. The Report Server component includes several subcomponents that perform specific functions.

13. Report Manager: Describes the Web application tool used to access and manage the contents of a report server database.

14. Report Builder: Report authoring tool used to create ad hoc reports.

15. Report Designer: Report creation tool included with Reporting Services.

16. Model Designer: Report model creation tool used to build models for ad hoc reporting.

17. Report Server Command Prompt Utilities: Command line utilities that you can use to administer a report server.
a) RsConfig.exe, b) RsKeymgmt.exe, c) Rs.exe

Q8. what are the Command Line Utilities available In Reporting Services?
· Rsconfig Utility (Rsconfig.exe): encrypts and stores connection and account values in the RSReportServer.config file. Encrypted values include report server database connection information and account values used for unattended report processing
· RsKeymgmt Utility: Extracts, restores, creates, and deletes the symmetric key used to protect sensitive report server data against unauthorized access
· RS Utility: this utility is mainly used to automate report server deployment and administration tasks.Processes script you provide in an input file.

Q. How to know Report Execution History?
ExecutionLog table in ReportServer database store all the logs from last two months.
SELECT * FROM ReportServer.dbo.ExecutionLog

-Development
Q. What is difference between Tablular and Matrix report?
OR What are the different styles of reports?

Tablular report: A tabular report is the most basic type of report. Each column corresponds to a column selected from the database.

Matrix report: A matrix (cross-product) report is a cross-tabulation of four groups of data:
    a. One group of data is displayed across the page.
    b. One group of data is displayed down the page.
    c. One group of data is the cross-product, which determines all possible locations where the across and down data relate and places a cell in those locations.
    d. One group of data is displayed as the "filler" of the cells.
Martix reports can be considered more of a Pivot table.

Q. How to create Drill-through reports?
Using Navigation property of a cell and setting child report and its parameters in it.

Q. How to create Drill-Down reports?
To cut the story short:
- By grouping data on required fields

-Then toggle visibility based on the grouped filed

Q. How to select ALL from a parameter list?
http://sqlserversolutions.blogspot.com/2011/03/select-all-in-parameter-of-ssrs-report.html

(Work in progress, more to follow)

Might want to read SSIS Interview quesitons

Check out Whats happening on the Report Server

2011-06-06T14:42:00.001+05:30

Sometime it may happen that Report server may work slow and you might want to know whats really happening in the server and which reports are in use to get a lead.

1. To know which reports are running on you system is to check ExecutionLog table in ReportServer database of your server.

SELECT * FROM ReportServer.dbo.ExecutionLog

This table contains the history of which report is executed at which time, taking how much time and by whom. This can lead to the report which is taking most time.

But the draw back of this table is that it does not get information about subscription reports. And when I look into any Report Server first thing I look for is Reports Subscriptions. Those are little hidden ones and take work in background with letting anyone who that how much resource and time they are consuming.

2. Next method of knowing whats happening in our Report Server is through Manage Jobs page on ReportServers SiteSettings

This displays and lets user to cancel any In-Progress Report or Subscription. Only limition is that it will display only in-progress reports/subscription which are in-progress for atleast 60 seconds.

Data-dump in Dynamic Flat file with todays date

2011-05-10T11:32:00.000+05:30

This is with reference to a question asked my an online friend regarding how can we do a daily Datadump on a flat file with a todays date as a file name.

To show that I will use an example in which we will dump a table "Student" from SQL Server database into a file with name appended with todays date and time eg. DataDump_201105101108.txt. 20110510 being the date and 11:08 as time of load.

1. Lets have a look at our table.

2. Now create a simple package with a Data Flow task which dumps data from Student to a flat file let say with Dummy.txt. SSIS is metadata oriented so at the time of creation of package you would need to have a dummy txt file so that mappings can be created.

3. Not the actual work starts. Go the Flat file Connection Managers Properties and expand Expressions.
Choose ConnectionString as Property.

4. In Expression Builder window create a expression as
"E:\\DataDump_" + (DT_WSTR,4)DatePart("yyyy", GetDate()) + RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("dd", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("hh", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("mi", GetDate()), 2) + ".txt"

5. Run the package it will create a flat file with required File name.

--Cheers

Can't Expand MSDB in Integration Services Server

2011-04-14T15:42:00.004+05:30

TCP Provider: No connection could be made because the target machine actively refused it. (MsDtsSrvr)

"Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Native Client)"

Other day one of the team here had to work on a fairly static server which has few SSIS packages installed on it. The Team wasn't able to expand MSDB node to view packages installed on the Intergration Services.
Error they were receiving was:

Steps to check if you are not able to expand MSDB in Integration Services:
1. User has permission on MSDB database as SSIS pacakges are stored in MSDB

2. Check if SQLBrowser services is running. Run > Services.msc

3. Check if TCP-IP/Named pipe are enabled for the Sever instance from SQL Server Configuration Manager

4. Windows firewall is not blocking SQL Sever access. Add SQLServer.exe or port 1433 to exceptions.

5. Find MsDtsSrvr.ini file at drive:\Program Files\Microsoft SQL Server\90\DTS\Binn (for 2008 find in 100\DTS\bin folder) and check for Server tag if it is <ServerName>.ServerName> (this was the case with the team here :-) )

If yes then replace "." with Servername\instance name and restart Integration Sevices.

Have fun!!

Select ALL in parameter of SSRS report

2011-03-22T11:07:00.002+05:30

Select ALL as parameter value is one of the most common functionality which most of the reports have and there are number of ways to implement it.

I will explain below the way I do it and I find easiest. The driver of the solution is CASE option under WHERE clause of SELECT DataSet Query.

        SELECT * FROM TableName
         WHERE
         (
               CASE
                      WHEN @RepParam <> 'ALL' AND ColName= @RepParam THEN 1
      WHEN @RepParam= 'ALL' THEN 1
   END
         ) = 1 ;

Lets see it through an simple Student Table example where we will Select student either on the basis of the Grade they are in or select all of them.

1. Records in Student table are

2. Create a simple report with DataSet Student as SELECT * FROM Student;

3. To add the Grade parameter and option for Select ALL

3.a. Create a DataSet for available Grades for Report parameter

3.b. Configure a Report parameter "Grade" as

3.c. Modify query for Student Dataset to allow filtering on Grades as

4.a. Run the Report for ALL grades

4.b. Run the Report for grade - X

Cheers!!

Execute Scripts from a folder using Powershell

2010-12-10T11:21:00.004+05:30

For last few days I am kinda got wired up with Powershell and trying to do whatever I used to do otherwise using SQL PowerShell e.g. other day we had some 40 odd scripts to be deployed on our database so I worte a simple 2 liner SQL PS script which would go and execute each script one by one.

1. To illustrate I have 3 Scripts in a folder called ScritpsFolder to create 3 Stored Procedures

2. Script inside sp_A

3. Now the Powershell code to execute scripts from the folder.

ForEach ($S In Gci -Path "C:\ScriptsFolder\" -Fliter *.sql | Sort-Object Name )
{
Invoke-SqlCmd -InputFile $S.FullName
}

a) ForEach : will iterate on each item insire $S.
b) Gci (Get-Child Item): will get all the filenames from given path and will store in variable $S.
c) Invoke-SqlCmd: will take the script and run it on the Database.

To run the script you should be in context of your SQL Server/Database and particular database if it not defined in the script.

4. Check the SPs created in the database

Execution Tree in SSIS

2010-12-01T15:26:00.006+05:30

I have been asked many times, the way we can see Execution Query Plan for Queries in SSMS, Can we see something similar in SSIS too. Well YES WE CAN, it wont give you factors like I/O, CPU or SubTree Cost but will give you fair idea of path and buffers SSIS engine will create to do the DATA FLOW.

At run time Data Flow Engine divides the Data Flow Task operations into Execution Trees which demonstrate how package uses buffers and threads. These execution trees specify how buffers and threads are allocated in the package.

Each tree creates a new buffer and may execute on a different thread. When a new buffer is created such as when a partially blocking or blocking transformation is added to the pipeline, additional memory is required to handle the data transformation; however, it is important to note that each new tree may also give you additional worker thread.[TechNet]

Let's take an example to show the Execution Tree in the Data Flow Task. I will create a simple Data Flow Task and have two flows in it.

1) Direct transfer of data from SrcEmployee table to DestEmployee table.
2) SrcDepartment to DestDepartment by having one Sort Component in between to sort on DepName.

To see Execution Tree log you will have enable build-in logging and to see log entries add Log Event window from View>OtherWindows.

Now when you run package, SSIS will log an entry for User:PipelineExecutionTrees which discribes the Trees/paths SSIS has created to run the Package.

Message:

Begin Path 0 [Tree 1]
   output "OLE DB Source Output" (11); component "SrcEmployee" (1)
   input "OLE DB Destination Input" (29); component "DestEmployee" (16)
End Path 0

Begin Path 1 [Tree 2]
   output "OLE DB Source Output" (124); component "SrcDepartment" (114)
   input "Sort Input" (147); component "SortDepartment" (146)
End Path 1

Begin Path 2 [Tree 3]
   output "Sort Output" (148); component "SortDepartment" (146)
   input "OLE DB Destination Input" (142); component "DestDepartment" (129)
End Path 2

Happy Extraction and Inception!!

MERGE Statement in SQL SERVER 2008

2010-11-16T14:34:00.002+05:30

SQL SERVER 2008 has introduced MERGE statement which is very helpful in synchronizing two tables.We can perform INSERT, UPDATE, or DELETE operations on target table based on the result of the join with the source table.

Well Syntax for MERGE is very complex so I will use a very simple example where I will create two table SOURCE and TARGET and then perform insert, update and delete on SOURCE table and then synchronize TARGET table using MERGE Statement.

1. Lets create two tables Source and Target with same schema and data and after updates in Source we will sync it with Target table.

CREATE TABLE SOURCE
(
ID INTEGER,
Name VARCHAR(50),
);
GO
INSERT INTO SOURCE
( ID, Name)
VALUES
(1, 'Rahul'),
(2, 'Mark'),
(3, 'Jen');

SELECT * INTO TARGET FROM SOURCE;

2. Lets modify data in Source table ( Delete , update and Insert new record).

DELETE FROM SOURCE
    WHERE ID =2;
--Update in source
UPDATE Source
    SET Name = 'Jason'
WHERE Id = 3;

-- new record in source
INSERT INTO SOURCE
    (ID, Name)
VALUES
    (4, 'Antonia');

3. Now comes the MERGE Statement
Source table will be joined with Target table on ID and then WHEN clause is used to identify type of changes
a.Update: Where both ID match but other columns doesnt match.
MATCHED and Target.Name <> Source.Name
b. New/Inserts: When IDs are not matched by Target
c. Deletes: When IDs are not matched by Source.

MERGE INTO TARGET
USING (SELECT * from Source) AS SOURCE
ON Target.ID = Source.ID
WHEN -- upadate
    MATCHED and Target.Name <> Source.Name THEN
        UPDATE
        SET Name = SOURCE.Name
WHEN -- new record in source
    NOT MATCHED BY TARGET THEN
        INSERT ( ID ,Name)
        VALUES ( Source.ID,Source.Name)
WHEN --records deleted in source
    NOT MATCHED BY SOURCE THEN
        DELETE
--see action
OUTPUT $action
    , Inserted.ID AS InsertedID
    , Inserted.NAME AS InsertedName
    , Deleted.ID AS DeletedID
    , Deleted.Name AS DeletedName;

I have used an OUTPUT to see operations performed by MERGE Statement.

4. Tables after MERGE

Happy Coding!!

Script Out Database objects using PowerShell

2010-08-30T12:05:00.001+05:30

PowerShell is a very powerful tool and sometimes provide an amazing way for accomplishing which otherwise would have taken lot of effort and time.. One such scenario I had recently when I had to script out few stored procedure from my SQL Sever Database. There we go.. Below I will explain how we can script out Database Objects using PowerShell.

Lets get rolling and start PowerShell- We start PowerShell directly from SSMS by Right clicking any node and select Start PowerShell. It will open PowerShell connected to your Server

I will script Stored Procedures from the Database and I will show various options of doing that.

First let get down to Stored Procedures

1. First thing first .. let try to display our Stored Procedure on Screen

gci *uspGetAllNodes* | %{$_.Script()}

2. Displaying it on Screen doesnt help much, lets Sript-Out this in a file

gci *uspGetAllNodes* | %{$_.Script()} | Out-File c:\Scripts\uspGetAllNodes.sql

3. Thats cool we can get one Stored Procedure, how about getting all the StoredProcedures

gci | %{$_.Script()} | Out-File c:\Scripts\AllStoredProcs.txt

4. Great, but what if I have few Stored procedures to extract and I dont what to run the script again and again.

Use an Array Variable.

$SPNameList = @("uspGetAllNodes", "uspGetAllPaths")
FOREACH ( $SP in $SPNameList)
{
gci *$SP* | %{$_.Script()} | out-file c:\Scripts\$SP.sql
}

5. OutPut Directory

Thanks!!

Data from Dynamic Sheets of an Excel

2010-08-22T19:09:00.002+05:30

This is with reference to a Question asked on the blog:

Question:

How to get data from different sheets in an Excel WorkBook having same column names?

Solutions:

Basic idea to get the done is get name of all the sheets in an variable and then iterate over that variable and get data from respective Sheet.

To Demonstrate I have created an Excel sheet having 3 sheets having only one column "ColumnName"

1. Excel WorkBook

2. Now lets create the package with below variables

3. Variable QueryStmt will hold a dynamic value which would be used as SQL Command passed to Excel.

Check EvaluateAsExpression Property as True and set Expression as

4a. Next Step is to use Script Task to get all the sheet names in Variable SheetNamesList.

4b. Write below code in Script Task to get all the sheets names in SheetNamesList Variable.

Directive needed using System.Data.OleDb;

        public void Main()

        {

            OleDbConnection con = null;

            System.Data.DataTable dt = null;

            string ExlPath = Dts.Variables["ExcelPath"].Value.ToString();



            String conStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +

                "Data Source=" + ExlPath + ";Extended Properties=Excel 8.0;";

            con = new OleDbConnection(conStr);

            con.Open();

            dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);



            String[] excelSheetNames = new String[dt.Rows.Count];

            int i = 0;

            foreach (DataRow row in dt.Rows)

            {

                excelSheetNames[i] = row["TABLE_NAME"].ToString();

                i++;

            }

            Dts.Variables["SheetNameList"].Value = excelSheetNames;

            // TODO: Add your code here

            Dts.TaskResult = (int)ScriptResults.Success;

}

5 Create a For Each Loop to iterate over SheetNameList

b. Set the index to SheetName to get counter sheet name

6. Add a DATA FLOW TASK and use Excel Source to connect to the WorkBook and Set the properties as

7. Thats it.. Execute the package and get the data...

Thanks!!

Drop-Down/ComboBox Sorting in SSRS

2010-08-15T22:01:00.003+05:30

For Dynamic Sorting SSRS has Interactive Sorting which enables a sorting button on the Tablix Column and user can sort on desired Column but many time user wants to have Drop-Down or Combo Box listing columns and wish to chose the sorting column from there.. Something like

Okay , this is not built-in feature of SSRS but we can certainly work around and provide that feature... I will take a simple example to demonstrate that: I will create a Simple Student table and provide drop down sorting on the columns in the table.

So Lets get started

1. Create a Student table

2. Create a SSRS project and create a Shared DataSource linking our Database

3. Create a simple report having columns from Student table

4. Report would look like.

5a. Now lets start our work and create a parameter SortBy

5b. Specify Available values as Column name from the Student table and assign values to them

6. Go to Tablix Properties> Sorting

7.Add a sorting Option and in Column tab give expression as

=IIf(Parameters!SortBy.Value=1,Fields!StudentID.Value
,IIf(Parameters!SortBy.Value=2,Fields!Name.Value
,Fields!Marks.Value))

8. Run the report and chose the SortBy column from Drop-Down

a. By Name

b.By Marks

Cheers-Have fun!!

Rahul Sherawat

Create Custom Task in SSIS

2010-05-31T10:20:00.012+05:30

Microsoft fitted SSIS with rich set of Tasks and Transformation that an ETL developer would need but sometime you have a requirment which you feel better done in some othere way so SSIS allows you to create Custom task and include it in SSIS.

Whenever any tricky situation comes my boss would say "Why dont you do this with Custom Task or Create package programtically" and I used to think if there is something that cant be done using existing tasks and transformation then perphaps the work doesnt worth doing with SSIS or I dont worth working on SSIS.

Well than one night( wonder why developers always have night rather than day) I thought lets do something programatically.

SSIS allows to have 5 types of custom objects

Custom tasks.
Custom connection managers
Custom log providers
Custom enumerators
Custom data flow components

I will create a Custom Task in this post. Most common thing when I debug a SSIS pacakage is to know the value of a variable so I created a custom task to display value of a select variable in a message box ( can do it in script task but isnt it a pain to do such a simple thing there). So lets create a "Display Variable" task.

It is best practice to create two assemblies: one for UI and anothter for actual runtime processing code.

First lets start with UI of DisplayVariable

A1. Create a Class Library project

A2. Add reference to

System.Drawing
System.Windows.Forms
Microsoft.DataTransformationServices.Controls -- This doesnt show up in .Net tab so you can browse to C:\Windows\Assembly to add
Microsoft.SqlServer.Dts.Design

A3. DisplayVariableForm:
Add a Windows Form ( name as DisplayVariableForm) then add one comboBox (CmBxVariableList ) and a Button( butOK)

A4. Code for DisplayVariableFormUI.cs

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using Microsoft.SqlServer.Dts.Runtime;

using Microsoft.SqlServer.Dts.Runtime.Design;

namespace DisplayVariableUI

{

public partial class DisplayVariableForm : Form

{

private TaskHost vTaskHost;

private Connections vConnection;

const string VARIABLE_NAME = "VariableName";

const string VARIABLE_VALUE = "VariableValue";

public DisplayVariableForm(TaskHost taskHost, Connections connections)

{

vTaskHost = taskHost;

vConnection = connections;

InitializeComponent();

}

//Fill Variable combo

private void DisplayVariableForm_Load(object sender, EventArgs e)

{

CmBxVariableList.BeginUpdate();

foreach (Variable var in vTaskHost.Variables)

{

CmBxVariableList.Items.Add(var.QualifiedName);

}

CmBxVariableList.EndUpdate();

}

private void butOK_Click(object sender, EventArgs e)

{

VariableName = CmBxVariableList.Text;

VariableValue = vTaskHost.Variables[VariableName].Value.ToString();

DialogResult = DialogResult.OK;

}

//get variable name

private string VariableName

{

get

{

if (vTaskHost.Properties[VARIABLE_NAME].GetValue(vTaskHost) != null)

{

return vTaskHost.Properties[VARIABLE_NAME].GetValue(vTaskHost).ToString();

}

return null;

}

set

{

vTaskHost.Properties[VARIABLE_NAME].SetValue(vTaskHost, value);

}

//Get variable value

private string VariableValue

{

get

{

if (vTaskHost.Properties[VARIABLE_VALUE].GetValue(vTaskHost) != null)

{

return vTaskHost.Properties[VARIABLE_VALUE].GetValue(vTaskHost).ToString();

}

return null;

}

set

{

vTaskHost.Properties[VARIABLE_VALUE].SetValue(vTaskHost, value);

}

A5. DisplayVariableUI.cs:

To initialize and display the user interface associated with the task we will create a class DisplayVariableUI.cs which will be inherited from interface IDtsTaskUI. When the user interface for a task is invoked, the designer calls the Initialize method, implemented by the task user interface and then provides the TaskHost and Connections collections of the task and package, respectively, as parameters. These collections are stored locally, and used subsequently in the GetView method.

The designer calls the GetView method to request the window that is displayed in SSIS Designer. The task creates an instance of the window that contains the user interface for the task, and returns the user interface to the designer for display. Typically, the TaskHost and Connections objects are provided to the window through an overloaded constructor so they can be used to configure the task.

The SSIS Designer calls the GetView method of the task UI to display the user interface for the task. The task user interface returns the Windows form from this method, and SSIS Designer shows this form as a modal dialog box.

When the form is closed, SSIS Designer examines the value of the DialogResult property of the form to determine whether the task has been modified and if these modifications should be saved. If the value of the DialogResult property is OK, the SSIS Designer calls the persistence methods of the task to save the changes; otherwise, the changes are discarded.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using Microsoft.SqlServer.Dts.Runtime;

using Microsoft.SqlServer.Dts.Runtime.Design;

namespace DisplayVariableUI

{

public class DisplayVariableUI : IDtsTaskUI

{

private TaskHost taskHost;

private Connections connectionService;

public void Initialize(TaskHost taskHost, IServiceProvider serviceProvider)

{

this.taskHost = taskHost;

IDtsConnectionService cs = serviceProvider.GetService (typeof(IDtsConnectionService)) as IDtsConnectionService;

this.connectionService = cs.GetConnections();

}

public ContainerControl GetView()

{

return new DisplayVariableForm(this.taskHost, this.connectionService);

}

public void Delete(IWin32Window parentWindow)

{

}

public void New(IWin32Window parentWindow)

{

}

A6. Now UI code is complete and we have to sign assembly with strong name so go to Properties >> Signing and sign the assembly using a StrongName.

A7. Build DisplayVariableUI

A8. We will need public key token of DisplayVariableUI assembly in Task project. To create Public key token Open Visual Studio 2008 command prompt , browse to the project folder where strongkey should be created and type

a. sn -p DisplayVariableUIPrivateKey.snk DisplayVariableUIPublicKey.snk

To see the Public key token

b. sn -t DisplayVariableUIPublicKey.snk

Copy the Public Key token

Now we move to Task code with actual runtime code is written:

B1. Create a Class Library project ( DisplayVariable.cs)

B2. Add reference to

System.Windows.Forms
Microsoft.DataTransformationServices.Controls

-- This doesnt show up in .Net tab so you can browse to C:\Windows\Assembly to add

B3. Code for DisplayVariable.cs

a. Apply the DtsTaskAttribute attribute to the class, this attribute provides design-time information such as the name, description, and task type of the task. we will need to specify PublicKeyToken of UI here.

b. DisplayVariable class should inherit from Task Class and then override Base Task class DTSExecResult Execute method to show a message box cantaining Variable name and Variable value.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.SqlServer.Dts.Runtime;

namespace DisplayVariable

{

[DtsTask

(

DisplayName = "DisplayVariable",

Description = "Task can display any variable from Variable List",

RequiredProductLevel = DTSProductLevel.None,

TaskContact = "Rahul Sherawat-logtorahul@gmail.com",

UITypeName = "DisplayVariableUI.DisplayVariableUI, DisplayVariableUI, Version=1.0.0.0, Culture=Neutral, PublicKeyToken=6d67e5bed27edc4b"

)

]

public class DisplayVariable: Task

{

//will get Variable Name

private string UIvariableName;

public string VariableName

{

get { return UIvariableName; }

set { UIvariableName = value; }

}

//will get variable value

private string UIVariableValue;

public string VariableValue

{

get { return UIVariableValue; }

set { UIVariableValue = value; }

}

//override execute

public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)

{

DTSExecResult execResult = DTSExecResult.Success;

if (string.IsNullOrEmpty(VariableName))

{

System.Windows.Forms.MessageBox.Show("No Variable Selected");

}

else

{

string VarVal;

VarVal = VariableName + "\r\n"+ "Value: " + VariableValue;

System.Windows.Forms.MessageBox.Show(VarVal);

}

return execResult;

}

c. DtsTaskAttribute attribute PublicKeyToken is the public key token of UI assembly that we will create in next step and this has to updated in Task assembly and rebuild.

B4. DisplayVariable Assembly has to be signed so go to Properties >> Signing and sign the assembly using a StrongName.

B5. Build the project. This will create DisplayVariable.dll in debug folder of project.

C1. Go to DisplayVariableUI project in add reference of DisplayVariable.dll and build the UI project again.

C2. Now both the dll are created and we have to add them to GAC

In Visual Studio Command prompt type

a. For DisplayVariable.dll

gacutil -i DisplayVariable.dll
b. For DisplayVariableUI.dll
gacutil -i DisplayVariableUI.dll

C3. Copy DisplayVariableUI.dll and DisplayVariable.dll to \\Program Files\Microsoft SQL Server\100\DTS\Tasks so that it can be picked up in SSIS tool box

C4. Create a new SSIS project. Go to Tools>>Choose ToolBox items..>>SSIS ControlFlow items select DisplayVariable

C5. You should be able to see DisplayVariable Task in ToolBox now.

C6. Drag it to control flow and use it

You can download entire solution from here

Same Connection Manager on Multiple Server

2010-05-19T12:39:00.001+05:30

There was a question on MSDN forum to Run same query on Mulitple Servers.

We can do this by using one Connection Manager and passing the Connection String through Expressions
Below are the steps I followed to achieve this

1. Store Connection String for various connection in a table

2. Declare two Varibles like

3.Use Expression of Source Connection Manager to provide Connection String

4. Use Execute Sql Task to get connections in a Varible of type Objects from the table

5. Use For Each Loop to Iterate on that variable

6. Use Script component to cast the value of CurrConnection to String
        public void Main()
        {
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
            String con;
            con = (string) Dts.Variables["CurrConnection"].Value;
            Dts.Variables["CurrConnection"].Value = con;

        }

7. Use any task inside the for loop with Connection Manager and it will use different server for each loop.

Jobs Running on Server

2010-04-22T20:18:00.006+05:30

To find out jobs running on a server through T-Sql

DECLARE @job_owner VARCHAR(100);

SELECT @job_owner = SUSER_SNAME()

IF EXISTS
(
   SELECT 1   FROM tempdb.dbo.sysobjects
   WHERE ID = OBJECT_ID(N'tempdb..#JobStats')
)
BEGIN
   DROP TABLE #JobStats
END

CREATE TABLE
   #JobStats
   (
       job_id UNIQUEIDENTIFIER
   ,   last_run_date INT
   ,   last_run_time INT
   ,   next_run_date INT
   ,   next_run_time INT
   ,   next_run_schedule_id INT
   ,   requested_to_run INT
   ,   request_source INT
   ,   request_source_id VARCHAR(100)
   ,   running INT
   ,   current_step INT
   ,   current_retry_attempt INT
   ,   job_state INT
   )

INSERT INTO #JobStats
            EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner

SELECT
   sysjobs.name
,   JobStats.current_step
,   JobStats.running
,   JobStats.*
FROM
   #JobStats JobStats
    JOIN MSDB..sysjobs sysjobs
    ON JobStats.job_id = sysjobs.job_id

SELECT

SYSJOBS.Name

, SYSJOBS.Job_Id

, SYSPROCESSES.HostName

, SYSPROCESSES.LogiName

, *

FROM

MSDB.dbo.SYSJOBS

JOIN

MASTER.dbo.SYSPROCESSES

SUBSTRING(SYSPROCESSES.PROGRAM_NAME,30,34)

=MASTER.dbo.fn_varbintohexstr ( SYSJOBS.job_id)

AND

LEFT(PROGRAM_NAME,28) ='SQLAgent - TSQL JobStep (Job'

Dynamic Package Configuration File in SSIS

2010-04-16T09:49:00.006+05:30

This is in reference to an interesting problem posted on MSDB forum. It was so interesting that i though it worth a post. So here we go:

Problem:

Child Package should use a Configuration file whose path is determined only at the run time of Parent Package. Package Configuration doesnt have any property which may allow it to dynamically determine path at run time and use it.

Solution:

There can be many ways of solving this as suggested by other SSIS pandits but what struck to my mind first was, we can do it by using very basic tasks in SSIS. The approach is to:

In Child Package:

1. Create the Child Package as usual without bothering of Dynamic Config file and store config file to some location say E:\Configs

In Parent Package

1. Get actual path of Config(which is to be used)

2. Use File System Task and copy this actual (Dynamic as I prefer to say) to the location of Child Package Config (E:\Configs)

To Demonstrate this.:

1. I Created a Child package which would access a database table using it Development config file.

2. Now I created a Parent package which will copy the dynamic config to the location at call the child package.

Now i will Run this Parent package- which will make the child package to use the dynamic Config file

Cheers!!

Delete Duplicates from a Table

2010-04-10T14:09:00.009+05:30

WITH DupCTE(Col1,Col2, Ranking)

(

SELECT

Col1

, Col2

, DENSE_RANK() OVER

(PARTITION BY ID ORDER BY NEWID() ASC) AS Ranking

FROM TableName

)

DELETE FROM DupCTE

WHERE Ranking > 1;

Few days back I had a table which had duplicates on the Id columns also, so i had to flip a bit to write a shortest code i can write to delete or remove duplicates from the table.

Below I will demonstrate it by create a temp table, inserting it with some duplicate data and then remove the dups using CTE( Common Table Expressions)

1. Create Table with duplicate data:

--Create Table

CREATE TABLE #Dups

(

Id INT

, Name VARCHAR(50)

);

--Insert data with Duplicates

INSERT INTO #Dups VALUES ( 1, 'Rahul');

INSERT INTO #Dups VALUES ( 2, 'Divya');

INSERT INTO #Dups VALUES ( 3, 'Jason');

2. CTE to remove Dups:

WITH DupCTE(Id, Name, Ranking)

(

SELECT

, Name

, DENSE_RANK() OVER(PARTITION BY ID ORDER BY NEWID() ASC) AS Ranking

FROM #Dups

)

DELETE FROM DupCTE

WHERE Ranking > 1;

You can download the complete sample code from here.

Cheers!!

Error: it(job) does not have any job server(s) defined

2010-01-18T21:34:00.004+05:30

Today I was creating a job on SQL SERVER 2000 through SSMS 2008, and when I was trying to run the job it gave me error as

Server: Msg 14256, Level 16, State 1, Procedure sp_start_job, Line NN

Cannot start job 'XXXX' (ID NNNN) because it does not have any job server(s) defined.

Solution:

Well the solution is pretty simple- In Job properties got to TARGET and check either 'Target local server' or 'Target multiple servers'.

Top New Features in SSIS 2008

2009-12-27T18:51:00.001+05:30

1) Create Script tasks by using Microsoft Visual C# and Microsoft Visual Basic .NET.

2) Use ADO.NET for tasks as well as for source and destination components.

3) Improve scalability with thread pooling and enhanced lookup transformations.

4) Perform more functional and scalable data transfers with the improved SQL Server Import and Export Wizard.

5) New SSIS connectors for SAP BW, Oracle and Teradata.

Dynamic query as sql command for ADO NET / OleDb Source

2009-11-24T22:59:00.006+05:30

A question on MSDN forum drew my attention towards a very common requirement which is made very tricky to do in SSIS.

A user may need to pass a dynamic query to OleDb source/ADO source something like

SELECT * FROM TblNm WHERE Col1 = @Var

In OleDb source user can create query like this in a variable and pass it to the OleDb Source but ADO NET source does not have Variable as Data Access Mode, so what do we do for ADO Net Source. Okay dont scratch around there is way out which works for both OleDb and ADO Net Source.

1. Create a variable

2. Create ADO NET Source or OleDb Source and select SQL Command as DATA ACCESS MODE and give a simple query like SELECT * FROM TblNm

3. In Control Flow Select DataFlow Task which contains this Source and from it Property Tab select Expressions and open Property Expressions Editor

4. Choose [ADO NET Source].[SQL Commnad] and make a expression like

5. Now run the package and Sql will be dynamically created and will get the value of variable.

Fail to save package file : class not registered

2009-11-01T12:24:00.004+05:30

Problem:

Sometime a user may encounter an error like below while creating a SSIS package from BIDS.

Error creating package

------------------------------
ADDITIONAL INFORMATION:

Failed to save package file "C:\Documents and Settings\UserName\Local Settings\Temp\tmp87.tmp" with error 0x80040154 "Class not registered".
-------------------------------

Solution:

To solve this try to register below DLLs by typing below commands in CMD promopt:-

regsvr32 msxml3.dll
regsvr32 msxml4.dll
regsvr32 msxml6.dll

Copy/Rename a file using File System Task in SSIS

2009-04-21T10:32:00.010+05:30

This is a reference answer to a post on MSDN forum but is very useful

Question: While copying and renaming a file to a dynamic location through File System Task using variable throws an error

Error: Failed to lock variable "c:\test\test_200904202009.txt" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

How to copy and rename a file to a dynamic location using SSIS?

Solution:

I will move and rename a file "test.txt" from c:\ to c:\backup with new name appended with date.

1) Create three variables

Src_File = c:\test.txt

Dest_File = test

Dest_Dir = c:\backup

2) In connection Manager create a File Connection with name as DestinationConn

3) In Expression property of DestinationConn use ConnectionString and provide following expression

@[Dest_Dir] + "\\" + @[Dest_File] + "_" + (DT_WSTR,4)DatePart("yyyy", GetDate()) + RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("dd", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("hh", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("mi", GetDate()), 2) + ".txt"

this will set the destination path and new name for the file

4) Create a File System task and configure like :

5) Execute the package

This Package can be downloaded from here.

To run the package create a file named "test.txt" and a folder "backup"at C:\

Thanks!!

Please leave a comment.

Trail Period is Over - the funny side

2009-04-01T16:57:00.003+05:30

Column length in Excel source

2009-03-24T16:05:00.005+05:30

Here is one very common warning with using Excel in SSIS

Whenever we use Excel file in SSIS, it takes default length of each column as 255. This results in having a warning if we try to map this column with column having length less than 255. We can avoid this warning my setting column length of the Excel source.

Right click to Open Excel source in Advance editor and set lenght of the column as depicted in the figure.

Getting Daily Feed in SSIS

2009-03-02T19:02:00.008+05:30

Many time there comes a situation when you need to use a daily feed kind of file which is copied in a fixed directory and we need to use the file for data import.

Below I will show an approach to automate the process so that SSIS package would check if the today's file is present or not and if present then do the data import.

For Example
File name format : FileNameDate (FileName03022009.xls)
Fixed Directory : D:\Abc

1.Create a Variable "FileName" This will hold the path and name of the daily feed file.

2. Use script task. specify readwrite variable as "FileName"

3. Coding
a) Configure FileName to have path and name of the daily feed file.
Dts.Variables("FileName").Value = "D:\Abc\FileName" _ & Date.Today.Month.ToString.PadLeft(2, "0"c) & Date.Today.Day.ToString.PadLeft(2, "0"c) _ & Date.Today.Year & ".xls"

b) check if file is present or not. (Fail the package if file is not present)
'check if file is present
Dim Fi As New FileInfo(CStr(Dts.Variables("FileName").Value)) If (CBool(Fi.Exists().ToString())) Then Dts.TaskResult = Dts.Results.Success Else MsgBox(CStr(Dts.Variables("FileName").Value) & " is not present") Dts.TaskResult = Dts.Results.Failure End If

4) Create a Excel Connection Manager and in Expression property specify

5) Use the Excel Connection Manager in Excel source of DATA FLOW
6) Execute

Passing variable to Child package from Parent package in SSIS

2009-02-25T13:32:00.015+05:30

(APPLICABLE ONLY IN SSIS 2005, Use Parent Package Configuration for SSIS 2008)

Very frequent Question which looks so complicated to programmers.

Actually passing a variable value to a child package is very trivial task. We can pass on the value by configuring parent variable in package configuration but there is an easy way of achieve this and the fact lies beneath the fundamental principle of Variable Scope.

If you call a Child package then it is like a container itself and all the variables defined in above hierarchy will be accessible in the Child package.

Let me show this with an example in which I will declare a variable "ParentVar" in my parent package and call a Child package which will access "ParentVar" and display in a msgbox.

1) Parent: Create Parent Package and declare a variable "ParentVar"

2) Child: Create a Child package and use a script task and define readonly variable as ParentVar

3) Child: Now in Script you can use ParentVar like any other variable.eg I am using to display it in a msgbox. I would suggest to create another child package variable and assign Parent package variable value to it and use it in the child package variable through out the package.

4) Child: Whole Child package will look like

5) Parent: Now in parent package call the child package through Execute Package task.
The Parent package will look like

6) Result: Execute Parent package.it will in turn call child package and it will display msgbox

Here was a simple method to use parent package variable in Child package.

SSIS Interview Questions

2009-02-23T11:56:00.021+05:30

Common search for new SSIS programmer looking for change is what questions to expect on SSIS. Based on the interviews I take on SSIS, I will list down my favorites and expected questions on SSIS.

Q1 Explain architecture of SSIS?
SSIS architecture consists of four key parts:
a) Integration Services service: monitors running Integration Services packages and manages the storage of packages.

b) Integration Services object model: includes managed API for accessing Integration Services tools, command-line utilities, and custom applications.

c) Integration Services runtime and run-time executables: it saves the layout of packages, runs packages, and provides support for logging, breakpoints, configuration, connections, and transactions. The Integration Services run-time executables are the package, containers, tasks, and event handlers that Integration Services includes, and custom tasks.

d) Data flow engine: provides the in-memory buffers that move data from source to destination.

Q2 How would you do Logging in SSIS?
Logging Configuration provides an inbuilt feature which can log the detail of various events like onError, onWarning etc to the various options say a flat file, SqlServer table, XML or SQL Profiler.

Q3 How would you do Error Handling?
A SSIS package could mainly have two types of errors
a) Procedure Error: Can be handled in Control flow through the precedence control and redirecting the execution flow.
b) Data Error: is handled in DATA FLOW TASK buy redirecting the data flow using Error Output of a component.

Q4 How to pass property value at Run time? How do you implement Package Configuration?
A property value like connection string for a Connection Manager can be passed to the pkg using package configurations.Package Configuration provides different options like XML File, Environment Variables, SQL Server Table, Registry Value or Parent package variable.

Q5 How would you deploy a SSIS Package on production?
A) Through Manifest
1. Create deployment utility by setting its propery as true .
2. It will be created in the bin folder of the solution as soon as package is build.
3. Copy all the files in the utility and use manifest file to deply it on the Prod.
B) Using DtsExec.exe utility
C)Import Package directly in MSDB from SSMS by logging in Integration Services.

Q6 Difference between DTS and SSIS?
Every thing except both are product of Microsoft :-).

Q7 What are new features in SSIS 2008?
explained in other post
http://sqlserversolutions.blogspot.com/2009/01/new-improvementfeatures-in-ssis-2008.html

Q8 How would you pass a variable value to Child Package?
too big to fit here so had a write other post
http://sqlserversolutions.blogspot.com/2009/02/passing-variable-to-child-package-from.html

Q9 What is Execution Tree?
Execution trees demonstrate how package uses buffers and threads. At run time, the data flow engine breaks down Data Flow task operations into execution trees. These execution trees specify how buffers and threads are allocated in the package. Each tree creates a new buffer and may execute on a different thread. When a new buffer is created such as when a partially blocking or blocking transformation is added to the pipeline, additional memory is required to handle the data transformation and each new tree may also give you an additional worker thread.

Q10 What are the points to keep in mind for performance improvement of the package?
http://technet.microsoft.com/en-us/library/cc966529.aspx

Q11 You may get a question stating a scenario and then asking you how would you create a package for that e.g. How would you configure a data flow task so that it can transfer data to different table based on the city name in a source table column?

Q13 Difference between Unionall and Merge Join?
a) Merge transformation can accept only two inputs whereas Union all can take more than two inputs

b) Data has to be sorted before Merge Transformation whereas Union all doesn't have any condition like that.

Q14 May get question regarding what X transformation do?Lookup, fuzzy lookup, fuzzy grouping transformation are my favorites.
For you.

Q15 How would you restart package from previous failure point?What are Checkpoints and how can we implement in SSIS?
When a package is configured to use checkpoints, information about package execution is written to a checkpoint file. When the failed package is rerun, the checkpoint file is used to restart the package from the point of failure. If the package runs successfully, the checkpoint file is deleted, and then re-created the next time that the package is run.

Q16 Where are SSIS package stored in the SQL Server?
MSDB.sysdtspackages90 stores the actual content and ssydtscategories, sysdtslog90, sysdtspackagefolders90, sysdtspackagelog, sysdtssteplog, and sysdtstasklog do the supporting roles.

Q17 How would you schedule a SSIS packages?
Using SQL Server Agent. Read about Scheduling a job on Sql server Agent

Q18 Difference between asynchronous and synchronous transformations?
Asynchronous transformation have different Input and Output buffers and it is up to the component designer in an Async component to provide a column structure to the output buffer and hook up the data from the input.

Q19 How to achieve parallelism in SSIS?

Parallelism is achieved using MaxConcurrentExecutable property of the package. Its default is -1 and is calculated as number of processors + 2.

-More questions added-Sept 2011
Q20 How do you do incremental load?

Fastest way to do incremental load is by using Timestamp column in source table and then storing last ETL timestamp, In ETL process pick all the rows having Timestamp greater than the stored Timestamp so as to pick only new and updated records

Q21 How to handle Late Arriving Dimension or Early Arriving Facts.

Late arriving dimensions sometime get unavoidable 'coz delay or error in Dimension ETL or may be due to logic of ETL. To handle Late Arriving facts, we can create dummy Dimension with natural/business key and keep rest of the attributes as null or default. And as soon as Actual dimension arrives, the dummy dimension is updated with Type 1 change. These are also known as Inferred Dimensions.

Might want to read SSRS Interview Questions

Selecting Excel source range in SSIS

2009-02-19T11:25:00.022+05:30

Many times I have been asked by SSIS programmers that they face difficulties when they need to select some specified columns and rows or they need to skip first 2 /3 rows in the excel sheet.

Below I will demonstrate how we can select a specified range in an Excel Sheet.

A) Let me take below sheet as an example and select data after first 2 rows

B) Now in Data Flow task, select an Excel source
Configure Data Access Mode as SQL Command
and in SQL Command Text give query as

SELECT * FROM [Sheet1$ range]
SELECT * FROM [Sheet1$A2:B10]

C) Preview

This way user can select any range or any specific cell value in an Excel File

Matching in Lookup Component in SSIS

2009-02-03T14:34:00.012+05:30

Many SSIS programmers get confused with the comparison results of Lookup transformation and feels baffled ;-| . Lookup transformation in SSIS is used to lookup for a reference in the lookup table where it performs a equi-join and returns the referenced column.

Let me explain the comparison/matching behavior of lookup transformation: It matches data differently when in cached (default) and non-cached (Enable memory Restriction) mode. In cached mode comparison will be case sensitive where as in non-cached mode comparison will be based on collation level of column being matched.

Mode :: Behavior
Cached (default) :: Case sensitive
Non-cached (Enable memory restrictions) :: Collation level of column

Why?
It basically depends where the comparision is done. In cached mode SSIS reterives data from the source and comparision is done at the client on the byte level which is CASE SENSITIVE. Whereas in Non-Cache mode ( Enable memory Restriction) comparision is done at database and is based on the collation level of the column being matched.

That's the behavior of Lookup Transformation in SSIS.

New improvements / features in SSIS 2008

2009-01-30T14:27:00.006+05:30

With the release of SQL SERVER 2008 comes improved SSIS 2008. I will try to list down the improved and new features in SSIS 2008

1) Improved Parallelism of Execution Trees:

The biggest performance improvement in the SSIS 2008 is incorporation of parallelism in the processing of execution tree. In SSIS 2005, each execution tree used a single thread whereas in SSIS 2008 , the Data flow engine is redesigned to utilize multiple threads and take advantage of dynamic scheduling to execute multiple components in parallel, including components within the same execution tree

2) Any .NET language for Scripting:

SSIS 2008 is incorporated with new Visual Studio Tool for Application(VSTA) scripting engine. Advantage of VSTA is it enables user to use any .NET language for scripting.

3) New ADO.NET Source and Destination Component:

SSIS 2008 gets a new Source and Destination Component for ADO.NET Record sets.

4) Improved Lookup Transformation:

In SSIS 2008, the Lookuo Transformation has faster cache loading and lookup operations. It has new caching options, including the ability for the reference dataset to use a cache file(.caw) accessed by the Cache Connectin Manager. In addition same cache can be shared between multiple Lookup Transformations.

5) New Data Profiling Task:

SSIS 2008 has a new debugging aid Data Profiling Task that can help user analyze the data flows occurring in the package.In many cases, execution errors are caused by unexpected variations in the data that is being transferred. The Data Profiling Task can help users to discover the cource of these errors by giving better visibility into the data flow.

6) New Connections Project Wizard:

One of the main usability enhancement to SSIS 2008 is the new Connections Project Wizard. The Connections Project Wizard guides user through the steps required to create source and destinations.

Difference between Merge and Union all transformation

2009-01-29T13:07:00.007+05:30

I have been asked by many new SSIS developer about difference between Merge and Union all transformation in SSIS.

Well both of them essentially takes outputs from more than one sources and combines them into a single result set but there are couple of differences between two:

a) Merge transformation can accept only two inputs whereas Union all can take more than two inputs

b) Data has to be sorted before Merge Transformation whereas Union all doesn't have any condition like that.

Using varibles in Data flow Script task in SSIS

2009-01-27T13:28:00.007+05:30

How to use variables in Script component of Data flow task in SSIS

1) Declare a variable as MyVar in varible window.

2) Declare variable type in dataflow> Script Component >Script

3) Use variable in the script
Now here is the catch we use variable in data flow task by
Me.Variable.MyVar ="MyVariable"

where as in control flow we use variable using
DTS.Variable("MyVar").value = "MyVariable"

Creating directory using SSIS

2009-01-05T17:09:00.010+05:30

Creating a directory/folder at a dynamic path using SSIS sometimes becomes complex job as I feel and can understand by reading questions posted on various SSIS forums, File System task is not good in handling dynamic path

Here as an example and answer to a question posted on MSDN forum I would demonstrate how we can create a directory of today's date.

1) Create a variable varDirPath of string datatype

2.a) Create a Script task and give varDirPath as ReadWriteVariable

2.b) Use below code to set varDirPath value as the path of directory to be created
Public Sub Main()
'set dir path
Dts.Variables("varDirPath").Value = "D:\" + Date.Today.Year.ToString _
+ Date.Today.Month.ToString + Date.Today.Day.ToString

Dts.TaskResult = Dts.Results.Success
End Sub

this will create path like D:\20090115

3) Use File System Task and configure like below pic:

4) Execute the package

Cracking Query Execution - II: Why Actual and Estimated Query Execution Plan may differ

2009-01-02T13:26:00.005+05:30

Generally there is no difference between Actual Execution plan and Estimated plan and Query is executed the way Estimated Plan outlines.However, circumstances can arise that can cause Execution Plan to change:

Obsolete Statistics:
The main cause of difference between the plans is difference between the statistics and the actual data.This generally occurs over time as data is added and deleted. This causes the key values that define the index to change,or their distribution to change.

Estimated Plan becomes Invalid:
In some instances, SQL Server Engine doesn't even generate Estimated execution plan, like
CREATE TABLE tblAbc
(
Col1 INT,
Col2 INT);

INSERT INTO tblAnd VALUES ( 1,2);

This will through error as
Msg 208, Level 16, State 1, Line 6
Invalid object name 'tblAnd'.

As table tblAbc doesn't exists in database yet, so it cant create a plan for it.

When Parallelism is required:
When a plan meets a threshold for parallelism two plans are created. Which plan is actually executed is up to the query engine.

Cracking Query Execution - I: Query Cycle

2008-12-31T11:34:00.018+05:30

The very basic idea of designing a database is to store information and Query effectively, I will discuss what a Query is and how it gets executed:

QUERY: A precise request for information retrieval with database and information systems

What happens when we submit a query:
As we submit a query, number of processes in the database engine go to work on it so as to manage the system in such way that retrieve or store a data in a timely a manner as possible, whilst maintaining the integrity of the data.
We can roughly break down these processes into two stages
A) Processes that occurs in the Relational engine
In relational engine the query is parsed and then processed by Query Optimizer, which generates an execution plan. This plan is sent (in binary format) to Storage engine.
B) Processes that occurs in the Storage engine
In storage engine process such as locking, index maintenance and transaction occur.

Query Parsing:
As a SQL Server Engine receives a query,it passes it through a process that checks that the T-SQL is written correctly and is well formed.The output of the parser process is a PARSE TREE.

If the T-SQL is a DML, the Parse tree is passed to a precess called as ALGEBRIZER, it resolves all the names of various objects like table and columns referred in the query, it also performs type checking and does aggreate binding (determines location of aggregates with in the query). Output of Algebrizer is called as QUERY PROCESSOR TREE or ALEGEBRISER TREE and is passed to Query optimiser
Non DML are not passed to Algebrizer of query optimiser as non-DML cant be optimized (SQL Server doesn't provide many ways of creating a table).

Query Optimizer:
The optimizer figures out how best to implement the request represented by the T-SQL query you submitted. It decides if the data can be accessed through indexes, what types of joins to use and much more. The decisions made by the optimizer are based on what it calculates to be the cost of a given execution plan, in terms of the required CPU processing and I/O, and how fast it will execute. Hece, this is known as cost-based plan.

The optimizer will generate and evaluate many plans (unless there is already a cached plan) and, generally speaking, will choose the lowest-cost plan i.e. the plan it thinks will execute the query as fast as possible and use the least amount of resources, CPU and I/O. The calulation of the execution speed is the most important calculation. Sometimes, the optimizer will select a less efficient plan if it thinks ith will take more time to evaluate many plans than to run a less efficient plan.

Once the optimizer arrives at an execution plan, the estimated execution plan is created and stored in a memory space known as the PLAN CACHE.
Sometimes actual executed plan differs from the estimated execution plan , WHY

Query Execution:
Once the execution plan is generated, the action switches to the storage engine, where the query is actually executed, according to the plan.

Numeric Checking in SSIS

2008-12-24T16:09:00.010+05:30

A post in MSDN forum drew my attention toward missing functionality of IsNumeric() in SSIS expressions.

To solve this we can use script task and check of IsNumric()
forexample I will use following data and table

--create a test table
create table TEST
(
[id] varchar(20),
[name] varchar(20)
)
--Fill some data

Insert into TEST values ( 1,'Mike')
Insert into TEST values ( 2 ,'Rahul')
--error data with non numeric id
Insert into TEST values ( '3a' ,'Worngdata')

Now lets design our package
1) Get data extract in Oledb DataSoruce
2) Create a Script task and specify input column as ID

2.b) Add a column as IsNumeric of Boolean datatype

2.c) Write below code as Script

If IsNumeric(Row.id) Then
Row.IsNumeric = True
Else
Row.IsNumeric = False
End If

3) Use Conditional Split to separate out bad records and valid records

4) Now when we execute the package it will separate out records with non-numeric data

Full package

Identify Orphan Users on an instance

2008-12-23T17:02:00.003+05:30

Many a time when we move our database to new instance, rebuilding server or setting up a backup we need to synchronize user logins with the server

We can use below script to find out the Orphan Users on the Server

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[usp_ShowOrphanUsers]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_ShowOrphanUsers]
GO

CREATE PROC dbo.usp_ShowOrphanUsers
AS
BEGIN
CREATE TABLE #Results
([Database Name] sysname ,
[Orphaned User] sysname ,
[Type User] sysname )

SET NOCOUNT ON

DECLARE @DBName sysname, @Qry NVARCHAR(4000)

SET @Qry = ''
SET @DBName = ''

WHILE @DBName IS NOT NULL
BEGIN
SET @DBName =
(
SELECT MIN(name)
FROM master..sysdatabases
WHERE name NOT IN
('master', 'model', 'tempdb', 'msdb', 'distribution', 'pubs', 'northwind', 'dba_database')
AND DATABASEPROPERTY(name, 'IsOffline') = 0
AND DATABASEPROPERTY(name, 'IsSuspect') = 0
AND name > @DBName
)

IF @DBName IS NULL BREAK

SET @Qry = ' SELECT ''' + @DBName + ''' AS [Database Name],
CAST(name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User],
[Type User] =
CASE isntuser
WHEN ''0'' THEN ''SQL User''
WHEN ''1'' THEN ''NT User''
ELSE ''Not Available''
END
FROM ' + QUOTENAME(@DBName) + '..sysusers su
WHERE su.islogin = 1
AND su.name NOT IN (''INFORMATION_SCHEMA'', ''sys'', ''guest'', ''dbo'', ''system_function_schema'')
AND NOT EXISTS (SELECT 1 FROM master..syslogins sl WHERE su.sid = sl.sid)'
INSERT INTO #Results
EXEC master..sp_executesql @Qry
END
SELECT *
FROM #Results
ORDER BY [Database Name], [Orphaned User]
IF @@ROWCOUNT = 0
PRINT 'No orphaned users exist in this server.'
END
GO

--Execute the proc
EXEC [dbo].[usp_ShowOrphanUsers]

"This script is taken from SQL Server Central"

New Datatypes in SQL Server 2008

2008-10-31T14:18:00.002+05:30

SQL Server 2008 has introduced 4 new datatype:

Date and Time: Four new date and time data types have been added, making working with time much easier than it ever has in the past. They include: DATE, TIME, DATETIME2, and DATETIMEOFFSET.
Spatial: Two new spatial data types have been added--GEOMETRY and GEOGRAPHY--which you can use to natively store and manipulate location-based information, such as Global Positioning System (GPS) data.
HIERARCHYID: The HIERARCHYID data type is used to enable database applications to model hierarchical tree structures, such as the organization chart of a business.
FILESTREAM: FILESTREAM is not a data type as such, but is a variation of the VARBINARY(MAX) data type that allows unstructured data to be stored in the file system instead of inside the SQL Server database.

Date and Time:

In SQL Server 2005 and earlier, SQL Server only offered two date and time data types: DATETIME and SMALLDATETIME. While they were useful in many cases, they had a lot of limitations, including:

Both the date value and the time value are part of both of these data types, and you can’t choose to store one or the other. This often causes a lot of wasted storage (because you store data you don’t need or want); adds unwanted complexity to many queries because the data types often had to be converted to a different form to be useful; and often reduces performance because WHERE clauses with these data and time data types often had to include functions to convert them to a more useful form, preventing these queries from using indexes.
They are not time-zone aware, which often requires extra coding for time-aware applications.
Precision is only .333 seconds, which is often not granular enough for some applications.
The range of supported dates is not adequate for some applications, and the range does not match the range of .NET CLR DATETIME data type, which requires additional conversion code.

To overcome these problems, SQL Server 2008 introduces four new date and time data types, which include:

DATE: As you can imagine, the DATE data type only stores a date in the format of YYYY-MM-DD. It has a range of 0001-01-01 through 9999-12-32, which should be adequate for most business and scientific applications. The accuracy is 1 day, and it only takes 3 bytes to store the date.
TIME: TIME is stored in the format: hh:mm:ss.nnnnnnn, with a range of 00:00:00.0000000 through 23:59:59:9999999 and is accurate to 100 nanoseconds. Storage depends on the precision and scale selected, and runs from 3 to 5 bytes.
DATETIME2: DATETIME2 is very similar to the older DATETIME data type, but has a greater range and precision. The format is YYYY-MM-DD hh:mm:ss:nnnnnnnm with a range of 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999, and an accuracy of 100 nanoseconds. Storage depends on the precision and scale selected, and runs from 6 to 8 bytes.
DATETIMEOFFSET: DATETIMEOFFSET is similar to DATETIME2, but includes additional information to track the time zone. The format is YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm with a range of 0001-01-01 00:00:00.0000000 through 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC), and an accuracy of 100 nanoseconds. Storage depends on the precision and scale selected, and runs from 8 to 10 bytes.

Prompting for parameter in SSIS

2008-10-20T17:26:00.014+05:30

A question in MSDN forum drew my attention towards the missing functionality of

Prompting for parameters in SSIS

A question might struck in your mind that if we have it in SSRS than why not in SSIS. Well I am a great fan of Microsoft as they provide alternative for any thing under the sun (Sometimes SUN itself). In this post I would like to demonstrate how we can implement prompt for variable values in SSIS..

For the implemention, Let me consider a simple table having Name and Email Id.
Through SSIS package we will prompt for Name and accept Name from User.

CREATE TABLE [tblSSISPrompt]
(
[Name] varchar(15),
[Email] varchar(50)
)
GO
--Fill table with some data

INSERT INTO [tblSSISPrompt] VALUES ('Rahul', 'Rahul.Kumar@sqlsvrsol.com')
INSERT INTO [tblSSISPrompt] VALUES ('Prashant','Prashant.M@sqlsvrsol.com')
INSERT INTO [tblSSISPrompt] VALUES ('Mark','Mark.W@sqlsvrsol.com')
GO

SELECT * FROM [tblSSISPrompt]

Now we start with SSIS
A) Declare two variable
a) SqlStmt to store SQL query
b) Email to get Email from the resultset from Sql query

B) Use Script Task to prompt and prepare SQL query and store in SqlStmt variable
a) Specify SqlStmt as ReadWriteVariables

b) Code for prompt and prepare SQL query

Public Sub Main()
Dim Name As String

'Prompt
Name = InputBox("Enter Name", "Name Dialog")

'Prepare SQL query
Dts.Variables("SqlStmt").Value = _
"SELECT Email FROM tblSSISPrompt WHERE Name = '" + Name + "'"

'msgbox
MsgBox(Dts.Variables("SqlStmt").Value, , "Query")

End Sub

C) Use Execute SQL Task to execute the query

D) Execute the Package
a) As you execute the package, it will prompt for Name

b) Script will display query in Message box

c) Finally Execute SQL Task will execute the query

Congrats!!! thats Prompting for parameters in SSIS

If you want to do the same thing in C#, read first comment.

Zip and unzip files in a folder

2008-10-15T16:56:00.010+05:30

Many a time we need to zip or unzip more then one file present in a folder. SSIS provides a easy way to handle this.

For my example I am using 7-zip though we have many other compress software like (pkzip or winzip)

Steps to zip/unzip files
A) Declare a variable FileName as string datatype

B) Use ForEach loop to loop through the files in the folder and map file names to FileName variable
a) Provide folder name inEnumerator configuration

b) map FileName Variable

C) Use Execute Process task to zip/unzip to individual files
a) Browse to Exe of 7z in Executable

b) Use expression to configure the argument

D) Execute the package

Use the files........

Returning numeric value in Execute SQL task

2008-10-15T13:33:00.010+05:30

Yesterday night a question on MSDN fourm drew my attention towards a wired but ture fact that SSIS does not accept numeric value as double in Execute SQL task.

So how do we use our numeric value in Execute SQL Task

SSIS considers the numeric values as string when they are returned from Execute SQL task, so to use that value we have to follow two steps

1) Map the value to a String or Object datatype variable
2) Convert this value to double and store in other variable

Let me take an example to show how it works

I will consider below table for the example and extract maximum value of Num column
CREATE TABLE tblAbc
(
[Id] INT IDENTITY (1,1),
[Num] NUMERIC(10,4)
)
GO

INSERT INTO tblAbc VALUES (123.23)
INSERT INTO tblAbc VALUES (555.23)
GO
--query we will use to extract max of Num
SELECT Max(Num)as Num FROM tblAbc

Now its implementation
A) Declare two variables 1) objNum as object and 2) dblNum as double datatype

B) Use Execute SQL task to extract numeric value and map it to objNum variable

map result to objNum variable

C) Use script task to convert objNum value into double and assign it to dblNum variable
a) specify
Readonlyvariable as ObjNum
Readwritevariable as dblNum

b) Write below code as script

Dts.Variables("dblNum").Value = CDbl(Dts.Variables("objNum").Value
MsgBox(Dts.Variables("dblNum").Value, , "dblNum")

D) Excute the package

Now we have dblNum having our numeric value and can use it for any calculation purpose

Difference between Stored Proc and User-defined Functions

2008-10-14T16:19:00.005+05:30

Today morning my close friend Prashanth (DotNet expert) asked difference between Stored Proc and Functions . So I am dedicating this post to him, also today is bithday of beautiful Buddu, his girlfriend.

Difference betweem Stored Proc and User-defined Functions

Stored Procedure have pre-compiled execuction plan where as functions do not.
Functions are used for computations where as procedures are mainly used for performing business logic.
Functions can only have 'in' parameter where as SP can have both 'in' and 'out' parameters.
Function does not allow DML (insert, update, delete) queries on an object where as SP allows.
Functions can be used inline where as SP can't be.
Stored Procedure can retun more than one value at a time while funtion returns only one value at a time.
Functions MUST return a value, procedures need not be.

Please leave your comments..

Functions

2008-10-14T11:06:00.010+05:30

With my personal experience of SQL Server, I believe Function (User defined functions) are under utilized then their potential so I thought of writing this post in complete honor of UDFs.

Microsoft SQL Server provides three programmable objects
a) User defined Functions b) Stored Procedure c) Triggers

" Functions are pre-prepared pieces of code that may accept parameters, and always return a value"

UDFs support the creation of rich programming logic, including looping, flow control, decision making and branching.
UDFs can be divided into two types based on the value they return
a) Scaler functions b)Table-valued functions

A) Scaler functions: Scalar functions accept 0 or more input parameters and return a single scalar value.
You use the CREATE FUNCTION Transact-SQL statement to create a function. The general syntax of the statement is as follows:

CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
[ = default ] }
[ ,...n ]
]
)
RETURNS return_data_type
[ WITH [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END [ ; ]
::=
{
[ ENCRYPTION ]
| [ SCHEMABINDING ]
| [ EXECUTE_AS_Clause ]
}

Example from AdventureWorks database
CREATE FUNCTION [dbo].[ufnGetStock](@ProductID [int])
RETURNS [int]
AS
-- Returns the stock level for the product. This function is used internally only
BEGIN
DECLARE @ret int;

SELECT @ret = SUM(p.[Quantity])
FROM [Production].[ProductInventory] p
WHERE p.[ProductID] = @ProductID
AND p.[LocationID] = '6'; -- Only look at inventory in the misc storage

IF (@ret IS NULL)
SET @ret = 0

RETURN @ret
END;
GO

--Execute
SELECT
p.[ProductID]
,p.[Name]
, dbo.ufnGetStock(p.ProductID) as [Stock]
FROM Production.Product p;

Result

B) Table-Valued Functions: The only difference in table-valued functions is that they return a table as output. Therefore, they are generally used in the FROM clause of a SELECT statement and possibly joined to other tables or views.

Syntax
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
[ = default ] }
[ ,...n ]
]
)
RETURNS @return_variable TABLE <>
[ WITH [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END [ ; ]

Example from AdventureWorks :
CREATE FUNCTION [dbo].[ufnGetContactInfo] (@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
[ContactID] int PRIMARY KEY NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[JobTitle] [nvarchar](50) NULL,
[Email] [nvarchar] (150) Null
)
AS
BEGIN
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT
c.[ContactID]
,c.[FirstName]
,c.[LastName]
,e.[Title] AS [JobTitle]
,c.[EmailAddress]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
WHERE c.[ContactID]= @ContactID;
END;

RETURN;
END;
GO
select * from ufnGetContactInfo(1209)
Result

C) Restrictions on UDFs
Although we can execute virtually any valid batch of code with in function, but still they do have some restrictions:
Most significant is you cannot use a function to change the state of any object in a database or the database itself.Therefore,you cannot insert, update, or delete data in tables, nor can you create, alter, or drop objects in the database. However, you can create one or more table variables and issue INSERT, UPDATE, and DELETE statements against the table variable

Please leave comment...

Views - the other face

2008-10-09T16:34:00.000+05:30

To put it in a simple way : A view is a SELECT statement that has a name and is stored in Microsoft SQL Server.

Views act as virtual tables to provide several benefits.

a) Re-usability : A view gives developers a standardized way to execute queries, enabling them to write certain common queries once as views and then include the views in application code so that all applications use the same version of a query.

b) Security : A view provides a level of security by giving users access to just a subset of data contained in the base tables that the view is built over and can give users a more friendly, logical view of data in a database.

c) Performance : a view with indexes created on it can provide dramatic performance improvements, especially for certain types of complex queries.

We can categorize views as :
a) regular views, b) updateable views, c) indexed views.

A) Syntax of view creation
CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH [ ,...n ] ]
AS select_statement [ ; ]
[ WITH CHECK OPTION ]
::=
{
[ ENCRYPTION ]
[ SCHEMABINDING ]
[ VIEW_METADATA ] }

ENCRYPTION : Should View be encrypted
SCHEMABINDING : will not allow you to drop a table, view or function reference by this view without dropping the view
VIEW_METADATA : returns metadata about a view to client-side data access libraries.

Select statement can be of any complexity as long as it is a valid query with following exceptions

should not have COMPUTE or COMPUTE BY clause
should not have INTO keyword
should not have OPTION clause
should not reference a temporary table or table variable
should not have ORDER BY clause unless it also specifies the TOP operator

Example of a view can be on AdventureWorks database
CREATE VIEW [vwEmployee]
AS
SELECT
e.[EmployeeID]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,e.[Title] AS [JobTitle]
,c.[Phone]
,c.[EmailAddress]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]

B) Modifying Data Through Views (UPDATEBLE VIEW)
Although views allows you to update data with some exception but it will be best practice if we use INSTEAD triggers.

Exception for data modification through VIEWS

All changes must directly reference column, not derivations of a column
View definition cannot contain GROUPBY or DISTINCT clause
You cannot modify columns that are derived through aggregate functions (AVG,COUNT, SUM,MIN,SUBSTRING)
You cannot reference columns generated by using operators such as union, crossjoin, and intersect.
You cannot use TOP when you specify WITH CHECK OPTION

Example:
UPDATE [vwEmployee ]
SET [Phone] ='123-256-1685'
WHERE [employeeID] =1

C) INDEXED VIEW

An indexed view, also called a materialized view, causes SQL Server to execute the SELECT statement in the view definition. SQL Server then builds a clustered index on the view’s results, and stores the data and index within the database. As you change data in the base tables, SQL Server propagates these changes to the indexed view. If the result of the view could change from one execution to another or could change if different query options were set, the entire set of data SQL Server calculated and stored would be invalidated. Therefore, all the operators or functions that can cause varying results are disallowed.

Restrictions for index views

View should be Schema bound
The SELECT statement cannot reference other views.
All functions must be deterministic. For example, you cannot use getdate() because every time it is executed, it returns a different date result.
AVG, MIN, MAX, and STDEV are not allowed.

Example to create index on above view,
We will need to have it schema bound
ALTER VIEW [vwEmployee]
WITH
SCHEMABINDING
AS
SELECT
e.[EmployeeID]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,e.[Title] AS [JobTitle]
,c.[Phone]
,c.[EmailAddress]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]

Now we can create index as
CREATE UNIQUE CLUSTERED INDEX [IX_vwEmployee_EmpID] ON [vwEmployee]
(
[EmployeeID] ASC
)

Welcome

2008-10-09T15:47:00.000+05:30

Hi,

Welcome to my blog which is solely devoted to SQL Server.

Good Luck and Cheers
Rahul Kumar
MCTS - SQL Server 2005