Executing SSIS package from batch file

We can also execute the SSIS package through a batch file.

In this article, we will see how we can execute the package through a batch file.

I am creating a simple package. This package I so simple, in this package I am inserting a record into a table.

                  

Inserting a table into this package

  

Now I am running this package

Package executed successfully.

See the record into the table

  

Now I am creating a batch file which call the package.

In the batch file we are calling DTEXEC.exe to execute the package.

Read: DTEXEC command to execute the SSIS package

https://bageshkumarbagi-msbi.blogspot.com/2018/12/dtexec-command-to-execute-ssis-package.html

In the text file I am writing below code

@ECHO OFF CLS ECHO My Test Package executing from batch file PAUSE "c:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTEXEC.exe" /File "H:\Package.dtsx" PAUSE

Save it with .bat extension.

 

 

File is created.

  

Now click on the bat file

Before running this package record in the database table

  

Now running the batch file

  

File executed successfully.

See the records into the database table.

 

Call the SSIS package when a record inserted into the table

We have one requirement in that the business way to run the scheduled job when we insert a record into the custom table.

There is a job which is scheduled to run every Sunday but some time business want to run this package in weekdays when we insert a record into a table (Manual_Job_run_history)

Structure of Manual_Job_run_history table

create table Manual_Job_run_history   (RunID int identity(1,1),   Executed_by varchar(50),   Rundate datetime    )

With the help of the trigger, we can achieve this task

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERTING, UPDATE, or DELETE statements on a table or view.

Read More about Trigger Here

https://bageshkumarbagi-msbi.blogspot.com/2017/08/trigger-in-sql-server.html

The job is scheduled on the Sql server Agent

  

In this job calling a simple package.

in this package, I am inserting a record into a table.

  

Inserting a table into this package

Now I am running this package

Package executed successfully.

See the record into the table

Now I am creating a trigger on the Manual_Job_run_history table on insert event

Create trigger trg_Manual_Job_run_history_insert on Manual_Job_run_history for insert AS BEGIN EXEC msdb.dbo.sp_start_job N'TestJob' END

The trigger is created.

   

    

Now I am inserting a record into this table

Before inserting a record into the Manual_Job_run_history table see the records in the BadData table

  

Now inserting a record into the table

  

See job is executed successfully

     

Record is also inserted into the BadData table

  

We can see the job execution details in the Job history

  

Hope it will help.

Thanks!

Call Sql Server Agent job using SP_Start_Job Stored Procedure in SQL Server to execute SSIS package

We can all any SQL Server Agent job using SP_Start_job System SP. Here we will see how to call an SSIS package which is scheduled in the SQL Server Agent.

Let’s see this demo

I am creating a simple package. This package I so simple, in this package I am inserting a record into a table.

  

Inserting a table into this package

  

Now I am running this package

  

Package executed successfully.

See the record into the table

  

Now I am creating a job to schedule the SSIS package.

  

Now I am running this job for test

  

See records in the table

  

Writing the SQL script to call this job

USE msdb ;  GO   EXEC dbo.sp_start_job N'TestJob' ;  GO

Before running this script record in the table.

  

Now running this script

  

Job executed successfully.

See the records in the table

  

We can see the job execution details in Job History

Executing a SSIS Package from Stored Procedure (xp_cmdshell) in SQL Server

There are multiple way to execute SSIS packages. In this Article we will see how to run the package using stored procedure.

We can call the SSIS package through below way

Ø  Call SQL Agent jobs using SQL server (EXEC dbo.sp_start_job)

Ø  Call package which is deploy in SSISDB catalog (exec ssisdb.catalog.create_execution)

Ø  Call SSIS package directly (EXEC master..xp_cmdshell)

In this article, we will see how to call SSIS package using xp_cmdshell.

Let’s see this demo

I am creating a simple package. This package I so simple, in this package I am inserting a record into a table.

    

Inserting a table into this package

   

Now I am running this package

   

Package executed successfully.

See the record into the table

  

Now calling this package using System SP

DECLARE @SQLQuery AS VARCHAR(2000) DECLARE @ServerName VARCHAR(200) = 'DESKTOP-DQLCKGK' SET @SQLQuery = 'DTExec /FILE ^"H:\Package.dtsx^" ' EXEC master..xp_cmdshell @SQLQuery GO

       

Before call this SP we need to configure the “sp_configure 'xp_cmdshell', 1”

Using below Script

