Package execution fails in Windows service but runs fine as a Windows application.

I am attempting to write a Windows service that watches a database for uploaded files to import. When a new file is found, the corresponding SSIS package is run from the file system with variables passed through. I started development as a Windows app and copied the functionality to a service.

The app runs fine. The service does not. I get a "Failure" each time a package is executed. Everything is identical behind the scenes with the obvious exceptions that OnStart and OnStop handlers are buttons in the app. I added a script task at the beginning of one of the SSIS packages to notify me that it is even running at all. It doesn't even hit that initial task.

Again, the app will run all packages just fine. The data is imported and the results return as "Success."

The following is the code executing the package. Any help is appreciated. I've been banging my head on this one for a few days now. (Is there a tag to format a code sample )

Dim pkgLocation As String
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult

pkgLocation = sPackageFolder & PackageName & ".dtsx"

pkg = app.LoadPackage(pkgLocation, Nothing)

Dim vars As Variables = pkg.Variables

vars("ImportId").Value = ImportId
vars("ProductionServer").Value = ProductionServer
vars("ProductionDatabase").Value = ProductionDatabase
vars("SourceFileName").Value = FileName
vars("SourceFilePath").Value = FilePath

pkgResults = pkg.Execute()

Answer this question

Package execution fails in Windows service but runs fine as a Windows application.

Dr. Pastor.
Not sure (it was a while back), but I think I had to specify on the service installer to run under 'LocalSystem' account. Also, try this code:

ProcessStartInfo processStartInfo = new ProcessStartInfo("dtexec", strBldr.ToString());
processStartInfo.UseShellExecute = false;
Seah Chen Khoon

Hi Al, et al.

Thanks for posting your code, that is somre really elegant argument building!

What did you have to do from a security perspective to get dtexec to work using Process.Start() (i.e. did you have to grant any special log on rights)

In prototyping this on ASP.NET, I get the following error when I interrogate the StandardOutput property:

Could not create DTS.Application because of error 0x800401F3

To rule out the obvious, I am impersonating the worker process as myself and I am an admin on the box and an sa on my local SQL box.

Also, I cannot seem to get the actual window to pop up for the life of me.

Thanks,

Rick
DiskJunky
We have a similar Windows service app that's been running a few weeks now. I launch it as a new process because of some advice I got in this post:

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

Here is my code:

public void StartPackage(NameValueCollection vars, string pkgLocation)
{
Process process = new Process();
process.StartInfo.FileName = "dtexec";
StringBuilder strBldr = new StringBuilder(vars.Count * 100);

// Append the package location (full path) in quotes. /f denotes file system location.
strBldr.Append(" /f \"");
strBldr.Append(pkgLocation);
strBldr.Append("\"");

foreach (string key in vars.AllKeys)
{
strBldr.Append(" /set ");
strBldr.Append(" \"\\package.variables[");
strBldr.Append(key);
strBldr.Append("].Value\";\"");
strBldr.Append(vars[key]);
strBldr.Append("\"");
}

process.StartInfo.Arguments = strBldr.ToString();
#if DEBUG
process.StartInfo.WindowStyle = ProcessWindowStyle.Normal;
#else
process.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
#endif

process.Start();
}
Package execution fails in Windows service but runs fine as a Windows application.

Answer this question