How to import excel with multiple sheets to the database using SSIS

How to import excel with multiple sheets to the database using SSIS

Introduction 

Generally we have task likr import the excel data to sal data table .. ??? we will use the ssis tool to overcome these tasks we know . if the same task we make some thing completex like single excel file having the multiple sheets (sheet1,steet2 ……) all the sheets table data need to import to database table 

Look here the process 

1. First create one excel file and name it as “Sampledata excel” and save it as 97-2003 excel file formate because my system have that version support for the reason .

2.creathe data like 

SNo	Name	Salary
1	Krishn	10000

And select the data – got to insert tab – select the table – one popup will come juct click ok – and save – like that create all the sheets with different data to chek

SNo	Name	Salary
2	Krishn1	20000

SNo	Name	Salary
3	Krishn2	30000

3. Create one SSIS project and add new ssis package

Go to Control flow tab – right click here and select variables – add one variable and name it as “VariableTableName” – select data type as string – value is sheet name “Sheet1$”(don’t forgot to put he $ symbole to the value ).

4.In control flow tab drag and drop the For Each loop and – right click on the for each loop and click edit – in that go to collocation tab –  select “For Each ADO.NET Schema ROOT Set ” in the enumerator – in enumerator configuration select connection as new connection – one popup will open – in that click new again one more popup will open – in that select the provider as –

“Microsoft Jet 4.0 OLE DB Provider” click ok – and browse excel .xls file – and go to ALL tab – in Advanced properties “Extended Provider as Excel 8.0” text the connection ok .

Select schema type as table 

Drag on  data flow in for each loop – double click on the data flow it will redirect to the Data flow tab

In that – drag 2 tools Source as Excel , Destination as OLEDB 

Browse the excel sheet and select the data access mode to “table name or view name ” it will populate the variables name which we created before

Read More »

SQL Server – Working with OFFSET (paging in SQL server )

SQL Server – Working with OFFSET (paging in SQL server )

Introduction 

In this article I am explaining about how to access the table rows between 2 intervals. Later versions we have use logics to get these type of data, but in SQL server 2012 onwards very simple to get this data with simple queries 

For Example:- 

I am using the AdventureWorks2012  data base

SELECT ROW_NUMBER()OVER(ORDER BY p.Name) AS Sno,* FROM Production.Product p ORDER BY p.Name

SELECT ROW_NUMBER()OVER(ORDER BY p.Name)AS Sno,* FROM Production.Product p ORDER BY p.Name

offset 10 rows FETCH NEXT 5 ROWS ONLY

SELECT ROW_NUMBER()OVER(ORDER BY p.Name)AS Sno,* FROM Production.Product p ORDER BY p.Name

offset 5 rows FETCH NEXT 5 ROWS ONLY

In the above query having the key words like “OFFSET” is nothing starts with and “FETCH NEXT” mean how many rows to fetch from the table 

For example 

SELECT ROW_NUMBER()OVER(ORDER BY p.Name)AS Sno,* FROM Production.Product p ORDER BY p.Name

offset 5 rows FETCH NEXT 5 ROWS ONLY

“OFFSET” get the records from 6^th row to “FETCH NEXT” next  5 rows .

Read More »

How to create XML format from SQL Table Using SQL Server

How to create XML format from SQL Table Using SQL Server 

1. Copy the code and execute in DB

CREATE TABLE [dbo].[Employees](

        [EmployeeID] [bigint] NOT NULL,

        [FirstName] [varchar](20) NULL,

        [Title] [varchar](50) NULL,

        [BirthDate] [datetime] NULL,

        [Salary] [money] NULL,

CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED

(

        [EmployeeID] ASC

))

CREATE TABLE [dbo].[Customers](

        [CustomerID] [int] NOT NULL,

        [CustomerName] [varchar](50) NULL,

CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED

(

        [CustomerID] ASC

))

CREATE TABLE [dbo].[EmployeesCustomers](

        [EmployeeID] [int] NOT NULL,

        [CustomerID] [int] NOT NULL,

CONSTRAINT [PK_EmployeesCustomers] PRIMARY KEY CLUSTERED

(

        [EmployeeID] ASC,

        [CustomerID] ASC

))

INSERT INTO Employees VALUES (1,'Krishna','Team Leader'   ,'1947-11-25', 100.00)

INSERT INTO Employees VALUES (2,'Ravi'  ,'HR Manager'    ,'1967-04-20', 100.00)

INSERT INTO Employees VALUES (3,'Durga'  ,'Office Cleaner','1978-08-03', 10.00)

INSERT INTO Employees VALUES (4,'Vijay','Mail Deliverer','1989-12-01', 20.00)

INSERT INTO Employees VALUES (5,'Ranga'  ,'Developer'     ,'1995-01-01', 50.00)

INSERT INTO Employees VALUES (6,'Raju','QA Person'     ,'1995-05-13', 50.00)

INSERT INTO Customers VALUES (1,'TCS')

INSERT INTO Customers VALUES (2,'SBI')

INSERT INTO Customers VALUES (3,'WIPRO')

INSERT INTO Customers VALUES (4,'HSBC')

INSERT INTO Customers VALUES (5,'Accenture')

INSERT INTO Customers VALUES (6,'IBM')

INSERT INTO Customers VALUES (7,'Capgemini')

 

INSERT INTO EmployeesCustomers VALUES (1,5)

INSERT INTO EmployeesCustomers VALUES (1,6)

INSERT INTO EmployeesCustomers VALUES (1,7)

INSERT INTO EmployeesCustomers VALUES (2,1)

INSERT INTO EmployeesCustomers VALUES (2,2)

INSERT INTO EmployeesCustomers VALUES (3,3)

INSERT INTO EmployeesCustomers VALUES (3,4)

After that past the below code to generate XML data 

SELECT (

  SELECT TOP 1 NULL as N,

    (SELECT Employee.EmployeeId as Id, Employee.FirstName as Name,

      (SELECT TOP 1 NULL as N,

        (SELECT Customer.CustomerID as Id, CustomerName as Name

         FROM EmployeesCustomers INNER JOIN Customers Customer

         ON EmployeesCustomers.CustomerID = Customer.CustomerID

         WHERE EmployeesCustomers.EmployeeId = Employee.EmployeeId

         ORDER BY Customer.CustomerID

         FOR XML AUTO, TYPE)

       FROM Customers

       FOR XML AUTO, TYPE)

     FROM EmployeesCustomers INNER JOIN Employees Employee

     ON EmployeesCustomers.EmployeeID = Employee.EmployeeID

     GROUP BY Employee.EmployeeId, Employee.FirstName

     ORDER BY Employee.EmployeeId

     FOR XML AUTO, TYPE)

  FROM Employees

  FOR XML AUTO, ROOT('Company')

) AS COL_XML  

 OutPut is look like 

<Company>

  <Employees>

    <Employee Id="1" Name="Krishna">

      <Customers>

        <Customer Id="5" Name="Accenture" />

        <Customer Id="6" Name="IBM" />

        <Customer Id="7" Name="Capgemini" />

      </Customers>

    </Employee>

    <Employee Id="2" Name="Ravi">

      <Customers>

        <Customer Id="1" Name="TCS" />

        <Customer Id="2" Name="SBI" />

      </Customers>

    </Employee>

    <Employee Id="3" Name="Durga">

      <Customers>

        <Customer Id="3" Name="WIPRO" />

        <Customer Id="4" Name="HSBC" />

      </Customers>

    </Employee>

  </Employees>

</Company>

Read More »