SSIS
SSIS Assignment 4: CSV to SQL #Hadling Null #Duplicate Records #Archive #File System Task #For Each Loop #Aggregate Transformation
(In Case Assignment database is not created): 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
);
CREATE TABLE [Operation_bad_data](
[survey_id] [varchar](50) NULL,
[descr] [varchar](255) NULL,
[industry] [varchar](50) NULL,
[level] [varchar](50) NULL,
[size] [varchar](50) NULL,
[line_code] [varchar](50) NULL,
[value] [varchar](50) NULL
);
Task 1: Load data (Unique records) from CSV to SQL Server Table [Operation_Survey], with primary key constraint.
Load Null and Duplicate records in another table [Operation_bad_data] for further analysis of that data.
Task 2: Try the above logic with multiple sources (Flat file Files with same metadata), using For Each Loop.
Task 3: Archieving the flat files, once data is loaded to the tables.
Task 4: Use a sequence container and drag ur tasks so created in Task 1 to Task 3, 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())
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]
Note: If anyone is interested to try for the scenario, they can share their mail id in the comment section. Also one can post their doubt in the comment section.
In questa pagina del sito puoi guardare il video online SSIS della durata di ore minuti seconda in buona qualità , che l'utente ha caricato b2aLearning 19 luglio 2019, condividi il link con amici e conoscenti, su youtube questo video è già stato visto 283 volte e gli è piaciuto 10 spettatori. Buona visione!
