In this article we will talk about one more important component of SSIS called as “Execute SQL Task“.
Links to other Labs of Learn MSBI Step by Step.
Learn MSBI Part 1:- Creating SQL tables, Practicals of SSIS-ETL(Extraction, Transformation & Loading)
Learn MSBI Part 2:- Performing Validation on Records
Learn MSBI Part 3:- SCD (Slow Changing Data)
Learn MSBI Part 4:- Fact and Dimension.
Learn MSBI Part 5:- CUBE Creation
Learn MSBI Part 6:- Displaying reports using SSRS
Learn MSBI Part 7:- DQS (Data Quality Services)
Learn MSBI Part 8:- Data Profiling Task
Learn MSBI Part 9:- Pivot, UnPivot and Aggregation
Learn MSBI Part 11:- Send Mail Task & Script Task Component
Learn MSBI Part 12:- CDC Concept using SSIS
Learn MSBI Part 13:- Asynchronous, Synchronous, Full-blocking, Semi-blocking & No-blocking
Learn MSBI Part 14:- Understanding Page Split and maintaining SSIS Performance
Learn MSBI Part 15:- Debugging in deployment – Instrumentation using Data Taps
Learn MSBI Part 16:- Using Multi-threading and its performance in SSIS program
This article will talk about one more important component of SSIS called as “Execute SQL Task“. This article will show working of “Execute SQL Task“ component with example.
So without getting it into much talk let’s start practical demonstration of the same.
For that first go to SQL Server Management Studio and create a new table.
Let’s name the table and call it as “TblStudents” with following column fields mentioned in it.
And finally save the table.
Now go to Visual Studio, add new package to SSIS project and then on the Control Flow drag component “Execute SQL Task“ from SSIS Toolbox.
Then double click the component or do right click and click Edit option on component to configure or Edit it.
Once the Editor window is open then click on the General tab and on this tab do the following settings: –
Set ConnectionType –> “ADO.NET” from the dropdown.
Set Connection, on this field click on "< New connection… >" and configure the ADO.NET connection manager.
Once clicked on “< New connection…>” it will open configuration window, select if available server name under Data connections or click on “New” to get server name and database name(It will open separate window to select it) and then click on OK to close the configuration window.
Next set “SQLSourceType” à Direct input.
Then set the SQL statement once clicked on that field it will open small window to enter SQL Query as shown in the image below.
Write following query statement in that window
insert into TblStudents(EmpID,EmpName,EmpAddress)
then click on OK to close the “Enter SQL Query” window and then click on OK to close Editor window.
Once “Execute SQL Task“ is configured as the red marked has gone off. Next is click on “Start” and execute the package which turns “Execute SQL Task“ components with green mark once it is successfully executed as shown in the following image.
After package is successfully executed go to SQL Server Management Studio, and view the table content is successfully loaded.
After getting to see the result loaded in the table, this lab is successful accomplished.
Dynamic insertion of values into SQL table
So with dynamic insertion values won’t be static entered or hard coded in to the SQL Query instead it would use parameter. In other words there won’t value entered manually on “Enter SQL Query” window in place of that (@StudentName, @StudentAddress) parameter will be done.
For that Edit the component “Execute SQL Task” by doing right click on it and click on General tab then select SQL Statement field which will open “Enter SQL Query” window Edit present existing value to parameter value by writing @StudentName, @StudentAddress and click OK to close the window.
Next go to Parameters, and click on Add new Parameters and add following values to the field.
Next mapping of above created parameters need to be done for that open “Execute SQL Task” Editor and then click on “Parameter Mapping” tab. Under this tab create new mappings by clicking on “Add” one for the Student Name and other for Student Address as following: –
Student Name –> @StudentName
Student Address –> @StudentAddress
Once the mapping are done the click on OK to close the Editor window.
After configuring and mappings are done then run the package to the see the result whether parameter values are getting inserted into database table.
Once package is successfully executed with green mark as shown in the image below then go to database and check the table “dbo.TblStudents”.
On SQL Server Management Studio, after opening the table new value for Student Name and Student Address has been inserted successfully as shown in the image below.
With such above method, parameter insertion of constant values into the SQL table will happen for every package execution in short this not the expected way of proceeding i.e. Visual Studio won’t there in real time package execution.
So now what to do?
Package will be deployed in to the system and then it will be executed. Next lab will focused and demonstrate using package deployment method. And then will execute the deployed package.
Demonstrating the package deployment: For that do a right click on package name in Visual Studio in order to go to package project location.
Do a double click on the file available here at the project location
Which will open deployment wizard screen as shown in the image below. Just follow the screen, fill in the details and do next option available at the bottom.
In this screen let remain value set to default radio button “Project deployment file” and keep path set to default only in this case project is present in this path only.
If project is at different location then change the path accordingly.
And then click on Next.
After coming on next tab which is “Select Destination”. Under this tab browse, select and set two fields
- Select the server name
- Select the project location
Just click on browse which will open new window and set the above mentioned two fields and click on Next button.
Finally click on Review tab, check all the selection done within this wizard. Once found selection correct click on the Deploy button available at the bottom.
After doing deployment next comes the Result tab, once found Result –> Passed for the deployment wizard click on Close. It will close the wizard screen.
Now go to SQL Server Management Studio, do refresh on Object Explorer and under “Integration Services Catalogs” deployed project can be seen.
Do a right click on the deployed package and click on Execute.
It will open below screen where existing value of the parameter can be changed for both Address and Student: –
ParAddress –> ParDepAddress
ParStudent –> ParDepStudent
And the click OK to close each respective windows.
Now just execute the table to get new deployed parameter value get inserted in to new field of the SQL table “dbo.TblStudents”
Following is the image which shows deployed parameter values inserted into SQL table.
Using Stored Procedures
This lab will demonstrate practical steps in order to insert values into SQL Server table “dbo.TblStudents” using stored procedures.
In order to write stored procedures go to SQL Server Management Studio and on Object Explorer under Databases –> CustomerDataWareHouse –> Programmability –> Stored Procedure –> New Stored Procedure
Once clicked on Stored Procedure write the query as mentioned in the below snapshot then save and execute it.
Next is go on Visual Studio and right click/double click the component to reconfigure it. Once the Execute SQL Task Editor is open: –
Set IsQueryStoredProcedure –> True
Click SQLStatement and enter the query as “Sp_InsertStudent”
Click OK button to close the window and Editor screen.
Once “Execute SQL Task” component is configured with the stored procedure just ensure to see the values which is available under the “Parameters” tab. Value with “ParStudent” and “ParAddress” will be seen under it and these are values which will be entered into the SQL table after execution of package is done.
Next is to click “Start” button and execute the package. After it is successfully executed with green mark on the component.
Then go to SQL Server Management Studio, execute the table which will display the result of executed by stored procedure.
How to execute select queries using Execute SQL Task
Lab Agenda: In this lab queries will be fired using existing TblStudent and then will use ForEach Task and loop over the record set and then insert each record of TblStudent into the SSISTarget table.
For that now create a similar table with name “SSISTarget” and same fields(StudentsID,StudentName,StudentAddress) on the SQL Server.
For this lab a new SSIS package will be created from the Solution Explorer, by doing a right click on SSIS package –> New SSIS Package with name ExecuteSelectQuery.dtsx.
Once the package is created, next is drag and drop “Execute SQL Task” component on the Control Flow. Thereafter double the click the component or right click and click on Edit to configure it.
On Execute SQL Task Editor click on the General tab and repeat the same steps which we did earlier just follow the steps from 1 to 5 exactly as shown in the image below.
Next is to provide SQLSourceType, here this time will set it to “File Connection”.
Then provide file which contains statement as given below stored location available on the local system
“Select StudentID,StudentName,StudentAddress from TblStudents”
Once file is selected and then select Usage type –> Existing file.
Then select set ResultSet –> “Full result set” and then click OK to close the Editor window.
Next is to click on “Result Set” tab and then click on Add “ResultSet” and under “ResultName = 0” and click on < New variable…> under Variable Name column which will turn open new window named “Add Variable“.
Now under “Add Variable” window add two new things: –
- Give a suitable name to variable, here “StudentData” is given.
- Set Value type to “Object”.
And then click on OK to close the window of “Add Variable” and then again click on OK to close Execute SQL Task Editor window.
Time to drop one more component from SSIS Toolbox called “Foreach Loop Container” on the Control Flow.
Next is to configure it by double clicking on this component.
Once component Editor is open click on Collection tab and on this tab set Enumerator à “Foreach ADO Enumerator”
Then set from dropdown ADO object source variable –> User::StudentsData
And set Enumeration mode as Rows in the first table.
Moving ahead to next tab for doing the Variable Mappings.
Under the Variable column click on each column which will open Add Variable window under which set the variable name, value types and value (do value selection for first variable as zero for rest it will set automatically in incremental order as shown in the image below.
Finally click on OK to close the Editor window.
Now do right click on the Control Flow and then click on Variables to add variables.
It will open variable window under it click on add variable icon and here give the name “InsertQuery” and then set Data type as string and under value add below mentioned statement
“Insert into SSISTarget(StudentID,StudentName,StudentAddress)values(@StudentID,@StudentName,@StudentAddress)”
Next on Control Flow, drag and drop from SSIS Toolbox “Execute SQL Task” but this time drop it inside “Foreach Loop Container” component and not on Control Flow pane then Edit the component in order to configure it.
Now in order to configure it click on General tab and set the following: –
ConnectionType –> ADO.NET
Connection –> set the connection manager which has server name and database.
SQLSourceType –> Variable
SourceVariable –> User::InsertQuery
Coming to the following Parameter Mapping tab, here click on Add three times as mapping of three variables need to be done
Please Note: Here Parameter Name is the table field on the “SSISTarget” table available on SQL Server. So enter the same name with that naming convention.
Finally click OK to close the Editor window.
Then connect the “Execute SQL Task” component to “Foreach Loop Container” component as shown in the below image
Execute the component by clicking on the Start button on the top menu or click ctrl+F5 on the keyboard. Once package is executed successfully.
Next is to go on SQL Server Management Studio and open both the tables TblStudents and SSISTarget compare and check fields are created exactly.
From below image snapshots it shows all fields are correct and they inserted the records exactly.
Also see our below start-up MSBI project series videos: -