0

I am able to execute some SSIS packages from my local computer however when I am trying to execute the same through windows service on Windows server R2 it fails. Windows Service Code:

using DTS = Microsoft.SqlServer.Dts.Runtime;

    DTS.Package pkg;
            DTS.Application app;
            DTS.DTSExecResult pkgResults;
            app = new DTS.Application();
            pkg = app.LoadPackage(pkgLocation, null);
            pkgResults = pkg.Execute();

I am installing the service on the server and when started it fails on app = new DTS.Application();

On the server I have already SSIS installed and I am also able to execute the Package from command prompt. The complete error message is :

Microsoft.SqlServer.Dts.Runtime.DtsComException:

An Integration Services class cannot be found. Make sure that Integration Services is correctly installed on the computer that is running the application. Also, make sure that the 64-bit version of Integration Services is installed if you are running a 64-bit application. --->

System.Runtime.InteropServices.COMException: Retrieving the COM class factory for component with CLSID {BA785E28-3D7B-47AE-A4F9-4784F61B598A} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).

at Microsoft.SqlServer.Dts.Runtime.Application..ctor() --- End of inner exception stack trace --- at Microsoft.SqlServer.Dts.Runtime.Application..ctor()

Any help on this?

Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
user2519971
  • 345
  • 3
  • 12
  • 27

1 Answers1

0

It seem that you have installed the 32-bit version of SSIS, according to this article 64 bit Considerations for Integration Services if you run it from command prompt the 32-bit version will be used even if the 64-bit version is installed

By default, a 64-bit computer that has both the 64-bit and 32-bit versions of an Integration Services command prompt utility installed will run the 32-bit version at the command prompt. The 32-bit version runs because the directory path for the 32-bit version appears in the PATH environment variable before the directory path for the 64-bit version. (Typically, the 32-bit directory path is :\Program Files(x86)\Microsoft SQL Server\100\DTS\Binn, while the 64-bit directory path is :\Program Files\Microsoft SQL Server\100\DTS\Binn.)

Now i believe that your service is built against 64-bit, which made the SSIS package request the 64-bit version and it didn't find it.

So you either change it to run as 32-bit or install the 64-bit version of Integration Services.

Mahmoud Darwish
  • 1,168
  • 1
  • 15
  • 28
  • On my dev box both 32 & 64 bit version is installed and i made changes to Path environment variable to point to respective Microsoft SQL Server\100\DTS\Binn directory but still no luck. One thing I have found that on the server Microsoft.SQLServer.ManagedDTS.dll does not exist and I had copied this dll to service BIN directory. do you see any issue associated with that ? – user2519971 Jul 10 '13 at 11:31
  • so both 32 and 64 bit versions are installed on the Windows server R2 machine? as for the Microsoft.SQLServer.ManagedSTS.dll here is the correct location for it http://stackoverflow.com/questions/6679721/cant-compile-ssis-component-missing-assembly-in-visual-stduio-2008 for 2008 R2 its C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies for 32 and C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies for 64 – Mahmoud Darwish Jul 10 '13 at 11:44
  • Thanks. this dll is not available at suggested path so should I fire up the SQL Server installation disk and install the SDK again or merely copying the libraries will sort out the problem? can u suggest the name of the component to be selected at the time of installation ? – user2519971 Jul 10 '13 at 12:39
  • check [Install Integration Services](http://msdn.microsoft.com/en-us/library/ms143731.aspx) >Select Integration Services to install the Integration Services service and to run packages outside the design environment. For a complete installation of Integration Services, together with the tools and documentation for developing and managing packages, select both Integration Services and the following Shared Features: **Client Tools SDK** to install managed assemblies for Integration Services programming. – Mahmoud Darwish Jul 10 '13 at 12:53