Microsoft SQL Server

SQL Server/ MSSQL

SQL Server is back end tool and developed by Microsoft. SQL Server is platform dependent as it works only on Windows OS.
Front end applications will be developed by using Java, .Net
Back end applications will be developed by using SQL Server, Oracle, My SQL, DB2

Platform = OS + Processor
Platform Independent = Any OS + Any Processor = Oracle
Platform Dependent = One OS + Any Processor = SQL Server

UI: UI are of two types — CUI (Character User Interface) and GUI (Graphical User Interface)
CUI: Operations like CREATE, UPDATE, DELETE, INSERT and so on..will be performed using Query
GUI: Above operations will be performed without Query

SQL Server provides more GUI facility.

SQL Server Installation:
At the time of installation we provide instance name.

[table id=9 /]

---

Differences between Equi Join and Non-Equi Join
Equi Join:
→ Retrieving data from multiple tables based on equality condition (=)
→ The following operators cannot be used <, >, <=, >=, !<, !>, !=
→ SELECT * FROM TABLE1, TABLE2 WHERE TABLE1.COL_NAME = TABLE2.COL_NAME;
→ Only matching records will be displayed which has same value in both the tables in COL_NAME
Non-Equi Join:
→ Retrieving data from multiple tables based on any condition except = condition
→ The following operators can be used <, >, <=, >=, !<, !>, !=, BETWEEN, AND, OR
→ The following operator cannot be used =

---

Unique, Not Null and Check Constraints
→ UNIQUE
♦ Restricts duplicate data but accepts NULL
♦ Syntax: <column_name> <datatype [size]> UNIQUE
→ NOT NULL
♦ Restricts NULLS but accepts duplicate values
♦ Syntax: <column_name> <datatype [size]> NOT NULL
→ PRIMARY KEY = UNIQUE + NOT NULL
→ CHECK
♦ To check value before inserting into a column. Apply condition on column
♦ Syntax: <column_name> <datatype [size]> CHECK (condition)
♦ Ex: CREATE TABLE T1 (EID INT, SAL MONEY CHECK(SAL >= 5000))

---

Clauses in SQL Server
1) GROUP BY
→ Used to Group the similar data
Ex: Query to get count of employees in each Job – SELECT JOB, COUNT(*) FROM EMP GROUP BY JOB;
Ex: Query to get sum of salaries in each Job – SELECT JOB, SUM(SALARY) FROM EMP GROUP BY JOB;
Ex: Query to get max, min and avg salaries in each Job – SELECT JOB, MAX(SALARY), MIN(SALARY), AVG(SALARY) FROM EMP GROUP BY JOB;
Ex: Query to get number of employees in each job and in each dept – SELECT JOB, COUNT(*), DEPTNO FROM EMP GROUP BY JOB, DEPTNO;
2) HAVING
→ To filter the records after Grouping the data
→ This should be used only with GROUP BY clause
Ex: Query to get count of employees in each Job which has count less than 4- SELECT JOB, COUNT(*) FROM EMP GROUP BY JOB HAVING COUNT(*) < 4;
Ex: Query to get count of employees in each Job which has salary greater than 100000 – SELECT JOB, SUM(SALARY) FROM EMP GROUP BY JOB HAVING SUM(SALARY) > 100000;

---

TCL – Transaction Control Language
Transaction means to perform some operation.
→ BEGIN TRANSACTION
→ COMMIT
→ ROLLBACK
→ SAVEPOINT

Unlike Oracle, DML operations like INSERT, UPDATE, DELETE gets permanently executed in SQL Server. They are AUTOCOMMIT so after performing these operations we cant ROLLBACK. AUTOCOMMIT is Implicit COMMIT.
The below statement doesn’t work here:
BEGIN TRANSACTION
ROLLBACK;

