Running SSIS package using .Net code

I am running this code my question is when it runs ExecuteNonQuery() it doesn't wait for package to complete It just returns "Package succeeded' - The way you can test if you run this code 2 times one after another. ExecuteNonQuery() breaks with error -

SQLServerAgent Error: Request to run job RunSsisPkg (from User Domain\User) refused because the job is already running from a request by User Domain\User.

How can I check before return package is running and wait to complete before return?

using System;

using System.Data;

using System.Data.SqlClient;

namespace SSISRun

{

class Program

{

static void Main(string[] args)

{

SqlConnection jobConnection;

SqlCommand jobCommand;

SqlParameter jobReturnValue;

SqlParameter jobParameter;

int jobResult;

jobConnection = new SqlConnection("Data Source=(local);Initial Catalog=msdb;Integrated Security=SSPI");

jobCommand = new SqlCommand("sp_start_job", jobConnection);

jobCommand.CommandType = CommandType.StoredProcedure;

jobReturnValue = new SqlParameter("@.RETURN_VALUE", SqlDbType.Int);

jobReturnValue.Direction = ParameterDirection.ReturnValue;

jobCommand.Parameters.Add(jobReturnValue);

jobParameter = new SqlParameter("@.job_name", SqlDbType.VarChar);

jobParameter.Direction = ParameterDirection.Input;

jobCommand.Parameters.Add(jobParameter);

jobParameter.Value = "PackageName";

jobConnection.Open();

jobCommand.ExecuteNonQuery();

jobResult = (Int32)jobCommand.Parameters["@.RETURN_VALUE"].Value;

jobConnection.Close();

switch (jobResult)

{

case 0:

Console.WriteLine("Package succeeded.");

break;

default:

Console.WriteLine("Package failed.");

break;

}

}

}

}

I believe the RunningPackage class can help you.

http://technet.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.aspx|||

Is it true that to use Microsoft.SqlServer.Dts.Runtime I need to have SSIS services in the box? I am looking for a way to run SSIS package without having SSIS service in the box.

The other option using web service is really not working I have one post on that.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1949366&SiteID=1

Thank you - Ashok

|||

Ashok Ojha wrote:

Is it true that to use Microsoft.SqlServer.Dts.Runtime I need to have SSIS services in the box? I am looking for a way to run SSIS package without having SSIS service in the box.

The other option using web service is really not working I have one post on that.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1949366&SiteID=1

Thank you - Ashok

In SQL Server 2005 you cannot because we were telling developers to run data automation with DTS packages, Replication was threatened but you can do it in 2000 and it works like a charm.

|||

I don't believe you have to have the SSIS Service running to run packages, but you do have to have a license for SSIS on the computer where you will be running the packages, and you will need to install the SSIS components.

|||

jwelch wrote:

I don't believe you have to have the SSIS Service running to run packages, but you do have to have a license for SSIS on the computer where you will be running the packages, and you will need to install the SSIS components.

In 2000 the the DTS runtime is in the package it is not the same, it is one of the changes in the product I have used DTS from 7.0

|||

Thank you. I am using SSIS so it's sql 2005. What I understand that I can install sql server 2005 and SSIS services in server "A" with license and my server "B" which runs ETL can have this .net process which is using "Microsoft.SqlServer.Dts.Runtime" dll can run .dtsx files in Server "A" without having SSIS service/license in server "B". right?

- Ashok

|||

Ashok, Server B requires a license as well to get the Microsoft.SqlServer.Dts.Runtime dll.

The SSIS components are not redistributable like they were in DTS.

|||

Hi Phil,

How the SSIS components license and SSIS Services license works. I have to recheck installation of SSIS is it while installing SSIS I can pick only SSIS components or Service.

Thank you - Ashok

|||Just install the SSIS components. I think it's just one checkbox. You should not need the service, if you can ignore that.