I am executing an SSIS package from VB.NET code. Works great in design mode. When I publish it (using Click-Once), it works great on development computer but fails on a customer computer: Microsoft.SqlServer.Dts.Runtime.DtsPipelineException: Retrieving the COM class factory for component with CLSID {E80FE1DB-D1AA-4D6B-BA7E-040D424A925C} failed due to the following error: 80040154. ---> System.Runtime.InteropServices.COMException (0x80040154): Retrieving the COM class factory for component with CLSID {E80FE1DB-D1AA-4D6B-BA7E-040D424A925C} failed due to the following error: 80040154. at Microsoft.SqlServer.Dts.Runtime.Package..ctor() --- End of inner exception stack trace --- at Microsoft.SqlServer.Dts.Runtime.Package..ctor() My project references Microsoft.SQLServer.ManagedDTS. The error occurs on the following line: Dim objPackage As New Microsoft.SqlServer.Dts.Runtime.Package Looking at various forum threads, it would appear that it's because either (1) DLL(s) are not registered on the customer machine, or (2) DLL(s)are missing from the customer machine. The first explanation doesn't seem right -- even though Microsoft.SQLServer.ManagedDTS is a COM dll, doesn't .NET provide a wrapper when it references it? I downloaded Process Explorer from Sysinternals.com to compare loaded DLLs on my dev box to those on the customer's box and while several dlls are loaded when the line is successfully loaded, all of them exist on the customer box. Any help would be greatly appreciated.

ManagedDTS is a .NET assembly, not a COM .dll. The pipeline, however, is a COM dll. Is the Integration Services service installed on the customer computer?

Does the customers machine have SSIS installed? SSIS is a not a simple redistributable. This applies to both the files required and also the license perspective.

Thanks very much to both of you. No, I have not installed SSIS -- I didn't think it would be necessary. I will definitely do so and post info as to whether that fixes the problem. Thanks again.

Installing the SSIS Service got me 90% of the way there -- the DIM objPackage line executes successfully. THANK YOU. Now, the bad news: On the cursotmer's machine, when I run the SSIS package from my app, it returns with 5 errors, each of which has an Error Code = 1071636471. Note: everything works fine running the app on my dev box (both machines are on the same LAN). The two lines of code are: objPackage = objApp.LoadPackage(fileName:=\\NT34\Package\LobbyistSave.dtsx, events:=Nothing) objPkgResults = objPackage.Execute() [To get the number of errors I read objPkgResults.Errors.Count] On both machines, I am using my own login so I'm thinking it can't be permissions -- the difference has to be what is installed on the two machines -- are there other SQL Server components that need to be installed? I could install the full-blown SQL Server on the customer's machine, but I'd definitely rather not. Again -- any assistance will be greatly appreciated.

Have a look at the details of each of the Package.Errors items, what does it tell you about what went wrong? You need to install SSIS to run SSIS packages, it does not require the SQL Database Engine, but generally I install both, after all I have paid for both, since SSIS is licensed as a full SQL Server. Does your client need a dedicated SSIS server?

No, she doesn't need an SSIS server. My customer is actually a secretary in the same overall organization I'm doing my development in. The organization has the licenses but it seems like it shouldn't be necessary to install the database engine. I was going to install all of it just to see if that fixed the problem, but can't get to the CDs until tomorrow. In the meantime, to answer your question: the package is erroring out trying to access the database. (By the way, the package reads a database and copies it to an MSAccess file.) The database is accessed in my app and in the package using SQL Server Security - User = "3rdfloor" andthe correctpassword. There's no problem with the app reading data from that database but the package fails. As I've said earlier, on my dev box, the package runs great -- both computers are on the same network, running on my login. The error message Description is: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user '3rdfloor'.". Thanks for helping with this.

Im worried you have solution that you have no licenses for SSIS is a server product. To run a SSIS package requires a SSIS server. That SSIS server is the machine that hosts the SSIS package during execution. The machine on which is executing the package requires SSIS server installed. That machine requires a full SQL Server server licence, not just a CAL. When developing SSIS the package runs on your desktop, the machine that is running BIDS. That is fine for development, but for the secretary to run the package on here PC she needs SSIS, and a server licence. Without layer of abstraction between your user and the server hosted SSIS package, I think you have a problem. The simplest solution is to put the package on a server and run it via SQL Agent. That provides a server host with the relatively simple start job context to running a package. I am unclear on where the packages is really running, which machine hosts the execution process, and under which user context that is happening. It is a common issue for these two points to cause problems when moving from local dev to other environments,.

Sorry about the confusion -- I was trying to keep my questions brief. The package is running on a licensed SQL Server ("NT34");it accesses another licensed SQL Server ("WebSQL2") outside the firewall. Both SQL Servers have the database engine, SSIS, and Client Tools installed. My Dev box,the secretary's computer, and both SQL Servers are running on the same network. My Dev Box has a licensed copy of SQL Server running on it. Per this forum, I installed SSIS (but not the database engine or Client tools) onthe secretary's computer. There would not be a problem obtaining a SQL Server license for her computer if that is required. Hope that clears up what's running where.

