In this video, we will talk about execute SQL task. As the name implies, this task will let us execute SQL queries. For this demo, we have a simple database created with us called SSIS demo with a table called TBL employee. This TBL employee contains three columns, employee ID, employee name and address out of which employee ID is an integer column, and it's an identity column. This TBL employee table is currently completely empty. Now, to make this training, or to make this video more easy to understand, what I did is I simply broke this video into multiple laps.

So first lap will be a simple lab where we will insert a record to this table using execute SQL task. Back to Visual Studio and in Visual Studio, we will start with creating Connection Manager first. So rightly you add dotnet Connection Manager, you can remove the existing data connections if you want create a new one, server name is going to be.in my case database name is SSIS demo say OK, OK done. Now next step is go to exercise toolbox simply drag execute SQL task from exercise toolbox to SSIS designer, double click it and then in connection type select a do dotnet connection is this one local host dot SSIS demo one because this is the one which we created just now. And we are done with step number one. Next will be writing query.

Now either we can directly go and write query here or we can take query from some text file for that we have to take this file connection and we have to connect we have to create a connection manager file Connection Manager which will be connecting to that text file. Or we can put our query in some variable and we can use that variable here as well. But for this demo, we will use direct input. And let's directly go and write the query. So query will be insert into TBL. Employee because our table name is TBL.

Employee, ID is TBL. employee. So let's say insert into TBL. employee, and column name is employee name and employee address. So I'll say employee name, comma, employee address, enter values. In bracket I will say a comma or let's let's put some proper name I will say sukesh comma, I will say Mumbai and then I will say okay then after that say okay, and we are done with it.

Now let's go and execute this package as executed. So package x fusion hope completes. Yeah, it's completed the execution. Now let's go to our SQL Server. Let's refresh this table. And as you can see, one more row get inserted.

Okay, and Mumbai. Let's try it again. Let's go and re execute the package. Package engine completes, let's go to SQL Server. Let's refresh it. Now we have two rows.

So case Mumbai, Mumbai. So we are done with our lab number one that is executing simple queries simple insert queries using execute SQL task. Now let's remove this one row from here. And let's move towards our lab number two. In lab number two, what we will do is we will make this insertion dynamic right now each time we execute the package, we are getting the same record sukesh Mumbai sukesh Mumbai in next lab we will make it dynamic values. So for that, we will go to our Visual Studio Stop the execution, double click the package and then instead of writing query straight forward here like insert into TBL employee employee name employee address location Mumbai what we will do is we will say accurate employee name and I will say at the rate employee address and I will say okay now our queries parameterised query.

Now the next point is we have to provide values to these parameters. So, for that we will say ok and we will say go to parameters and here we will create two new parameters I will say one as a p underscore employee name, this is going to be string I will say this is going to be string, default value is default name I will create one more very variable p underscore employee address, this is again going to be string and here default value will be default address done. Now next, we will map this parameters to our SQL parameters. Remember guys don't get confused here we have two kinds of parameters one is a set size parameters p underscore employee name and p underscore employee address and in our query whatever parameter we have specified, that is SQL parameters. Technically SSIS parameters will get its values at the time of execution.

And we will create a mapping between SSIS parameters and this SQL parameter because of that, at the time of execution, these SQL parameters will get replaced with the values which we provide to SSIS parameters automatically. And at the end of the day, or insert query will become dynamic. So let's do it. So let's say okay, and then let's get to this parameter mapping section. And here we have to say add. Now, the variable name the variable name is p underscore employee address, which we believe should belongs to at the rate, employee address.

Now this is our SQL parameter name, and this is our variable name or exercise parameter name, we need one more p underscore name, which will belong which will go to at the rate employee name. We also have to change the data type that is string. And we are to say, string. Let's say Okay, and let's execute this package. Execution done let's go to our SQL Server. Let's say refresh.

