Scribd Logo
Upload

Welcome to Scribd!

  • 58 views

    Practical No.2 Perform The Extraction Transformation and Loading (ETL) Process To Construct The Database in The Sqlserver

    Uploaded by

    prerna inkar
    This document provides instructions for students to perform an Extract, Transform, Load (ETL) process to load data from a CSV file into a SQL Server database table. The steps include: 1) creating a CSV file with sample data, 2) defining a target SQL Server table, and 3) using SQL Server Data Tools to extract the CSV data, transform it by converting names to uppercase, and load it into the target table. Upon executing the ETL process, the data can be viewed in the target SQL Server table.

    Copyright:

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

    Practical No.2 Perform The Extraction Transformation and Loading (ETL) Process To Construct The Database in The Sqlserver

    Uploaded by

    prerna inkar
    58 views12 pages
    This document provides instructions for students to perform an Extract, Transform, Load (ETL) process to load data from a CSV file into a SQL Server database table. The steps include: 1) creating a CSV file with sample data, 2) defining a target SQL Server table, and 3) using SQL Server Data Tools to extract the CSV data, transform it by converting names to uppercase, and load it into the target table. Upon executing the ETL process, the data can be viewed in the target SQL Server table.

    Original Title

    Untitled

    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 instructions for students to perform an Extract, Transform, Load (ETL) process to load data from a CSV file into a SQL Server database table. The steps include: 1) creating a CSV file with sample data, 2) defining a target SQL Server table, and 3) using SQL Server Data Tools to extract the CSV data, transform it by converting names to uppercase, and load it into the target table. Upon executing the ETL process, the data can be viewed in the target SQL Server table.

    Copyright:

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

    Practical No.2 Perform The Extraction Transformation and Loading (ETL) Process To Construct The Database in The Sqlserver

    Uploaded by

    prerna inkar
    This document provides instructions for students to perform an Extract, Transform, Load (ETL) process to load data from a CSV file into a SQL Server database table. The steps include: 1) creating a CSV file with sample data, 2) defining a target SQL Server table, and 3) using SQL Server Data Tools to extract the CSV data, transform it by converting names to uppercase, and load it into the target table. Upon executing the ETL process, the data can be viewed in the target SQL Server table.

    Copyright:

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

    T.Y.BSc(I.

    T) SEM-VI Business Intelligence

    Practical No.2

    Perform the Extraction Transformation and Loading (ETL) process to construct the database in the
    Sqlserver.

    ETL(Extract, Transform and Load)

     ETL is a process in Data Warehousing and it stands for Extract, Transform and Load.
     It is a process, in which an ETL tool extracts the data from various data source systems, transforms
    it in the staging area and then finally, loads it into the Data Warehouse system.

    Note : For Practical No.2 we have to create .CSV file as source for ETL process.

    Step1 : Open notepad, and add the following records and save the file with name teachers.csv

    Comma-Separated Values(.CSV file)

    A comma-separated values file is a delimited text file that uses a comma to separate values. Each line of the file is a data
    record. Each record consists of one or more fields, separated by commas. The use of the comma as a field separator is the
    source of the name for this file format.

    Compiled By : Prof. Ansari Mohd. Shahid and Prof.Farzin Qureshi


    T.Y.BSc(I.T) SEM-VI Business Intelligence

    Step2: Now we will define the target in SQL SERVER Management Studio as follows.

    Note: Create same number of columns that we define in teachers.csv

    Now Open SQL Server Management Studio and Create Database and Table.

    Compiled By : Prof. Ansari Mohd. Shahid and Prof.Farzin Qureshi


    T.Y.BSc(I.T) SEM-VI Business Intelligence

    Press Control + S and Save the table with name Table_1

    Compiled By : Prof. Ansari Mohd. Shahid and Prof.Farzin Qureshi


    T.Y.BSc(I.T) SEM-VI Business Intelligence

    Step3: Now we will perform the ETL, So open SQL Server Data Tools(SSDT)

    Compiled By : Prof. Ansari Mohd. Shahid and Prof.Farzin Qureshi


    T.Y.BSc(I.T) SEM-VI Business Intelligence

    Drag and drop the Flat File Source from SSDT Tool Box.

    Compiled By : Prof. Ansari Mohd. Shahid and Prof.Farzin Qureshi


    T.Y.BSc(I.T) SEM-VI Business Intelligence

    Double Click on Flat File Source

    Click New

    Click Browse and Select the


    teacher.csv file

    Compiled By : Prof. Ansari Mohd. Shahid and Prof.Farzin Qureshi


    T.Y.BSc(I.T) SEM-VI Business Intelligence

    Compiled By : Prof. Ansari Mohd. Shahid and Prof.Farzin Qureshi


    T.Y.BSc(I.T) SEM-VI Business Intelligence

    We will be done with ETL Source.

    Now we will define the ETL target, select ADO.NET Destination from SSDT toolbox.

    Compiled By : Prof. Ansari Mohd. Shahid and Prof.Farzin Qureshi


    T.Y.BSc(I.T) SEM-VI Business Intelligence

    Double Click on ADO.NET Destination control

    Enter The SQL Server instance name = JOKER in this case

    2
    3

    Select DataBase

    Click Ok

    Compiled By : Prof. Ansari Mohd. Shahid and Prof.Farzin Qureshi


    T.Y.BSc(I.T) SEM-VI Business Intelligence

    Click Ok

    Step 3: Now we will define Transformation, for this drag and drop Derived Column from SSDT Toolbox

    Compiled By : Prof. Ansari Mohd. Shahid and Prof.Farzin Qureshi


    T.Y.BSc(I.T) SEM-VI Business Intelligence

    Double Click on Derived Column Control.

    Expand Columns and drag Select the UPPER case function


    from String Functions
    and drop the teacher name
    column here

    Apply the UPPER case


    Select Replace with
    function on teachername
    Teacher name colomn

    Connect the Blue arrows from left to right and execute the project.

    To execute Click

    Compiled By : Prof. Ansari Mohd. Shahid and Prof.Farzin Qureshi


    T.Y.BSc(I.T) SEM-VI Business Intelligence

    To see the output goto SQL Server Management Studio and Check the Table_1

    Compiled By : Prof. Ansari Mohd. Shahid and Prof.Farzin Qureshi

    You might also like