Problem with SSIS software and from the command line (DTEXEC)

Question

I am trying to execute an SSIS package programmatically using C #.

Application app = new Application(); Package package = app.LoadPackage(pkgFullPath, null); package.Execute(); 

I get an error message:

 Error in Microsoft.SqlServer.Dts.Runtime.TaskHost/SSIS.Pipeline : To run a SSIS package outside of SQL Server Data Tools you must install Conditional Split of Integration Services or higher. Error in Microsoft.SqlServer.Dts.Runtime.TaskHost/SSIS.Pipeline : To run a SSIS package outside of SQL Server Data Tools you must install Lookup of Integration Services or higher. 

I am using SSIS in Visual Studio 2010, but I am executing C # code from the nunit test in VS 2012 (.Net 4.0 works)

The package works fine in the SSIS project in VS 2010 if I start it using debugging (press F5), but it does not work with the same error if I try to start it using dtexec from the command line (the same failure in both 32 and 64-bit version of dtexec). It also fails with the same error if I ran it from within Visual Studio using ctrl + F5 (without debugging)

I found articles on the Internet that suggest this is due to a 64-bit problem with 32-bit versions, but I see the same error when starting both versions of dtexec. I am using dtexec version 11.0.2100.60, which corresponds to the version of SQL Server Integration Services Designer in VS 2010.

I do not get an error if I run a simple package without conditional split and search. Do I have to install something extra to run this outside of Visual Studio?

Any ideas?

Answer 1

Continuing my comment above, if the service is installed correctly, there may be a rights issue (I see that you are using SQL 2012) of the account from which the packages are running.

Contact http://technet.microsoft.com/en-us/library/hh213130.aspx

Hope this helps.

Answer 2

This is VB code, but it can be easily translated into C #. Try running the SQL command that runs the SSIS package.

Something like:

 Try 'Job implementation goes here Dim jobConnection As System.Data.SqlClient.SqlConnection Dim jobCommand As SqlCommand Dim jobParameter As SqlParameter Dim jobReturnValue As SqlParameter Dim jobResult As Integer jobConnection = New System.Data.SqlClient.SqlConnection(SSISConnectionString) jobCommand = New SqlCommand("msdb.dbo.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 = DirectCast(jobCommand.Parameters("@RETURN_VALUE").Value, Integer) jobConnection.Close() Select Case jobResult Case 0 'Successful run Case Else Throw New Exception("SQLAgent Job failed to start!") End Select Catch ex As Exception Return ex End Try 

Answer 3

According to microsoft web page Install Integration Services :

"Some of the SQL Server components that you can choose to install on the feature selection page in the Setup Wizard install a partial set of Integration Services components. These components are useful for specific tasks, but the functionality of Integration Services will be limited. For example, the" Database Component Services "installs the Integration Services components needed for the SQL Server Import and Export Wizard. The SQL Server Data Tools option installs the Integration Services components needed for package development, but the service Integration Services is not installed and you cannot run packages outside of SQL Server Data Tools To ensure that Integration Services is fully installed, you must select Integration Services on the function selection page.

This is why you can run SSIS packages from Microsoft tools, but not from outside these tools. It is strange that it falls only on certain types of components.

If you follow their recommendations on this page to fully install the integration services components, this should fix the problem.

"For a full installation of Integration Services, along with tools and documentation for developing and managing packages, select Integration Services and the following general functions:

• SQL Server data tools for installing package development tools.
• Management Tools — Complete the installation of SQL Server Management Studio for package management.
  • Client SDK for installing managed assemblies for programming Integration Services.

"