AcceptBreakpointManager(BreakpointManager) AcceptBreakpointManager(BreakpointManager) AcceptBreakpointManager(BreakpointManager) AcceptBreakpointManager(BreakpointManager) This method is called by the run-time engine when a task or container is created, and passes it a BreakpointManager manager to allow the task to create, remove, and check the status of breakpoints. Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure. End Enum ' The execution engine calls this method when the task executes. ' To access the object model, use the Dts property. Connections, variables, events, ' and logging features are available as members of the Dts property as shown in the following examples. The Microsoft.SqlServer.Dts.Runtime namespace contains the classes and interfaces to create packages, custom tasks, and other package control flow elements.

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.

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 SettingsSandeepMy DocumentsSSISPackageSsisPackage.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 : -

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

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.

Microsoft.sqlserver.dts.runtime Nuget

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

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 fromWeb.Config file of your C# code.

Manageddts

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

Microsoft.sqlserver.dts.runtime.variables

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

Microsoft Sqlserver Dts Runtime Webservice