In this video, we'll talk about one of the important and useful tasks in SSIS called script task. Now, script task is something in which we will write some C sharp code and do something which is not possible using any of the other tasks. For example, let's say we want to do some file operations, we want to do, we want to execute some custom logic, which is not possible using any of the other tasks. So in that case, we will go and write complete C sharp code from the scratch and execute it with the help of script TAs. So as you understood, in order to work with script task, you must have a little bit knowledge about sharp programming. So I recommend if you don't have any idea about C sharp programming, then please go and watch.

Learn dotnet in 60 day series from correspond v.com Get a basic idea about shisha programming at least. Now before we get into the script task, first let's have a look at the send mail task. And let's understand couple of things about it. So let's drag the send mail task from toolbox to designer window. And let's double click this to configure it. send mail task, the name itself says what does it do?

It will let us send mail to a particular email id. Now in order to configure this task, let's get to mail section. And here we will get couple of options to configure. We can configure the attachments we can specify one or more files which we want to send as an attachment. We can see the put the message source here the message which we want to send subject BCC cc to and from all our into do and the most important is we have to set SMTP connection. Right now.

We don't have any idea Simply precondition created. So let's simply click cancel and let's go and create that right click this Connection Manager section say new connection and search for SMTP here it is simply say add. Now SMTP Connection Manager will let us configure SMTP server for that we will put address for SMTP server that is smtp.questpond.com. Now, this is just a dummy address, it will not work really, but for demo purpose we are using it. Now we are done with specifying the address of our SMTP server. Next we have to understand the concept of authentication.

Now, authentication means validating user's identity or in simple words confirming whether a user is valid user or not. And in case of SSI is user means the one who is executing the package. Now, the SMTP server, which we are going to use to send mail may be configured with some authentication or may not be configured with some authentication. Now the situation where or SMTP server is not configured with any authentication. In that case, it's going to be very easy. Simply say, okay, because we are done with it.

But it won't really happen. Because just imagine if SMTP server is not configured with any authentication, in that case, any user can access that server and send emails Why does it really make sense? No. SMTP server is always configured with some kind of authentication. But again, there are two situations here. Situation number one, our SMTP server is configured with Windows Authentication.

In this case, our SMTP server expects some credentials credentials means us Name and Password. And after that SMTP server will validate those credentials against the credentials stored in current active directory. If credentials are valid credentials, their SMTP server will allow to send email, but credentials are not valid, then it will not allow to send any email. So if this is the case, then it's easy because we have to simply check this checkbox. When we check this checkbox and when we execute the package, or SMTP, task will simply send the credentials of the current logged in user to the SMTP server. And if the credentials are allowed to send mail, then mail will be sent.

If the credentials are not allowed to send email, and then the email will not be sent. It's all up to the Active Directory settings we are not required to worry about anything. Now let's talk about second situation. Now the second situation is or smtps server is configured with some custom authentication. it expects some kind of username and password, which it will validate against some custom logic. In that case, this SMTP Connection Manager will not work because here we don't have an option to specify custom username and password.

Either we will we can send current login username and credentials by checking this checkbox or we will not send anything will not have an option to specify custom username and password. So if this is the requirement, we cannot think about using SMTP Connection Manager and we cannot think about using send mail task. So the final conclusion is when SMTP server is configured with no authentication, then SMTP Connection Manager and send mail task is the ultimate option. When SMTP server is configured with use Windows Authentication, again SMTP Connection Manager and send mail task is a option. But when SMTP server is configured with custom authentication, where it needs some kind of custom username and password, we cannot think about SMTP Connection Manager and send mail task for sending emails. But this is not the only limitation of using send mail task and SMTP Connection Manager there is one more limitation and that is port number.

Here we don't have an explicit option to specify port number. Again, if that is the requirement, then again SMTP Connection Manager is not the best option. Are we done? No, there is third limitation. Third limitation is with our sendmail task. Here.

We will have an option to send an email, email as a plain text message. We don't have an option here to send email as an HTML email. A formatted email. Normally, when we send email, we get two options one, send it as a plain text second, send it as a custom HTML message where we get an option to format it as bold or some color, etc, etc. We don't have that option here. So in simple words, guys, if you believe that you want to send email, which is going to be formatted, you cannot think about send mail task and SMTP Connection Manager.

