SSIS package #Assignment 1 #SORT #Conditional Split #Row Count #Exec SQL Task

SSIS package using #SORT #Conditional Split #Row Count #Exec SQL Task.

Here, we have described how to fetch required records from excel in such a way that it wont violate primary key constraint.

SORT Transformation: It is used to remove duplicate data.

Conditional Split: Here we can define our condition, the condition in our case is not null data and the same we have defined in video. We can define n number of conditions here.

Row Count: It is used to count the number of records passed from it

Assignment 2 (solution to be present in next video)
Task 1: Create a package to load data from the attached CSV file to your Excel destination.
Task 2: Execute the below script in SQL Server:
Create Database Assignment;
Use Assignment;
Create Table Employee(
Id integer primary key,
Company_Name varchar(97),
Contact_Person varchar(22),
Residence varchar(21),
Department varchar(30),
Efficiency decimal
);
Create a package to load data from excel source (taking the same excel as excel that we created in task 1), and try to load in above SQL table via OLEDB Connection Manager.

Task 3: As you might have seen that we have a primary key in our SQL table, so it cant accept duplicate records.
To remove this, do have a 'execute sql task' before your 'data flow task' to delete records from SQL table.
Also, have 2 'row count transformations' in 'Data flow task' that will tel us how many records flown and actually inserted in destination.
With that, have a 'sort transformation' in data flow task to remove duplicate values flowing from excel to destination.
Hint for Task: Data Flow Task: (Red marks should not be visible. Also create variables to store values of row count and row count
with data type Int32)
Control Flow: (SQL Direct query: Delete from Assignment.dbo.employee)

Feel free to ask questions or suggestions related to the video in the comment section and we will provide you the assurance to reply for your queries soon.