Execute SSIS Packge by using C# and SQL Server

Introduction

Sometimes we need to call/execute an SSIS Package through a C# application. This is just a small solution for how we can Call/Execute an SSIS Package through a C# application.

There are different ways to Run an SSIS Package through a C# application. I have just described two easy ways to execute an SSIS Package through a C# application.

Using the code

By Using C# Code

For Call SSIS Package through C#. We have to Add Reference for run the DTS Package. The reference is: Microsoft.SqlServer.Dts.Runtime.

using Microsoft.SqlServer.Dts.Runtime;

The above reference is used for loading the SSIS Package, Execute the SSIS Package and the tasks which is related to SSIS Package.

I have approached the following way toExecute the SSIS Package : -

1. Load the SSIS Package by using app.LoadPackage(@"\\C:\Documents and Settings\Sandeep\My Documents\SSISPackage\SsisPackage.dtsx", null);
2. Store the Package details into package variable.
3. Set the Package Variable by using package.Variables["User::EmpCode"].Value = '1'; Here package.Variables use to access the variables of the Loaded SSIS Package.
4. After Set the variables into SSIS Package, Execute the SSIS Package by package.Execute(); and store the result into the DTSExecResult variable.
5. The returned result will be either Failure or Success.
6. You can show the any result based on Execution result of SSIS Package.

The Code which describes the above parts are as follows : -

public static void RunPackage()
{
    Application app = new Application();
    Package package = null;
    //Load the SSIS Package which will be executed
    package = app.LoadPackage(@"<a href="file://\\C:\Documents">\\" + 
      @"C:\Documents</a> and Settings\Sandeep\My Documents\SSISPackage\SsisPackage.dtsx", null);
    //Pass the varibles into SSIS Package
    package.Variables["User::EmpCode"].Value = '1';
    package.Variables["User::EmpName"].Value = "SANDEEP";        
    //Execute the SSIS Package and store the Execution Result
    Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();
    //Check the results for Failure and Success
    if (results == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
    {
        string err = "";
        foreach (Microsoft.SqlServer.Dts.Runtime.DtsError local_DtsError in package.Errors)
        {
            string error = local_DtsError.Description.ToString();
            err = err + error;
        }
    }
    if (results == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success)
    {
        string message = "Package Executed Successfully....";
    }
    //You can also return the error or Execution Result
    //return Error;
}

By Using C# & Stored Procedure

I have Implemented the another way, which is by using Stored Procedure which is created into SQL Server. Then execute that Stored Procedure from C# Code to Execute SSIS Package.

You can use that Stored Procedure anywhere for Execute the SSIS Package.

In the Stored Procedure, I have taken 3 Parameters, These 3 Parameters are used for executing the SSIS Package.

• EmpCode: Parameters which will pass into the package variable.
• PackagePath: SSIS Package which will be executed.
• EmpName: Parameters which will pass into the package variable.

In that stored Procedure, I am creating a SQL Query which uses DTExec utility. Then Set the package variable by /SET \Package.Variables[EmpCode].Value;'+ @EmpCode

After creating the SQL statement into the Stored Procedure that SQL statement will run on Command Shell. That will be used by EXEC master..xp_cmdshell @SQLQuery statement.

After executing that SQL Query, if any error occurs then ErrorHandler is called.

The Stored Procedure Script is as follows : -

 CREATE PROCEDURE [dbo].[sp_ExecPackage]
( 
@EmpCode Varchar(10), 
@PackagePath Varchar(2000), 
@EmpName varchar (10)
)
AS 
DECLARE @SQLQuery AS VARCHAR(2000)
SET @SQLQuery = 'DTExec /FILE "'+@PackagePath+'" '
SET @SQLQuery = @SQLQuery + ' /SET \Package.Variables[EmpCode].Value;'+ @EmpCode
SET @SQLQuery = @SQLQuery + ' /SET \Package.Variables[EmpName].Value;'+ @EmpName

EXEC master..xp_cmdshell @SQLQuery 
 
If @@ERROR <> 0 GoTo ErrorHandler 
 SET NoCount OFF 
 Return(0) 
 
ErrorHandler: 
 Return(@@ERROR)

I have used the above Stored Procedure into C# Code for Execute the SSIS Package.

In the RunLoad() function, I used the following approach : -

1. Passed the Package Path
2. Passed the Stored Procedure Name
3. Passed the SqlParameters for the Stored Procedure by using SqlParameter paramEmpCode = new SqlParameter("@EmpCode", "2"); paramEmpCode.SqlDbType = System.Data.SqlDbType.VarChar;
4. Call the executeCommandQuery() function to execute the Stored Procedure.
5. Fetch the Results after execution Stored Procedure.
6. You can use the result for the further use.

The Code snippet for the RunLoad() and executeCommandQuery() is as follows : -

public string RunLoad()
 {
 string result = string.Empty;
 int rows = 0;
 string PackagePath = "C:\Documents and Settings\Sandeep\My Documents\SSISPackage\SsisPackage.dtsx";
 string SPCallSSIS = "[sp_ExecPackage]"; // Stored Procedure Name
 
//Passing Parameters to the Stored Procedure
 SqlParameter paramEmpCode = new SqlParameter("@EmpCode", "2");
 paramEmpCode.SqlDbType = System.Data.SqlDbType.VarChar;
 SqlParameter paramEmpName = new SqlParameter("@EmpName", "SANDEEP");
 paramEmpName.SqlDbType = System.Data.SqlDbType.VarChar;
 SqlParameter paramPackagePath = new SqlParameter("@PackagePath", PackagePath);
 paramPackagePath.SqlDbType = System.Data.SqlDbType.VarChar;

 SqlParameter[] paramList = { paramEmpCode, paramEmpName, paramPackagePath };
rows = executeCommandQuery(SPCallSSIS, CommandType.StoredProcedure, paramList); // Executing Stored Procedure
 if (rows > 0)
{
 result = "Success";
 }
 else
 {
 result = "Error";
 }
 return result;
 }


protected int executeCommandQuery(String sQuery, CommandType commandType, SqlParameter[] Param)
 {
 int rowEffected = 0;
protected SqlConnection _connection;
_connection.ConnectionString = 
  ConfigurationManager.ConnectionStrings["SQLConnStr"].ConnectionString; //From Web.config
//You can also write the connection string for SQL Server
 try
 {
 _connection.Open();
 SqlCommand command = new SqlCommand(sQuery, _connection);
 command.CommandType = commandType;
 command.Parameters.AddRange(Param);
 command.CommandTimeout = 0; 
 rowEffected = command.ExecuteNonQuery();
 _connection.Close();
 }
 catch (Exception ex)
 {
 rowEffected = -9;
 _connection.Close();
 throw ex;
 }
 finally
 {
 _connection.Close();
 }
 return rowEffected;
}

In the executeCommandQuery() function we are passing the following Parameters : -

• sQuery: SQL Query which will be executed. That will be a Stored Procedure Name
• commandType: It should be Text or Stored Procedure
• Param: Param is the parameters passed into the Stored Procedure.

I have set the SQLConnection string. You will directly write the connection string here or set the connection string from Web.Config file of your C# code.

Then use ExecuteNonQuery() to execute the SQL statement or Stored Procedure.

Points of Interest

While writing the above code, I found different ways to execute SSIS Package through C#. There are just two ways here. In my next article, I will Execute that SSIS Package through SQL Job and Windows Profiler. Which will be created by C#.