Difference between DTS & SSIS

DTS	SSIS
SQL Server 7.0 introduces available on 2000 onwards	SQL Server 2005 onwards its available
Designed for ETS (Extract Transform Sources)	Designed for ETL (Extract Transform Load)
It Consists of Single Window for all operations. It has data transformations like work flow etc	It Consists of multiple windows for multiple operations. It has ControlFlow,Data Flow, Event Handler, Package Explorer.
Data transformations available.	Dataflow task introduced and all transformations are embedded.
Message boxes displayed in Active-X Script.	Message boxes displayed in Script task.
Less Transformations	More Transformations
Partial BI Support	Full BI Support
No Deployment Wizard	Deployment wizards are introduced.
No DSV(Data source view) , No Connection manger, No Event handlers, No looping through folders & files	Introduced in SSIS
Saved in Enterprise manager(SQL Server) & File system (Structured storage file)	Saved in local file system, deployed in SQL Server.
DTS Package looks like as follows	SSIS Package looks like as follows

 

Dynamically Create Excel Files depends on Export Data in SSIS

Scenario

     In this Article, we will explain dynamically creation of new Excel files in SSIS depends on Export data from SQL Server table.

Solution

    The solution here was to create new excel file with Department Name at the package ran and export Department names from the Database.

• Let’s create a table with following structure.

CREATE TABLE [dbo].[Emp_Data](

     [Eid] [int] NULL,

     [Ename] [varchar](50) NULL,

     [Sal] [float] NULL,

     [Dept] [varchar](10) NULL

)

GO

• Insert the values into the table of [dbo].[Emp_Data]

insert into Emp_Data values(1,'Ken J',15000,'IT')

insert into Emp_Data values(3,'Rob M',25000,'Coding')

insert into Emp_Data values(4,'Diane L',20000,'DMS')

insert into Emp_Data values(5,'Janice M',12000,'IT')

insert into Emp_Data values(6,'Kevin F',12500,'DMS')

insert into Emp_Data values(2,'Terri Lee',10000,'Coding')

• Create SSIS Package and Create variables as shown

• Take Execute SQL Task from Control Flow pane and configure as shown. The goal is to select the Department name from table and store into Object Type variable.

• Add Foreach loop Container to the package and select “Edit”. On the resulting dialog, click the “Collections” tab in the left-hand pane. On the right-hand pane, select the enumerator type of “Foreach ADO Enumerator”. The bottom portion in the right-hand pane will change. In that lower area, click the drop-down for the “ADO object source variable” and select the variable “User::Dataset”. And the “Enumerator Mode” changes the value in to “Rows in the first table”.

• Now click on the “Variable Mappings” tab of the Foreach Loop Editor and select “User::DeptName” and assign value “0” in Index.

• Let’s take Script task in Control Flow. Choose the variables that we need to use in Script task as shown below.

• Click on Edit Script and then we need to add reference to Microsoft.Office.Interop.Excel dll

• Use the below script in Script task.The code is coloured in Red. You can copy that and paste in the Script Task.

/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

using Excel = Microsoft.Office.Interop.Excel;

using System.Data.OleDb;

using System.Reflection;

using System.Diagnostics;

namespace ST_825e524384ad45d6994d16bda6651279.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        /*
  The execution engine calls this method when the task executes.
  To access the object model, use the Dts property. Connections, variables, events,
  and logging features are available as members of the Dts property as shown in the following examples.

  To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
  To post a log entry, call Dts.Log("This is my log text", 999, null);
  To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

  To use the connections collection use something like the following:
  ConnectionManager cm = Dts.Connections.Add("OLEDB");
  cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

  Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
 
  To open Help, press F1.
 */

public void Main()