To ROLLBACK such transactions we have to use DML statements as below syntax:
BEGIN TRANSACTION
<DML Operation>
BEGIN TRANSACTION
ROLLBACK
Means start your DML operation with BEGIN TRANSACTION key word. Later you have use COMMIT explicitly to permanently save the transaction. If we do not use DML operation with BEGIN TRANSACTION key word then they get AUTOCOMMIT. 

→ BEGIN TRANSACTION: To start the transaction
Syntax:
BEGIN TRANSACTION
<sql statements>;

→ COMMIT: To make the transaction permanent by Explicitly (user has to COMMIT).  We cannot ROLLBACK once the operation is COMMIT.
Syntax:
BEGIN TRANSACTION
<sql statements>
COMMIT;

→  ROLLBACK: To bring back to previous state (undo operation). This works only before COMMIT
Syntax:
BEGIN TRANSACTION
<sql statements>
ROLLBACK;

→  SAVEPOINT: Used to create a temporary memory to store the values which we want to ROLLBACK
Syntax:
BEGIN TRANSACTION
<sql statements>
SAVE TRANSACTION <pointer name>
<write statement which has to be ROLLBACK>
Ex:
BEGIN TRANSACTION
DELETE .1 …….
DELETE 2 ……..
SAVE TRANSACTION S1
DELETE 3 …….. –Here DELETE3 statement will be roll backed which has pointer S1.

BEGIN TRANSACTION
ROLLBACK TRANSACTION S1; — Now DELETE3 statement will not be deleted

---

Difference between CHAR and VARCHAR data type
CHAR(Size) — Size will be of fixed length. Static data type – wont change during execution time. It stores Characters and Alpha numeric characters. This is non unicode data type means for every character it allocates one byte [1 character = 1 byte]. Max size 8000 bytes  means 8000 characters. Memory gets wasted due to fixed length.
VARCHAR(Size/ Max) — Dynamic length. Used to store characters and alpha numeric characters. Non-Unicode [1 character = 1 byte]. VARCHAR(Size) = Max length is 8000 bytes.  VARCHAR(Max) = 2GB.

---

Difference between Unicode and Non-Unicode Datatypes
Character Datatypes: The value should be in string (should be in single quote). It stores Characters [A to Z or a to z]and Alpha numeric characters [A to Z or a to z or numbers 0 to 9 or special characters]
[table id=10 /]

---

ROLLUP and CUBE clauses
→ To find Sub and Grand Total of the given group of values.
→ ROLLUP clause is used to find Sub & Grand Total based on a single column.
→ CUBE clause is used to find Sub & Grand Total based on a multiple columns.
→ These two clauses should be used with GROUP BY
→ Syntax: SELECT <COLUMN1>, <COLUMN2> … FROM <TABLENAME> GROUP BY ROLLUP/ CUBE (<COLUMN1>, <COLUMN2>…);
Ex: SELECT JOB, COUNT(*) FROM EMP GROUP BY ROLLUP(JOB); = SELECT JOB, COUNT(*) FROM EMP GROUP BY CUBE(JOB);
Ex: SELECT JOB, COUNT(*), DEPTNO FROM EMP GROUP BY ROLLUP(JOB, DEPTNO); — Based on job
Ex: SELECT JOB, COUNT(*), DEPTNO FROM EMP GROUP BY CUBE(JOB, DEPTNO); — Based on job and deptno

---

Stored Functions in T-SQL
→ It must return a value which is similar to Oracle. Also called as User defined functions.
→ We have two types of Stored functions:
1. Scalar valued functions
2. Table valued functions