And if you believe that, you are going to send a pure plain text message via an SMTP Connection Manager, which is configured with either no authentication or which is configured with Windows Authentication, then only you can think about send mail task and SMTP Connection Manager. For other scenarios. These options are not proper, not well. So for that, we will use script task because Script task we can write or custom C sharp code and we can do whatever we want. So now, let's see using C sharp, and using script tags how we can achieve the same. Let's close this dialog box.

And let's delete send mail task and SMTP Connection Manager from the screen. Now let's drag script task from toolbox to exercise designer. Double click it, click on Edit script, it will open up a new visual studio where we can go and write simple C sharp code. So here it is. And this is the place where we can write our code. Again, if you don't have idea about C sharp programming, then please go and watch learn dotnet in 60 days and get basic idea about C sharp programming.

Now to start with our email code, we have to first import our namespace that is using system dot sorry, you have to say using system.net dot Mail. And then we get access to a special class called as SMTP client. Using this class, we will send email in this class via to configure a couple of properties. The first property is host here we will specify the address of our server email server. Now for this example, we are going to use Gmail server for sending mail Gmail have given a provision where we can specify his server and send a mail. So that is going to be smtp.gmail.com.

After that, we have to set the port number that is 587. And finally, we have to set enable SSL. So it means Gmail is going to use secure server. So this is going to be step number one, where we will configure our smtp. So we are done with step number one. Now let's move forward Now the next step will be providing credentials to this SMTP client object, we have to provide credentials using which we will connect to this server.

And that credential is nothing but our Gmail credentials. So, for demo purpose, I have created a dummy username and password that is semi colon, semi colon and string password is equal to this one. This is a dummy credentials created by me for testing purpose, Now, next step is creating instance of a class called as network credential. So I will write down step to provide credential. So let's do that. I have to create an instance of class called as network credential but as you can see, nothing is coming IntelliSense because in order to use network credential class, we have to put a using statement called using system.net, semi colon.

And let's come down. And here now let's see, yeah, there is a network credential class. So let's create the instance of this class, I will say credential equal to new network credential, and it asks for username and password. So this is going to be the username. This is going to be password. Now instead of putting this username and password, I recommend you to put your own username and password.

Next, as I said, we have to provide this credential to this client object. So I will say client to dot credentials equal to this credential. We are done with step two as well. Step number three, configure from address so for that, we will say Step three, configure from Here I will say, create an instance of mail address. wrong address equal to new mail address and we have to provide email ID that is I will say that we keep it has it is, this is going to be the email ID from where I will send my message name, I will put it as sukesh Marla done with step number three. Now, step number four is nothing but configuring message which needs to be sent.

So, let's create step number three. So I'll say step number four, create message to be sent. So here I have this simple source code. It's a very simple source code. First thing is I have to create an instance of mail message here it needs from address that is nothing but our from address. Then we have to add one or more two addresses.

So here I have created one sample demo demo Purpose email ID that is test for SSIS added@gmail.com. subject is SSI as mail and ease body HTML is set to true that means whatever message we will send, it will be considered as HTML. And as you can see here, body is actually an HTML. If you don't know HTML, then it simply Hello from SSI is going to be displayed in red color with green background and with bold formatting. So this is the message we want to send. Now the final step is send the message so for that, I will say Step five, send email.

And this is done. This will be done by simply invoking client dot send message. Next, let's go and test the code. So let's save the code. close this dialog box and As you can see, we have test for exercise email open here with no messages in the inbox. Let's go to our script task, say OK. And then let's go and execute the package.

It's executing hope so it will. Yeah, it's done. Now let's go back to our Chrome. Yeah, as you can see, mail is here. Hello from exercise with red for four color, green bag color, and it is bold. Let's delete it.

Now inbox is empty again. Let's go back to our package. Let's execute the package again. Package execution is executing. And it's complete. Let's go to our inbox.

And soon you will get the message Yeah, here it is. So you can see Hello, promises is we got it again. So this is all about using script tasks in SSIS. Hope you enjoyed it. If you have any query