What's the package ProtectionLevel property set to? Are you using a configuration to set the connection string? This sounds like the package does not have the password saved in it.

You've gotten me a lot closer -- thank you. Yes, the password was not being saved with the package. I checked the "Save Password" checkbox and it still doesn't save it -- every time I exit/re-enter the "Connection Manager" dialog, the password is blank. The Package Protection Level Property is set to "EncryptSensitiveWithUserKey". I temporarily changed it to "EncryptSensitiveWithPassword", which didn't help me -- it just started asking me for the password. I haven't been using a configuration, but I did play with that today. I defined a configuration that includes Connection String and the various values under that, including Password. The XML file it generated didn't actually contain the Password value, either in the Connection String or separately. As a test, I hacked the XML file, adding the Password value. The result was that I could now successfully run the package on the secretary's computer by double-clicking it and pressing the "Execute" button on the Execute Package Utilitydialog. (Before that, executing the package that way would fail UNLESS I changed the Connection String displayed in "Connection Managers" section of the dialog.) I obviously don't much like having the password in plain text in the XML file, but frankly, I could live with that if it fixed the problem of running the package from my app, but it didn't -- I still get the "Login failed for user '3rd floor'" error when I execute the package with the following line: objPackage = objApp.LoadPackage(fileName:=My.Settings.BackupToMSAccess_Package, events:=Nothing). I have no idea why running the package this way (i.e., from code) doesn't appear to use the configuration XML file and would sure appreciate some suggestions.

have your problem got solved? I am also facing the same error (password doesn't get saved in DTS designer) and so throws a "login-failed" error when running the DTS package from C#. Please help

No -- it definitely didn't get solved. I went on to other things and now I'm back and still can't get it to work on any other machine than the one I'm developing on. I'm wondering if there is a security setting that, unless changed, does not save the password even though the checkbox is checked.....

A couple of things to check: Are you using a relative path to your XML configuration? Or a mapped drive? When using EncryptSensitiveWithPassword, were you setting the password in the application code before loading the package? http://agilebi.com/cs/blogs/jwelch/archive/2007/11/15/running-password-protected-packages.aspx

Thanks to all who helped. I finally got this to work by changing my VB.NET code to call a Stored Procedure, which calls the package. So, the steps that workded for me are: 1. Create and save the package with ProtectionLevel = DontSaveSensitive. 2. In the package, set up a configuration file that saves ConnectionString 3. Save the package (in the code below, package name = "LobbyistSave.dtsx" 4. Edit the configuration file to add Password=xxx; -- where xxx is your password 5. Write a Stored Proc that calls the Package. For example: DECLARE @cmd varchar(255) SET @cmd = 'dtexec /F " \\nt34\D$\Microsoft SQL Server\MSSQL.\MSSQL\LobbyistSSISPackage\LobbyistSave.dtsx ' EXEC master..xp_cmdshell @cmd 6. Call the Stored Proc from your code

SSIS packages can only be programatically executed on the machine where SSIS Service is installed. This is registered as 'SQL Server Integration Services' in the windows service list. Note that SSIS Service is a Server Component of SQL Server, so it is probably just the servers or the developer's machine which has this. So when the windows app, in a client's machine, tries to run a SSIS package, it will error out. This might not be an issue with web forms where the Web Server and the SQL Server resides on the same machine. Since execution of packages will be done by the web server. Your solution is one way to get around this. Which is to invoke SSIS package execution remotely. So in your solution, the execution of the packages was basically done in the sql server as invoked by the client machine through an SP. With regards to the original reported error (80040154) which originally brought me to this site in the first place. That error is mainly due to platform inconsistencies. Visual Studio project has a default target platform of 'ANY CPU'. It means that when the dll is compiled at runtime by the CLR, the platform(32bit vs 64bit) will depend on what the machine platform is. If the machine (where CLR is running) is 64bit then the dll will be translated by the CLR into a 64bit machine code. Same with 32 bit. Now the issue comes in when the version of the installed SSIS Service is different (ex. 32bit SSIS in a 64bit machine). In the issue I was debugging, a 32bit SSIS was installed in a 64bit server. Since my project's target platform was 'ANY CPU' and my server was 64bit, when CLR loaded the dll, it compiled to a 64bit executable which then tried to load a 32bit DTS COM. Walah! This nasty 80040154 error pops up. In this case, I had to change the project's target platform from 'ANY CPU' to 'x86' to force CLR to translate to a 32bit machine code when the dlls are executed. You can do this, by going to the project's properties page, under the build tab. Look for Target Platform.