1. Scalar valued function: Function returns a single value or a single column value from the table. Cannot return more than one value
Syntax: CREATE [ALTER] FUNCTION <FNAME> (@<PARAMETER1> <DataType>[Size], ………….)
RETURNS <returns (PARAMETER/ ATTRIBUTE/ VARIABLE) datatype>
AS
BEGIN
<FUNCTION BODY/ STATEMENTS>
RETURN <return (PARAMETER/ ATTRIBUTE/ VARIABLE) name>
END;
Syntax to call Scalar valued function:
SELECT dbo.<FNAME> (Single Parameter/ Multiple Parameters);
Ex: Create a scalar valued function to return gross salary of employee based on following conditions.
Condition1: HRA 10%
Condition2: DA 20%
Condition2: PF 10%
CREATE FUNCTION F_GRSAL (@EID INT)
RETURNS MONEY
AS
BEGIN
DECLARE @BASIC MONEY, @HRA MONEY, @DA MONEY, @PF MONEY, @GROSS MONEY
SELECT @BASIC = SALARY FROM EMP WHERE EID = @EID
SET @HRA = @BASIC*0.1
SET @DA = @BASIC*0.2
SET @PF = @BASIC*0.1
SET @GROSS = @BASIC + @HRA + @DA + @PF
RETURN @GROSS
END
After compilation we can this function under programability >> Functions >> Scalar-Valued Functions
SELECT dbo.F_GRSAL(102)

2. Table valued function: Function returns more than one value or more than one column value from table.
Syntax: CREATE [ALTER] FUNCTION <FNAME> (@<PARAMETER1> <DataType>[Size], ………….)
RETURNS TABLE
AS
RETURN <SELECT Query>;
Syntax to call Table valued function:
SELECT * FROM <FNAME> (Single Parameter/ Multiple Parameters);
Ex: Create a table valued function to accept dept name as a parameter and return the list of employees who are working in the given dept name.
Solution: Go to Object Explorer >> Database name >> Programability >> Functions >> Check the functions which were created.
CREATE FUNCTION FNAME (@P_DEPTNAME VARCHAR2(10))
RETURNS TABLE
AS
RETURN (SELECT * FROM EMPLOYEE WHERE DEPTNAME = @P_DEPTNAME);

SELECT * FROM FNAME (‘HR’); — We will get employees from EMPLOYEE table who are working in HR dept.

---

Ranking Function
→ To assign Ranks to each row wise and group of rows wise.
→ We have three types of Ranking functions
1. ROW_NUMBER()
2. RANK()
3. DENSE_RANK()
→ Two important clauses in Ranking function are PARTITION BY and ORDER BY. PARTITION BY clause is not mandatory in Ranking function but we go with PARTITION BY clause only when we use group of rows. ORDER BY clause is mandatory in Ranking function.
→ Syntax:
[ROW_NUMBER()/ RANK()/ DENSE_RANK()] OVER(PARTITION BY <column_name> ORDER BY <column_name> [ASC/ DESC])
Ex: SELECT ENAME, SALARY, ROW_NUMBER() OVER ORDER BY (SALARY DESC) AS RANKS FROM EMP; — 1,2,3,4,5
Ex: SELECT ENAME, SALARY, RANK() OVER ORDER BY (SALARY DESC) AS RANKS FROM EMP; — 1,2,2,4,5
Ex: SELECT ENAME, SALARY, DENSE_RANK() OVER ORDER BY (SALARY DESC) AS RANKS FROM EMP; — 1,2,2,3,4
Ex: SELECT ENAME, SALARY, ROW_NUMBER() OVER (PARTITION BY DEPTNO) ORDER BY (SALARY DESC) AS RANKS FROM EMP; — GROUP BY DEPTNO you will get Ranks ORDER BY SAL with ROW_NUMBER functionality
Ex: SELECT ENAME, SALARY, RANK() OVER (PARTITION BY DEPTNO) ORDER BY (SALARY DESC) AS RANKS FROM EMP; — GROUP BY DEPTNO you will get Ranks ORDER BY SAL with RANK functionality
Ex: SELECT ENAME, SALARY, DENSE_RANK() OVER (PARTITION BY DEPTNO) ORDER BY (SALARY DESC) AS RANKS FROM EMP; — GROUP BY DEPTNO you will get Ranks ORDER BY SAL with DENSE_RANK functionality

---

