SSIS Practise #Assignment 2 #Multicast Transformation #Conditional Split #Excel Scenarios

SSIS Practise #Assignment 2 #Multicast Transformation #Conditional Split #Excel Scenarios In this video, we have discussed about various transformations along with the one that we have covered in our last videos. Multicast Transformation in SSIS. It sends input data to multiple destination paths without applying any sort of conditions or transformations. OR, Takes ONE Input and makes the logical COPY of data and passes the same data to multiple outputs. Sequence Container in SSIS - makes it simple to divide the control flow in a package into groups of tasks and containers that you can manage as a unit. Expression in SSIS: In Integration Services, expressions can be used to define conditions for CASE statements, create and update values in data columns, assign values to variables, update or populate properties at run time, define constraints in precedence constraints, and provide the expressions used by the For Loop container. Assignment 3 (solution will be present in next video): Create Database Assignment; Use Assignment; Create Table [Operation_Survey] ( survey_id int primary key, descr varchar(255), industry varchar(50), level int, size varchar(50), line_code varchar(50), value int ); create Table [Audit] ( id int identity(1,1), execution_id varchar(50) not null, package_name varchar(100) not null, start_time datetime not null, end_time datetime not null, src_count int , dest_count int , status varchar(20) not null ); Task 1: Load data from CSV to Excel in such a way that your Excel data wont allow multi-entries, if the package is run multi times. Hint: Use File System Task and copy a sample excel file from one place to ur source folder everytime, before running the load task. Task 2: Load data from Excel to SQL Server (OLE DB Connection) in such a way that it will restrict null and duplicate values to flow into SQL Server Table [Operation_Survey]. Hint: Use Excute SQL Task to delete data from SQL Server Table, SORT transformation to remove duplicate entries in Data Flow Task, and use Conditional Split to allow only not null values to pass though it. Task 3: Use a sequence container and drag ur tasks so created in Task 1 and Task 2, and do manual auditing checking the status of the task. Hint: Use [Audit] as a auditing table. Create Variables execution_id int, src_count int, dest_count int, execution_end_date datetime (expression: getdate()),execution_strt_date datetime (expression: getdate()) Drag row count transformation in Data flow task 1 and map src_count variable there, and Drag row count transformation in Data flow task 2 (before destination ie OLE DB) and map dest_count variable there. Use 'select Coalesce(max(execution_id)+1,1) from dbo.audit' to load (max id + 1) from audit table in execute SQL Task and place it above sequence container and ouput as single row and map variable (execution_id 0) Drag one more Execute SQL Task, after first excecute sql task above sequence container and go to expressions:sql satatemet source "Insert into dbo.[Audit] (execution_id, package_name, start_time, end_time, src_count, dest_count, status) values ("+ (DT_WSTR, 5) @[User::Execution_Id]+","+@[System::PackageName]+" , CAST('"+(DT_WSTR, 30) @[User::execution_strt_date] +"' AS DATETIME) ,'','','', 'Started')" Drag two execute sql task at the end of sequence container - one with precendence constraint (line connecting sequence container and task) as success and another one for failure. For success: "UPDATE dbo.Audit SET end_time = CAST('"+(DT_WSTR, 30) @[User::execution_end_date] +"' AS DATETIME),src_count = "+ (DT_WSTR, 10) @[User::src_count] +", dest_count = "+ (DT_WSTR, 10) @[User::dest_count] +",status = 'Completed' where execution_id ="+ (DT_WSTR, 10) @[User::Execution_Id] For failure:"UPDATE dbo.Audit SET end_time = CAST('"+(DT_WSTR, 30) @[User::execution_end_date] +"' AS DATETIME),status = 'Failed' where execution_id ="+ (DT_WSTR, 10) @[User::Execution_Id] Task 4: Load the values of not null values using multicast transform in data flow task, such that one of its output loads oledb destination and another output goes to flat file destination (create new flat file - text file with comma delimiter). 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.