We got this default name and default address values you but this is not the one we are expecting right here. In this way, every time we execute the package, same default name and default address will get inserted. Now, the answer for this question is, actually it's not, because in real time scenario, once we are done with the development of the package, when it comes to execution, we will not use Visual Studio for execution. Instead of that we will deploy or a package and then we excute that deployed package. We already have a detailed video on deployment and execution. So in this video, I will not explain you the concept of deployment and anything, I will directly go and deploy my package in this integration service catalog.

Back to the Visual Studio, right click, open containing folder. Open bin folder development folder, double click the execute SQL task. Next, Next, browse This is our local server. say okay, browse. We already have one Simple folder. Let's do the same one.

I will say Okay, next deploy. It will do the deployment of our package. Close. Let's go to SQL Server. Open it, refresh it. Let's open this simple folder.

Expand project. Here we have our integration service project, right click, or sorry, open packages. And here we have our package that is packaged or DTS x, right click, say execute. And you will see that here we are getting an option to change the value of these two parameters. So I will say, name one. Okay.

I will say name two. I will say okay, let's execute it. Okay. Like it is executing. No. Let's refresh the table.

New records are here. We can do it again. Right click the execute this time. We'll say name to. Okay. address to Okay, okay, no.

Refresh. Both the records are here. It was our second lab. Now in third lab, we will learn how to execute stored procedures using execute SQL task. Now for this demo in our SSIS demo database, we have a sample stored procedure created with the name, insert employ. If you check the stored procedure, what it will do is it will simply do the same insert task.

So, next lab will use this procedure and we will insert new records to our table. So let me remove all these three records from here. Yes, and let's get back to SQL and double click this execute SQL task and after that, let's do the changes first said this is Query Store for zero to true second step change SQL statement to procedure name that is sp underscore insert employee say OK, key parameter mapping has it is because in our procedure we have created parameters with same name that is employee name and sorry EMP name and EMP address, say okay, let's execute the package. It's done. Let's go to our SQL Server. Now we should get default employee name and if not default, the address here.

Let's refresh the table. Here it is, and we are done with our lab number three. In lab number four, we will learn how to execute select queries using execute SQL task. For this lab, we have a sample database created for us called as SSIS target. Now, lab agenda is very simple. We will fire select query on our existing TBL employee table and our For that, we will use for each task and loop over the record set and insert each row one by one into this TBL target table.

The structure of TBL employee, target and TBL employee is almost same. It contains three columns ID, name and address in our current backup. For this, we will create completely a new package that stop the execution Solution Explorer, right click New SSIS package. And let's rename this package to execute. Select query something like this, just to understand and we'll start with Connection Manager. This time we will need two Connection Manager one for our source database and one for our destination database.

Right click, say new ad dotnet connection we have our existing connection Hear which is connecting to exercise demo, if you want you can directly go and use it. I will say one more a dotnet connection. I will say new server name is dot, but database name is going to be SSIS. Target Test Connection. Yes. Okay.

Okay, done. Now we'll start with execute SQL task. Let me drag it here, double click it. And as usual, we'll start with connection type to a do dotnet connection to exercise demo one. This time let's take a query from file. So let's go to our for execute SQL task folder and let's create a text file.

I will say new text document SQL query. And let's write down a simple query inside it. Select employee ID comma employee name comma employee address from TBL. employee table. Let's close this say yes. Let's go back to our Visual Studio.

Let's set this SQL source type to file connection. But right now we don't have a connection manager which is connecting to that file. So simply say new connection, say browse. And let's go and copy the path or txt file, say open. Let me copy this SQL query, say OK, and after that next thing is parameter mapping. Right now, our query won't contain any parameters.

So this section is completely useless for us. We'll get into this result set, but the result is completely disabled. Now the reason for that is we have to do one more setting before we come into this little subsection that is go to general and here we have an option called as reserved So, right now it is set to none. Now when we set it to none, that means our query won't return anything, which is the good option when we are executing, insert or upgrade kind of a query, but we were but when we execute select query, we should be changed to either single row full result set or XML. When our query returns and XML value, we should set it to XML. When our query returns only one row, single row is good option on when we when our query return, a complete result set, which contain more than one row, which is our case, we should set it to full result set.