Sub Blocks in T-SQL
Sub blocks are named blocks. The code which is written in sub block is saved in DB and its reusable in any application like .net/ java
Two types of Sub blocks exist:
1. STORED PROCEDURE
2. STORED FUNCTION

Working with Stored Procedure:
→ Block of code
→ To perfrom some operation based on logic
→ These are pre-compiled blocks means one-time compilation.
Types of Parameters in SP:
1. IN/ INPUT parameter: Default parameters. Will store the input values during run time.
2. OUT/ OUTPUT parameter: When the procedure want to return a value.
Syntax: CREATE [OR ALTER] PROCEDURE <PRO_NAME> (@<parameter_name> <datatype[size]> <Type of parameter>, ….)
AS
BEGIN
<SQL Statements>
END
Syntax to call SP: EXECUTE/ EXEC <PRO_NAME> VALUE/ VALUES
Ex: Create a procedure to insert new data into emp table.
CREATE PROCEDURE SPINPUT (@EID INT, @ENAME VARCHAR(20), @SAL MONEY)
AS
BEGIN
INSERT INTO EMP VALUES (@EID, @ENAME, @SAL)
END
EXECUTE SPINPUT 101, ‘ABC’, 1000

→ These procedures can be created in two ways.
1. Without parameters
2. With parameters

1. Stored Procedure Without Parameters
Syntax: CREATE [OR ALTER] PROCEDURE <sp_name>
AS
BEGIN
<SQL Statements>
END;
Syntax to call stored procedure: EXECUTE <sp_name> or EXEC <sp_name>
Ex: CREATE PROCEDURE <sp_name>
AS
BEGIN
SELECT * FROM EMP
END;
EXECUTE sp_name;
Navigation to view Procedure: Object Explorer >> Database name >> Programability >> Stored Procedures >> Check the procedures which are created.

2. Stored Procedure With ‘OUT’ Parameters
We have three steps:
1) Declare bind variables for OUT parameters. Syntax: DECLARE @<BIND_VARIABLE_NAME> <DATATYPE[Size]>,…
2) Adding the bind variables to a stores procedure. Syntax: EXECUTE/ EXEC <PROC_NAME> VALUE1, VALUE2,…,@<BIND_VARIABLE_NAME> OUT
3) To print the bind variables. Syntax: PRINT @<BIND_VARIABLE_NAME>

Ex: Create a SP with EID as input and return the emp provident fund and professional tax on salary by following conditions.
Condition1: PF 10%
Condition2: PT 20%
Solution:
CREATE PROCEDURE SP_NAME(@EID INT, @PF OUT INT, @PT OUT INT)
AS
BEGIN
DECLARE @SAL MONEY
SELECT @SAL=SALARY FROM EMP WHERE EID = @EID
SET @PF=@SAL*0.1
SET @PT=@SAL*0.2
END

Output:
DECLARE @bPF INT, @bPT INT
EXECUTE SP_NAME 102, @bPF OUT, @bPT OUT
PRINT ‘Provident Fund is: ‘+CAST(@bPF AS VARCHAR)
PRINT ‘Professional Tax is: ‘+CAST(@bPT AS VARCHAR)

---

Primary Key and Foreign Key Relation on Table with/ without Cascade Rules
Establishing relationship between tables by using PK and FK constraints without cascade rules:
CREATE TABLE P1(EID INT PRIMARY KEY, ENAME VARCHAR(20), SAL MONEY)
INSERT INTO P1 VALUES(10, ‘ABC’, 1000), (20, ‘DEF’, 2000), (30, ‘GHI’, 3000)
CREATE TABLE F1(MBNO BIGINT, EADDRESS VARCHAR(50), EID INT FOREIGN KEY REFERENCES P1(EID))
INSERT INTO F1 VALUES(987654321, ‘HYD’, 10)
INSERT INTO F1 VALUES(987654322, ‘HYD’, 10)
INSERT INTO F1 VALUES(987654323, ‘BLR’, 20)
INSERT INTO F1 VALUES(987654324, ‘CHN’, 30)
SELECT * FROM P1
SELECT * FROM F1
We have three rules here:
1. Insertion rule
2. Updation rule
3. Deletion rule

