Send mail using execute sql task in SSIS

We can send the mail using execute SQL task in the package. For sending the mail we need to use sp_send_dbmail.

Before using Sp_send_dbmail we need to create a profile.

Read hear: Configure Database mail in SQL server

http://bageshkumarbagi-msbi.blogspot.com/2015/10/configure-database-mail-in-sql-server.html

I have created a profile named [Bagi mail]

Sp_send_dbmail

EXEC msdb.dbo.sp_send_dbmail  @profile_name = 'Bagi Mail',  @recipients = 'bageshkumarbagimsbi@gmail.com',  @body = 'Hello',  @subject = 'Execute sql task',  @exclude_query_output = 1

We can set profile name, recipients, and body and subject name using variables.

Now I am taking execute SQL task.

      

     

Creating the configure manager.

           

     

Click ok. Now I am running this package.

Package executed successfully.

     

       

Now we will see the mailbox.

     

     

See the mail body.

     

Executing SQL File in SSIS using execute SQL Task

We are getting the .sql file we need to run it in the database. This contains different DDL Statements, DML (update, delete etc.) statements.

Here I am using execute SQL task to run this .sql file.

.sql file      

Taking execute SQL task.      

   

Now donning the configuration. Here I am selecting SQLSourceType =’File connection’. After that, we need to configure the File Connection. See below.

           

 

Click on.

In my file I am using insert script hence I am selecting ResultSet is NONE

               

Click ok.

The package is ready to execute.

Before running this package records in the database table.

            

   

Now running this package.

          

Executed successfully. See the records in the database table.

    

Get the expected result.

Excel connection type in execute sql task in SSIS

In execute sql task we have below connection type.

In this article, we will see the Excel connection type.

In this example, I will read an excel sheet data and get the count of all records using the script task.

The package looks like as below.

Now I am creating excel connection manager and configuring it.

Creating a new connection

             

If you have the header on the excel file then you need to select First row has column name. Click ok.        

SQLSourceType will be direct input.

And in SQL Statement I write the select query

SELECT * FROM [100_CC_Records$]

Note: we need to write sheet name in [] followed by $. Otherwise it will throw below error.

[Execute SQL Task] Error: Executing the query "SELECT * FROM [100_CC_Records]" failed with the following error: "The Microsoft Jet database engine could not find the object '100_CC_Records'.  Make sure the object exists and that you spell its name and the path name correctly.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I am using [] followed by $.

One more thing when we are using excel we need to set project property Run64bitRunTime false in debugging.

           

     

Read more about this Click here:

Run64bitRunTime debugging property

http://bageshkumarbagi-msbi.blogspot.com/2018/03/run64bitruntime-debugging-property.html

Creating an object variable to store the result set.

      

     

Now assigning this result set

                         

           

Click ok.

I am taking the script task to get the count of all record in the existing excel sheet.

     

       

Double click on the script task.

                       

     

Click on the edit script

To get the count I write below c# code.

public void Main()               {             // TODO: Add your code here             //Object A = Dts.Variables["User::Result_Set1"].Value;             DataTable dt = new DataTable();             OleDbDataAdapter adapter = new OleDbDataAdapter();             adapter.Fill(dt, Dts.Variables["User::Result_Set2"].Value);             MessageBox.Show(dt.Rows.Count.ToString());                      Dts.TaskResult = (int)ScriptResults.Success;               }

Compile, save and close the script editor and click ok.

The package I ready to execute

        

         

See the records in the excel sheet.       

Get the expected result.

Do we use parameter placeholder? In the TABLE or FROM clause in execute sql task in SSIS

I had a requirement to load the data in the staging table and before the loading the data we need to truncate the table.

We have some flat files and these files have a proper naming convention. First 5 character indicates the Staging table name. Based on the file name we need to truncate and load that data in the corresponding table.

My package design as below.

 

   

In the execute SQL task I write the parameterized SQL statement as below

     

   

Here I am mapping the parameter value

      

     

While I am ruining my package getting below error

     

   

[Execute SQL Task] Error: Executing the query "  Truncate table ?" failed with the following error: "Incorrect syntax near '@P1'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

We cannot use the parameter placeholder? In the TABLE or FROM clause.
Instead, build our SQL statement in a variable.

Variable Sql statement

EvaluateAsExpression: True

Expression:

"TRUNCATE TABLE” + [User::table_name]

      

     

Click ok.

Then have the Execute SQL Task use that variable.

In general table we need to select SQLSourceType is Variable and assign the Source Variable.

       

     

Now click ok.

Run the package.

Before running the package.

         

Running the package.

  

       

Task executed successfully. See the records in the database table.

     

Get the expected result.

Is it possible to return multiple result sets in Execute SQL Task

No, execute SQL task return only one result set at a time. If we are using multiple results set in SQL script it will throw an error.

There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_Rowset".

Let’s see the example

I am taking execute SQL task

        

     

Now I am doing the configuration.

      

     

Here I am configuring Result set if full result set and the connection type is OLEDB.

Writing the two select statement in the SQL statement. It will return two result set. To storing this result sets I am creating two object variable.     

     

Mapping this variable with Resultset.

            

       

Click ok. Now we are able to run this package

As expected this package is failed

      

     

With below error.

   

There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_Rowset".

Now I am keeping only one dataset.

    

       

Also removed one result set

                           

     

Now running this package.

     

     

Package executed successfully.

Note: we can use multiple inserts, update and delete query. But in this case, we need to select result set is none.   

Execute SQL Task based on condition in SSIS

I have a scenario where we need to check the condition based on the condition we need to insert the record in the respective table.       

My requirement is like if we pass the parameter value ‘I’ it means to need to insert a record in the table and if the parameter value is ‘T’ need to truncate the table.

 

Here I am taking Execute SQL task.

   

Now I am creating a variable know as Flag and assign default value is ‘I’ see below

         

 

Now I am configuring the execute SQL task connection manager. Creating the connection and writing the query.             

     

Mapping the parameter here in place of?

       

   

Now we run this package.

Before running this package record in the table

           

       

Now running this package with parameter value ‘I’

Package executed successfully.

      

 

See the records in the table.

         

            

Now I am testing with the other value ‘T’. If we set the value as ‘T’ the table will be truncated.            

           

Running the package.

        

     

Package executed successfully. Now see the records in the tables.

       

 

Another way to use this dynamic query to use         

     

Select direct input.

Now go to expression.

In the expression, I am writing below query which is dynamically generated.

       

The value in the expression tab

"DECLARE @FLAG VARCHAR(1) SET @FLAG ='"+ @[User::Flag] +"' IF @FLAG = 'I' BEGIN INSERT INTO EMP VALUES(1,'BAGESH') END IF @FLAG = 'T' BEGIN TRUNCATE TABLE EMP END"

Click on the evaluate expression we will get below value.

DECLARE @FLAG VARCHAR(1) SET @FLAG = 'I' IF @FLAG = 'I' BEGIN INSERT INTO EMP VALUES(1,'BAGESH') END IF @FLAG = 'T' BEGIN TRUNCATE TABLE EMP END

Now click ok.

We are seeing the fx sign on the execute SQL task.

 

 

It means in this task we are using the dynamic expression.

Read more about expression click here:

https://bageshkumarbagi-msbi.blogspot.com/2018/10/evaluateasexpression-property-in-ssis.html

Now I am executing my package.

Package executed successfully.   

     

See the record in the table.

         

Best practices: when we are using dynamic SQL query it will be better to build using expression.