{

 // Store the Filepath

 String Filepath = "";           

 Filepath = Dts.Variables["User::ExcelFilePath"].Value.ToString();

 System.Data.DataTable dt = new System.Data.DataTable();

 Dts.Variables["User::ExcelFilePath"].Value =                                     Dts.Variables["User::ExcelFilePath"].Value+""+

                  Dts.Variables["DeptName"].Value + ".xls"  ;

 //Excel sheet

 Excel.Application oXL = new Excel.ApplicationClass();

 Excel.Workbooks oWBs = oXL.Workbooks;

 Excel.Workbook oWB = null;

 /* Set some properties oXL.Visible = true;*/

 oXL.DisplayAlerts = false;

 // Get a new workbook.

 oWB = oXL.Workbooks.Add(Missing.Value);

 oWB.SaveAs(Dts.Variables["User::ExcelFilePath"].Value,                    Excel.XlFileFormat.xlWorkbookNormal,Missing.Value,                    Missing.Value, Missing.Value, Missing.Value,

           Excel.XlSaveAsAccessMode.xlExclusive,

             Missing.Value, Missing.Value, Missing.Value,

                Missing.Value, Missing.Value);

 oWB.Close(false, Dts.Variables["User::ExcelFilePath"].Value,             Missing.Value);

 oWBs.Close();

 oXL.Quit();

 Dts.Variables["User::ExcelFilePath"].Value = Filepath;

 // TODO: Add your code here

 Dts.TaskResult = (int)ScriptResults.Success;

}// End of Main

}// End of Microsoft...

}// End of namespace

• Final Output:

Let’s execute our SSIS Package and see the before and after see the files in the folder.

Before Execution

After Execution

Let me know what you think about this article. 

One Statement for Insert, Update, Delete operations in SQL Server (Merge)

Introduction

       In this article, we will explain to perform Insert, Update, and Delete operations on Target table by matching the records from Source table by using a Single Statement in SQL Server.

Solution

      Generally we write separate statements to Insert, Update and Delete data based on certain conditions in SQL Server. But Merge statement using which we can do three operations in one statement. Simply says to perform multiple DML operations in one statement depends on some conditions. It is similar to the UPSERT command in Oracle.

Let’s create Employee1 and Employee2 details and inserted some records.

• Employee1 Details

create table Employee1

(Eid int,

Ename varchar(50),

Sal float)

insert into Employee1 values(1,'Ken J',15000)

insert into Employee1 values(2,'Terri Lee',10000)

insert into Employee1 values(3,'Rob M',25000)

insert into Employee1 values(4,'Diane L', 110000)

• Employee2 Details

create table Employee2

(Eid int,

Ename varchar(50),

Sal float)

insert into Employee2 values(1,'Ken J',15000)

insert into Employee2 values(3,'Rob M',25000)

insert into Employee2 values(5,'Janice M',12000)

insert into Employee2 values(2,'Terri Lee',33330)

insert into Employee2 values(4,'Diane L',20000)

• The data in two tables are shown below

Eid	Ename	Sal
1	Ken J	15000
2	Terri Lee	10000
3	Rob M	25000
4	Diane L	20000

Eid	Ename	Sal
1	Ken J	15000
2	Terri Lee	33330
3	Rob M	25000
4	Diane L	110000
5	Janice M	12000

In our example we will consider the main conditions are shown below

• Delete the records whose salary is greater than 100000.

• Update salary of Target table if the records exist in Source table and get the salary from Source table.

• Insert the records if record does not exist in Target table.

Now we will write the Merge statement to satisfied the above three conditions.

Merge into Employee1 T

using Employee2 S

on T.eid=S.eid

when matched and T.sal>100000 then Delete

when matched then update set T.sal=S.sal

when not matched then

insert (eid,ename,sal) values (S.eid,S.ename,S.sal);

Semicolon is mandatory after the Merge Statement.

• After Execute the Merge Statement

Final Output is

Eid	Ename	Sal
1	Ken J	15000
2	Terri Lee	33330
3	Rob M	25000
5	Janice M	12000

• Explanation is Merge Statement simply explain diagrammatically as follows.

Let me know what you think about this article.