Testing Insertion rule: INSERT INTO F1 VALUES (987653221, ‘MAD’, 40) — Not inserted since 40 not in P1. This rule is fixed.
Testing Updation rule: UPDATE P1 SET EID=1 WHERE EID=10 — Error since we have data in dependent table C1
Testing Deletion rule: DELETE FROM P1 WHERE EID = 30 — Error since we have data in dependent table C1

Establishing relationship between tables by using PK and FK constraints with cascade rules:
When we want to perform UPDATE and DELETE operations on parent table reference column and also child table reference column. Cascade rules are applicable on child table on FK column only.
Two types:
1. ON UPDATE CASCADE: This is applicable when we want to update data in parent table along with child table
2. ON DELETE CASCADE: This is applicable when we want to delete data in parent table along with child table
CREATE TABLE P2(EID INT PRIMARY KEY, ENAME VARCHAR(20), SAL MONEY)
INSERT INTO P2 VALUES(10, ‘ABC’, 1000), (20, ‘DEF’, 2000), (30, ‘GHI’, 3000)
CREATE TABLE F2(MBNO BIGINT, EADDRESS VARCHAR(50), EID INT FOREIGN KEY REFERENCES P2(EID) ON UPDATE CASCADE ON DELETE CASCADE)
INSERT INTO F2 VALUES(987654321, ‘HYD’, 10)
INSERT INTO F2 VALUES(987654322, ‘HYD’, 10)
INSERT INTO F2 VALUES(987654323, ‘BLR’, 20)
INSERT INTO F2 VALUES(987654324, ‘CHN’, 30)
SELECT * FROM P1
SELECT * FROM F1

Testing Updation rule: UPDATE P2 SET EID=1 WHERE EID=10 — EID 1 gets updated in both the tables
Testing Deletion rule: DELETE FROM P2 WHERE EID = 30 — Records get deleted from both P2 & F2

---

Sub Queries/ Nested Queries
→ A query inside another query. Sub queries = Nested queries
Syntax: SELECT * FROM <TABLE_NAME> WHERE <CONDITION> (SELECT * FROM…(SELECT * FROM..));
Here SELECT * FROM <TABLE_NAME> WHERE <CONDITION> — Outer query
(SELECT * FROM…(SELECT * FROM..)); — Inner query
→As per execution process of sub query it again classified into two types:
1. Non Co-related sub query — First inner query gets executed and later outer query gets executed. The outer query is dependent on the result of inner query.
Again classified into two types:
1. Simple/ Single row sub query – When it returns a single value.
Ex: Query to get employee details who are getting the first highest salary in table – SELECT * FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP)
2. Multi rows sub query — When it returns more than one value. We have to use IN operator.
Ex: Display employee details from table whose dept is same as dept of Smith or Scott – SELECT * FROM EMP WHERE DEPT IN (SELECT DNAME FROM EMP WHERE ENAME IN (‘SMITH’, ‘SCOTT’)) or SELECT * FROM EMP WHERE DEPT IN (SELECT DNAME FROM EMP WHERE ENAME = ‘SMITH’ OR ENAME =  ‘SCOTT’)
2. Co-related sub query — First outer query gets executed and later inner query gets executed. The inner query is dependent on the result of outer query.

Differences between non co-related and co-related sub queries:
Below are Non co-related sub queries:
♦ Query to find 1st highest salary: SELECT * FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP)
♦ Query to find 2nd highest salary: SELECT * FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP WHERE SAL < SELECT MAX(SAL) FROM EMP))
♦ Query to find 3rd highest salary: SELECT * FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP WHERE SAL < SELECT MAX(SAL) FROM EMP WHERE SAL < SELECT MAX(SAL) FROM EMP )))

