Scribd Logo
Upload

Welcome to Scribd!

  • 51 views

    Practical No: 2B Perform The Extraction Transformation and Loading (ETL) Process To Construct The Database in The SQL Server

    Uploaded by

    Natsu Dragneel
    This document provides steps to perform an Extract, Transform, Load (ETL) process using SQL Server to construct a database. It involves restoring a backup file, creating an SSIS package to extract data from the Sales.Store table, transform the data, and load it into a new table called OLE DB Destination. The final step is to execute a SELECT query on the new table to output the extracted and transformed data. The steps guide connecting to SQL Server, restoring a backup, setting up the SSIS package, extracting and transforming data in a data flow task, and loading it into the destination table.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Practical No: 2B Perform The Extraction Transformation and Loading (ETL) Process To Construct The Database in The SQL Server

    Uploaded by

    Natsu Dragneel
    51 views13 pages
    This document provides steps to perform an Extract, Transform, Load (ETL) process using SQL Server to construct a database. It involves restoring a backup file, creating an SSIS package to extract data from the Sales.Store table, transform the data, and load it into a new table called OLE DB Destination. The final step is to execute a SELECT query on the new table to output the extracted and transformed data. The steps guide connecting to SQL Server, restoring a backup, setting up the SSIS package, extracting and transforming data in a data flow task, and loading it into the destination table.

    Original Title

    2bbi.pdf

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document provides steps to perform an Extract, Transform, Load (ETL) process using SQL Server to construct a database. It involves restoring a backup file, creating an SSIS package to extract data from the Sales.Store table, transform the data, and load it into a new table called OLE DB Destination. The final step is to execute a SELECT query on the new table to output the extracted and transformed data. The steps guide connecting to SQL Server, restoring a backup, setting up the SSIS package, extracting and transforming data in a data flow task, and loading it into the destination table.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    51 views13 pages

    Practical No: 2B Perform The Extraction Transformation and Loading (ETL) Process To Construct The Database in The SQL Server

    Uploaded by

    Natsu Dragneel
    This document provides steps to perform an Extract, Transform, Load (ETL) process using SQL Server to construct a database. It involves restoring a backup file, creating an SSIS package to extract data from the Sales.Store table, transform the data, and load it into a new table called OLE DB Destination. The final step is to execute a SELECT query on the new table to output the extracted and transformed data. The steps guide connecting to SQL Server, restoring a backup, setting up the SSIS package, extracting and transforming data in a data flow task, and loading it into the destination table.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 13

    Practical no: 2B

    Perform the Extraction Transformation and Loading (ETL) process to construct the
    database in the SQL server.
    Software requirements: SQL SERVER 2012 FULL VERSION (SQLServer2012SP1-FullSlipstream-ENU-x86)
    Step 1: Open SQL Server Management Studio to restore backup file

    Now set the server name and select the authentication type as shown below and click on “Connect”:

    Step 2: Right click on Databases → Restore Database


    Step 3: Select Device → click on icon towards end of device box

    Step 4: Click on Add → Select path of backup files


    Step 5: Select both files at a time

    Step 6 : Click ok and in select backup devices window Add both files of AdventureWorks
    Step 7: Open SQL Server Data Tools Select File → New → Project → Business Intelligence → Integration
    Services Project & give appropriate project name.

    Environment consists of SQL Server Integration Services(SSIS)


    Step 8: Right click on Connection Managers in solution explorer and click on New Connection Manager. Add
    SSIS connection manager window appears.

    Step 9: Select OLEDB Connection Manager and Click on Add


    Step 10: Configure OLE DB Connection Manager window appears → Click on New

    Step 11: Select Server name(as per your machine) from drop down and database name and click on Test
    connection.

    If test connection succeeded click on OK


    Step 12: Click on OK

    Connection is added to connection manager


    Step 13: Drag and drop Data Flow Task in Control Flow tab

    Step 14: Drag OLE DB Source from Other Sources and drop into Data Flow tab
    Step 15: Double click on OLE DB source → OLE DB Source Editor appears→ click on New to add connection
    manager. Select [Sales].[Store] table from drop down → ok

    Step 16: Drag ole db destination in data flow tab and connect both
    Step 17: Double click on OLE DB destination Click on New to run the query to get [OLE DB Destination] in
    Name of the table or the view.

    Click on ok
    Step 18: Click on start

    Step 19: Go to SQL Server Management Studio In database tab → Adventureworks → Right click on [dbo].[OLE
    DB Destination] → Scrip Table as → SELECT To → New Query Editor Window
    Step 20: Execute following query to get output.
    USE [AdventureWorks2012]
    GO
    SELECT [BusinessEntityID]
    ,[Name]
    ,[SalesPersonID]
    ,[Demographics]
    ,[rowguid]
    ,[ModifiedDate]
    FROM [dbo].[OLE DB Destination]
    GO

    You might also like