Saturday, January 16, 2010

Executing an SSIS package through code or sql agent

Using code to execute the package
Create simple windows application to execute a package. Open Visual Studio and create a Windows Application. Add a reference to Micosoft.SQLServer.ManagedDTS as shown below:

In case SQL Server 2005 is used, then select version 9.0 and in case of SQL Server 2008 use the 10.0 version.
Add a button to the form and double click the button to add the following code in button click event:
String path = @" C:\ExecutionModesofSSIS\Package.dtsx";
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
Package pack = app.LoadPackage(path, null);
pack.Variables["Mode"].Value = "CSharp";
DTSExecResult Result = pack.Execute();
Add following using statement as well:
using Microsoft.SqlServer.Dts.Runtime
Build the solution and execute it. Check the result in the table. This time there will be an entry for Execution Mode as CSharp.

Using SQL Agent by creating a job (scheduled or unscheduled)
Create a job to execute the package from file system. Right click the SQL Agent by connecting to SQL Server Management Studio and select New Job. Give the job name as SSISMode and then click on Steps. Click on New to add a job step Execute as shown below:

SQL Agent Service Account is the default account which the SQL Agent will use while executing the package. Now click on Set Values tab shown in the above figure (Green colored box) and set the value of variable Mode as SQLAgent as shown below:

Once the value of variable Mode is set, finish the job by clicking OK. Don’t schedule the job (it can be scheduled by clicking on Schedule tab). Now right click the job and click on "Start Job at step…" as shown below:

Check and verify the result in table. There will be an entry for SQLAgent as Execution Mode inside the table. There is one column “Executing User Name” in the table which has not been considered yet. Check the value for that column. For all the execution modes other than SQLAgent, the executing user name will be same (the domain account like: ABCD\nitesh.rai) but for Execution Mode as SQLAgent, the user name will be NT AUTHORITY\SYSTEM. This is the account, which the SQL Agent is configured to use (SQL Agent Service account) which can be changed if required.


No comments:

Post a Comment