Below are Co-related sub queries:
Syntax: SELECT * FROM <TABLE> T1 WHERE N-1 = (SELECT COUNT(<COLUMN_NAME>) FROM <TABLE> T2 WHERE T2.COLUMN_NAME < or > AND T1.COLUMN_NAME)
♦ Query to find 1st highest salary (Here N=1): SELECT * FROM EMP E1 WHERE 0 = (SELECT COUNT(SAL) FROM EMP E2 WHERE E.SAL > E.SAL)
♦ Query to find 2nd highest salary (Here N=2): SELECT * FROM EMP E1 WHERE 1 = (SELECT COUNT(SAL) FROM EMP E2 WHERE E.SAL > E.SAL)
♦ Query to find 3rd highest salary (Here N=3): SELECT * FROM EMP E1 WHERE 2 = (SELECT COUNT(SAL) FROM EMP E2 WHERE E.SAL > E.SAL)

---

Views in SQL Server
→ Instead of fetching data from base table frequently its better to create view and make use of it
→ View is a virtual/ logical table for base table
→ It doesnt store actual data but the select query which was written during creation gets saved
→ It gets created with the help of SELECT statement
→ Two types of Views
1. Simple View: When we access the required data from a single base table. It supports all DML operations. Also called as UPDATABLE VIEW. Syntax: CREATE VIEW <view_name> AS SELECT * FROM <TABLE> [WHERE (condition)]; CREATE VIEW V1 AS SELECT * FROM EMP; Now check SELECT * FROM V1; INSERT V1 VALUES (106, ‘ABC’, 1000) — Executed and this change reflects on both base table and view as well.
2. Complex View: When we access the required data from multiple base tables. It doesnt support DML operations. It only supports ‘SELECT’ statement. Also called as NON-UPDATABLE VIEW.
Ex: CREATE VIEW V1 AS (SELECT * FROM T1 UNION SELECT * FROM T2);

---

Cursors in T-SQL
→ Cursor is a temp memory
→ Memory will be allocated by DB during DML operations
→ For storing DB tables
Types:
1. Implicit Cursor: Default cursor.
2. Explicit Cursor: User defined cursor. Fetch data from table in row-wise
Step1: DECLARE CURSOR OBJECT. Syntax: DECLARE <CURSOR_NAME> CURSOR FOR SELECT * FROM <TABLE>
Step2: OPEN CURSOR. Syntax: OPEN <CURSOR_CONNECTION>
Step3: FETCH DATA FROM CURSOR. Syntax: FETCH NEXT/ FIRST/ LAST/ PRIOR/ ABSOLUTE n/ RELATIVE n FROM <CURSOR_NAME> [INTO VARIABLES]
Step4: CLOSE CURSOR. Syntax: CLOSE <CURSOR_NAME>
Step5: DEALLOCATE CURSOR MEMORY. Syntax: DEALLOCATE <CURSOR_NAME>

Ex: Without Cursor Variables:
DECLARE C1 CURSOR SCROLL FOR SELECT * FROM EMP
OPEN C1
FETCH LAST FROM C1 — Last record
FETCH PRIOR FROM C1 — Last but one
FETCH ABSOLUTE 7 FROM C1 — 7th record from table
FETCH RELATIVE -2 FROM C1 — 5th record from table (above 7-2)
FETCH FIRST FROM C1
FETCH NEXT FROM C1
CLOSE C1
DEALLOCATE C1