Let's come back to result set. And this time it is enabled, say add. Let's keep result name as my result. And that take variable name. Right now we don't have any variables defined, we need variable which is going to hold the entire record set, which will be returned because of select query. So we'll see variable, I will name this variable as employee data and value type.

Now this is important. Now the type of the value is going to be object here, because we are going to get more than one records here. It is not just an ordinary record like string record or integer record. It's an entire record set it is going to contain more than one row. Each row consists of more than one values. So we need a complex data type that is object.

So here it is the object and say, OK, say OK again, and it completes configuring execute SQL task. Now before we proceed, let's confirm that at least whatever we did so far is correct. So let's go and execute the package. And there is an X symbol it says execution failed. Let's go to progress tab and check the error. Error says the result binding name must be set to zero for full results at an XML visit.

What does this mean? Let's go back to our control flow. And let's double click this execute SQL task again. Now, when we said result set to full result set or when we set it to XML in that case, it is very very important that result set name is zero, you cannot set anything else here It must be zero and we cannot have more than one rows also we can have one only one record here and that is going to be zero. Now, you may be wondering if this is the case, if we always need to set result name to zero and we cannot have more than one rows, then why this result name and add and remove buttons are given here. Now answer for this question is, these options are useful when we set result set as single row.

Single row means we will get only one row because of our query and that row may contain more than one columns. So, in that case, what we will do is we will define more than one column, we will say from our query, employee ID will go to this variable, employee name will goes to this variable. And finally, we will say employee address will goes to this variable. But in our case, our query returns more than one rows. So we cannot have this option. We have to say remove remove and we have to set it to zero.

These options will be useful when we say we are going to get single row in response we will set it to zero again, we will go to general and we will set it to full result set again whenever we get full result set or XML as response. We or we can have result name as zero only. Let's say okay, and we are done with execute SQL task configuring. Let's execute the package once again. And this time package execution completed Next we need for each task container. So let's go to or sorry, before that let's stop the execution.

And it's good to exercise toolbox. And let's drag this for each to container. And here we have to double click this container, go to collection. And here we have to select an option that is for each ad o and numerator. Don't get confused between for each ad o and numerator and for each ad or dotnet schema rousset enumerator, this this ad rotten schema router numerator will be used when you want to get the detail about schema for example, we want to find out how many tables are there or what all tables are there in a particular database. So in that case, we will use this option but right now we want to loop over an object which contains individual rows of our table.

That is we want to loop over all data set. So in that case, we will take this option for each video enumerator and after that, right now we have only one table so we can take one How to take this option rows in first table. And then our variable name is employee data. So let's take that. Next, let's go to variable mapping here we will create three variables sorry you have three variables one variable is integer variable, I will name this variable as employee ID and or let me make it as SSI as employee ID. This is going to be an integer variable and I will say default value is going to be zero say Okay, one more variable, I will say new variable, this is going to be SSIS employee name and this is going to be string variable and third variable is employee address.

So I will name it SSIS employee address. This is again going to be a string variable. column number one will go here column number two will go here column Number three we'll go here always remember, column index we'll start with 00 means first column one means second column and two means third column. So here we have defined which variable contain which values say okay and next we will say insert each of these records into a database for that we will need one more execute SQL task but this time this task will be placed inside for each loop container. Now before we go and configure this execute SQL task, let's do one thing, right click and say add variables and we will create a variable called insert query and we will make this a string variable and we will put our insert query here insert into TBL, employee target employee ID comma employee name comma employee address and values will be added at employee ID added employee name and add the rate employee address.

Remember, these three variables are SQL parameters, they are not SSIS variables they are SQL parameters. So now it's time to configure this execute SQL task. So simply double click it and select connection type as video dotnet connection to SSIS target, which is our second database. And this time for SQL source type we will take variable option and source variable will be nothing but the newly created variable that is SSR you sorry insert query variable that after that just like before, let's go to parameter mapping and here we have to say add add add trice and we will say this will come from added exercise employee ID and this will come from added exercise employee name and add the date SSIS employee address and parameter name will be emp id this time EMP name will be here and finally, EMP address will be here again repeating the same point.

