Checking for File Existence using script task in SSIS

ETL is nothing but its extract the data from different sources, transform (apply business logic) and load it into the destination. The source may be files or other systems.

Read about ETL here

http://bageshkumarbagi-msbi.blogspot.com/2014/12/microsoft-business-intelligence.html

When we are loading the data from the files, before loading we need to make sure that those files are present in the source folder. We are getting files from different sources. Sometimes we are not getting files on the source folder when our package starts execution. May be due to some reason like network issue or source system down etc. in this case our package will fail. To avoid loading the files we need to check the existence of the file.

See the demo

In this dome, we have a source folder where we are getting daily CSV files. Our package will load these files into the database. If there is no file present in the source folder we will send the mail to the respective team saying that we didn’t receive the files please resend the files.

 

This is the source folder. We need to load this file to the database.

 

Now I am taking script task

Taking some variables.

In script task I am writing the code to check the file existence. If file present then it will return true if not then it will return false.

DirectoryInfo di = new DirectoryInfo(Dts.Variables["User::filepath"].Value.ToString());             FileInfo[] files = di.GetFiles("*.csv");             foreach (var fi in files)             {                 if(fi.Exists)                 {                     Dts.Variables["User::flag"].Value = true;                 }             }

Here I am checking the csv files exist in the folder. If file exist then we are setting true else false.

Now I am taking one Foreach loop container and once script task to send the mail.

In precedence Constraints, I am checking the flag

Read more about Precedence Constraints in SSIS:

http://bageshkumarbagi-msbi.blogspot.com/2016/05/precedence-constraints-in-ssis.html

Foreach loop container

Script task

Now In the Foreach loop container, I am taking Data flow task to load the files.

 

Now I am receiving the file name into the variable.

 

Now I am taking the source as Flat file and derive column to add some columns and finally, I am loading it into the table.

 

Now I am taking the script task for sending the mail.

Blow is the script to send mail.

string smtpAddress = "smtp.gmail.com";             int portNumber = 587;             bool enableSSL = true;             string emailFromAddress = Dts.Variables["User::Email_id_sender"].Value.ToString(); //Sender Email Address             string password = Dts.Variables["User::Mail_credentials"].Value.ToString(); ; //Sender Password             string emailToAddress = Dts.Variables["User::Email_id_reciver"].Value.ToString(); //Receiver Email Address             string subject = "******* File not exist ! **************";             string body = "Hello Team,";                         using (MailMessage mail = new MailMessage())             {                 mail.From = new MailAddress(emailFromAddress);                 mail.To.Add(emailToAddress);                 mail.Subject = subject;                 mail.Body = body;                 mail.IsBodyHtml = true;                 mail.Body = "Hello Team,"+ Environment.NewLine+ "Files does not Exist for Today : " + DateTime.Now.ToShortDateString();                               using (SmtpClient smtp = new SmtpClient(smtpAddress, portNumber))                 {                     smtp.Credentials = new NetworkCredential(emailFromAddress, password);                     smtp.EnableSsl = enableSSL;                     smtp.Send(mail);                 }             }

Now our package is ready.

Now I am executing the package.

Case 1

File Exist in the source folder

Before running the package records in the database.

Now running this package.

Records in the package.

Both files has been loaded.

Case 2

File doesn’t exist in the source folder.

Now running this package.

See in the mail inbox

Mail body

Hope this will help for such type of scenario.