Ex: With Cursor Variables:
DECLARE C1 CURSOR SCROLL FOR SELECT EID, ENAME FROM EMP
DECLARE @EID INT, @ENAME VARCHAR(20)
OPEN C1
FETCH LAST FROM C1 INTO @EID, @ENAME — Last record
PRINT ‘THE EMPLOYEE’+”+@ENAME+”+’ID’ +CAST(@EID AS VARCHAR)
FETCH PRIOR FROM C1 INTO @EID, @ENAME — Last but one
PRINT ‘THE EMPLOYEE’+”+@ENAME+”+’ID’ +CAST(@EID AS VARCHAR)
FETCH ABSOLUTE 7 FROM C1 INTO @EID, @ENAME — 7th record from table
PRINT ‘THE EMPLOYEE’+”+@ENAME+”+’ID’ +CAST(@EID AS VARCHAR)
FETCH RELATIVE -2 FROM C1 INTO @EID, @ENAME — 5th record from table (above 7-2)
PRINT ‘THE EMPLOYEE’+”+@ENAME+”+’ID’ +CAST(@EID AS VARCHAR)
FETCH FIRST FROM C1 INTO @EID, @ENAME
PRINT ‘THE EMPLOYEE’+”+@ENAME+”+’ID’ +CAST(@EID AS VARCHAR)
FETCH NEXT FROM C1 INTO @EID, @ENAME
PRINT ‘THE EMPLOYEE’+”+@ENAME+”+’ID’ +CAST(@EID AS VARCHAR)
CLOSE C1
DEALLOCATE C1

---

SSIS – SQL Server Integration Services
Install SSDT to access SSIS.
Create new Package
Navigation: File >> New >> Project >> Templates >> Business Intelligence >> Integration Services >> Select Integration Services Project >> Name: SSIS_Demo; Location: Browse and create own folder if required (like on desktop); Solution Name: SSIS_Demo; >> Ok
Each Project can hold multiple packages

On right hand side you can see SSIS_Demo >> Right Click >> Convert to Package Deployment Model >> Ok >> Ok (This is what we see in BIDS which is old model and we cannot see connection managers). Again right click on SSIS_Demo >> Convert to Project Deployment Model >> Next…and so on>> Finally Close (This will be used in multiple packages) >> Right click on SSIS Packages and create 2 new SSIS packages by selecting New SSIS package >>Rename Pkg_Load_Customer.dtsx >> Rename Pkg_Create_Customerfile.dtsx >> Save
We can see these packages in Location where you saved earlier.

Control Flow: Controls the flow of each task in sequence. Data Flow Task – Fetch data from csv files; Execute SQL Task: Perform sql operations like TRUNCATE
Data Flow: We have Source and Destination information. OLE DB Source read data from SQL Server >> Click on New and select Server Name and DB Name >> Test Connection >> Ok >> Data Access Mode: SQL Command SELECT FNAME, LNAME FROM dbo.emp;
Parameters:
Event Handler: Error handler
Package Explorer:

Fetch data from table to csv file:
Data Flow: OLE DB Source read data from SQL Server >> Click on New and select Server Name and DB Name >> Test Connection >> Ok >> Data Access Mode: SQL Command – Write some SELECT query – SELECT FNAME, LNAME FROM dbo.emp; Click Ok
Next drag Flat File Destination >> Connect OLE DB Source to Flat File Destination >> Double click Flat File Destination >> Delimited >> Give some connection manager name – Conn_Emp >> Browse to create file >> Next …. >> Ok >> Save >> Start

Fetch data from tab delimited file to table:
Create tab delimited file and also table structure in DB
File >> New >> Project >> Right hand side you see SSIS Packages, right click on it >> New SSIS Package >> Rename LoadTabDelimiterToSQLTable.dtsx >> Data Flow >> Other Sources: Drag n drop ‘Flat File Source’ >> Now create a new connection by clicking on red x icon >> New >> 
Data Flow: OLE DB Source read data from SQL Server >> Click on New and select Server Name and DB Name >> Test Connection >> Ok >> Data Access Mode: SQL Command – Write some SELECT query – SELECT FNAME, LNAME FROM dbo.emp; Click Ok
Next drag Flat File Destination >> Connect OLE DB Source to Flat File Destination >> Double click Flat File Destination >> Delimited >> Give some connection manager name – Conn_Emp >> Browse to create file >> Next …. >> Ok >> Save >> Start