USE master GO -- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- To update the currently configured value for advanced options. -- WITH OVERRIDE disables the configuration value checking if the value is valid RECONFIGURE WITH OVERRIDE GO -- To enable the xp_cmdshell component. EXEC sp_configure 'xp_cmdshell', 1 GO RECONFIGURE WITH OVERRIDE GO -- Revert back the advance option EXEC sp_configure 'show advanced options', 0 GO RECONFIGURE WITH OVERRIDE GO

         

Now we can run this Sp.

Before running this SP see the records in the table

    

   

Now Running the Script

  

Now see the records in the table

 

  

Calling the package which is deploy in SSISDB catalog package using System SP (create_execution)

We can call our package through System SP which are deployed into SSISDB catalog. With the help of Create_execution SP.

Let’s see this demo

I am creating a simple package. This package I so simple, in this package I am inserting a record into a table.

  

Inserting a table into this package

 

Now I am running this package

 

Package executed successfully. See the record into the table

    

Already I deploy this package into SSISDB

  

With the help of below SQL Script we can call the package.

declare @execution_id bigint  exec ssisdb.catalog.create_execution   @folder_name = 'SSIS2012'  ,@project_name = 'SSIS2012'  ,@package_name = 'Package.dtsx'  ,@execution_id = @execution_id output  exec ssisdb.catalog.start_execution @execution_id  Print @execution_id

      

     

   

Package executed successfully.

See the record into the table

   

   

We can see the executing log with the help of the execution id.

 

Send daily mail to DBA team for Server storage free space Alert

DBA  responsibility is to monitor the SQL Server Storage. There are multiple ways to monitor the storage space.

Manual task:

One of my friend from the DBA team he uses to see the storage manually. Once he reached office he uses to connect to the server system and open the computer and see the free spaces of the storage.

Like below

                                

He comes to know that in C drive we have 30 GB free Space and H 10 GB like. After that, he writes an alert mail to the respective team.

Automation

I automate this task now DBA team getting mail to his inbox daily Morning like below.

Let’s see how we will achieve this task.

I am creating a package in that I extracting the storage status and sending mail to the DBA team.

xp_fixeddrives

With the help of this system SP, we will get the information about the free spaces.

See below.

I am creating a package in that I have created some variable.

  

Free_Space is an object type variable in which we are storing the result set of the System Sp.

Taking Execute SQL task

And configuring the connection manager.

  

In result set table we need to configure the

  

Click ok.

Now I am taking script take to compose the mail and send it to DBA.

Here taking variable

  

Click ok Edit script.

In the script task, I wrote below code

public void Main()         {             // TODO: Add your code here             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 = "Drive Free Alert";             string body = "Hello Team, Server Storage Information in GB.";             DataTable dt = new DataTable();             var oleDa = new OleDbDataAdapter();             float f;             oleDa.Fill(dt, Dts.Variables["User::Free_Space"].Value);             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 += " <html>";                 mail.Body += "<body>";                 mail.Body += "<table border=2>";                 mail.Body += "<tr>";                 mail.Body += "<td bgcolor=#00FFFF>Drive Name</td><td bgcolor=#00FFFF>Free Space in GB</td>";                 mail.Body += "</tr>";                 foreach (DataRow row in dt.Rows)                 {                     mail.Body += "<tr>";                     mail.Body += "<td>" + row.ItemArray[0].ToString() + "</td>";                     f = float.Parse(row.ItemArray[1].ToString()) / 1024;                     if(f<10)                     mail.Body += "<td bgcolor=#FF0000>" +f + " GB" + "</td>";                     else                         mail.Body += "<td bgcolor=#00FF00>" + f+ " GB" + "</td>";                     mail.Body += "</tr>";                 }                 mail.Body += "</table>";                 mail.Body += "</body>";                 mail.Body += "</html>";                 using (SmtpClient smtp = new SmtpClient(smtpAddress, portNumber))                 {                     smtp.Credentials = new NetworkCredential(emailFromAddress, password);                     smtp.EnableSsl = enableSSL;                     smtp.Send(mail);                 }             }             Dts.TaskResult = (int)ScriptResults.Success;         }

Business logic is that if the free space is less than 10 GB record show in Red otherwise Records Show in green.

Build the code and save and close it.

Now the package is ready to run

Now running this package

Before running see the free space in the system.

  

Running this package.

  

See the mail

  

The package is ready to use. We can schedule this package in any scheduler.

Hope this article is helpful for the DBA team.

Thanks!!!