Click here to Skip to main content
15,867,141 members
Articles / Database Development / SQL Server
Tip/Trick

Execute SSIS Packge by using C# and SQL Server

Rate me:
Please Sign up or sign in to vote.
4.85/5 (14 votes)
26 Sep 2013CPOL3 min read 140.6K   33   11
Here are the different ways to execute an SSIS Package by using C#.

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.

C#
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 : -

C#
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 : -

SQL
 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 : -

C#
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#.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) R1 RCM Pvt. Ltd.
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionAccess to project parameters executing loaded package Pin
siminal3-Nov-21 17:36
siminal3-Nov-21 17:36 
Questionhow did you passed the parameter?????? Pin
Member 1395217216-Aug-18 20:26
Member 1395217216-Aug-18 20:26 
QuestionError Executing SSIS Package C# Pin
PonchoDiaz30-Jun-16 13:04
PonchoDiaz30-Jun-16 13:04 
QuestionHave some clarification Pin
Ganesh KP16-Aug-15 21:29
professionalGanesh KP16-Aug-15 21:29 
AnswerRe: Have some clarification Pin
Sandeep Kumar Tripathi21-Aug-15 5:20
Sandeep Kumar Tripathi21-Aug-15 5:20 
QuestionSsisPackage Execution help for mentioned scenario Pin
NITESH SUVARE7-May-14 2:10
NITESH SUVARE7-May-14 2:10 
QuestionHow do you pass SQL credentials into the package? Pin
Julio Zuart5-Nov-13 3:40
Julio Zuart5-Nov-13 3:40 
GeneralMy vote of 5 Pin
Anthony Daly26-Sep-13 7:51
Anthony Daly26-Sep-13 7:51 
GeneralRe: My vote of 5 Pin
Sandeep Kumar Tripathi1-Oct-13 20:01
Sandeep Kumar Tripathi1-Oct-13 20:01 
GeneralMy vote of 5 Pin
kumarvinit8626-Sep-13 3:43
kumarvinit8626-Sep-13 3:43 
good hai sandip.
GeneralRe: My vote of 5 Pin
Sandeep Kumar Tripathi1-Oct-13 20:02
Sandeep Kumar Tripathi1-Oct-13 20:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.