Now these are our SQL parameter names and these are our SSIS variables. I will say Ok, done now let's execute it and let's see the output press ephi but unfortunately for each loop container execution failed let's go to progress tab and let's check the error error says variable employee data does not contain a valid data object. I suggest Pause the video and try to find the reason for this error by your own. Now, the reason for this error is execute SQL task executes and successfully completes and when for each loop container try to execute it fail. Why? Because foreach loop container executes before execute SQL task completes is executed And because of that, when for each loop container, try to access employee data variable, it's empty.

So what we want to do is, we want to make sure that for each loop container executes only after execute SQL task completes its execution. And that is possible with the help of precedence constraints. So let me stop the execution. And let me connect this execute SQL task to foreach loop container. Press f5 execution failed, let's go to progress tab and let's check the error. Copy this error.

Let's open a notepad. No the notepad space, their error says insert query fail because of this error fail to convert string to int 32. That means somewhere we are trying to convert string to integer and that conversion is getting failed. So let's go to our control flow. And you can see here that execute SQL task get field because execute SQL task get failed for each loop container task get failed. So the actual reason is this execute SQL task something is there in the execute SQL task which is causing this failure.

So let's stop the execution and double click this execute SQL task. Let's go to parameter mapping. As you can see, here, we are, we have created three parameters here each of them are integer actual, actually, it is wrong because employee name and employee address should be string so that make it string. And let's say okay, now before we go and execute this, let's go and check the error again error says insert query look at the insert query is that where it says insert into TBL? employee targer. There is a spelling mistake it should be target.

So let's go and correct it. So for that, we will say right click variables and we will say Get now everything is correct. See, the reason why I showed you this example is because many times when we do our execution in SSIS, we end up with such errors. And instead of getting confused, just go to your progress tab and let's check the age just check the error, most probably you will be able to find the error. Now everything is done. So let's go and execute the package once again.

Now everything is done. Let's go to SQL Server. Let's open this TBL target table, edit top 200 rows values are here. Let's remove everything. And let's execute the package once again. It's done.

Let's go to SQL Server. Let's refresh it. It's there. Now when you try this example, I want you to include two more requirements in it. requirement number one, make sure to put one more execute SQL task between these executed task and this container and this intermediate execute SQL task will truncate this employee target table completely. requirement number two, that intermediate execute SQL task and this for each loop container should execute in transaction.

We already have a detailed video on transaction, I want you to follow that video and complete this example by your own. Now, before we end this video, we have one more discussion to complete. Whatever we did so far, is just for the demonstration purpose, I just wanted to make you familiar and comfortable with execute SQL task and execute SQL tasks and for each loop container together in combination, and I believe now you got a pretty nice idea about both of these tasks and how they will how both of them will work together. In real time scenario, we will Never use execute SQL tasks. And for each loop container to perform ETL tasks like we did right now, when it comes to ETL tasks, we always have an easier way to do it that is Dataflow tasks. But definitely in real time scenario is a good SQL task and foreach loop container, work in combination a lot.

Let's talk about a scenario. Let's say we have a table, which store employee information. each employee contains email id. Now we want to loop over those employees and send an email to each employee saying some kind of message like leaves are applied or something like that, whatever. So in that case, data flow task will not work. We have to use execute SQL task and for each loop container in combination.

Let's talk about a second scenario. Let's say we have a table which contains a set of records. I mean to say more than one rows Now we want to loop over each row and then we want to pass value of each row one by one to a web service and get the result back and store it somewhere. And again, this is not possible with the help of simple data flow task now. So in this case, we'll again use the execute SQL task and for each loop container in combination. So, let me conclude for ETL tasks definitely, you should not should not use execute SQL task can also container for that get into data flow task there we have plenty of options to do it, we can even get option to write query.

But when it comes to custom things, where we want to look for the record set and do something different, in that case, definitely execute SQL task and for each loop container will be used in combination so that About execute SQL task. Don't forget to complete the assignment which I asked you to do. If you found any difficulty in completing the practical or assignment, please please write to us and we will always try to help you help you in the best possible way. We can