Error on Executing SSIS packages on a remote machine using code

Hello all,

I am trying to execute the ssis packages using code.These packages are hosted on remote server under MSDB in local Network.

Below is the code tried to Execute
--

DTSExecResult execRslt = _dtsxPackage.Execute();

foreach (DtsError dtserr in _dtsxPackage.Errors)
{
Console.WriteLine("Source: " + dtserr.Source + ", Description: " + dtserr.Description);
}

I get hte following Error
--

"Dupaco Load MMAs"-"An OLE DB error has occurred. Error code: 0x80004005.\r\nAn OLE DB record is available. Source: \"Microsoft OLE DB Provider for SQL Server\" Hresult: 0x80004005 Description: \"[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.\".\r\n"

"Data Flow Task" -"The AcquireConnection method call to the connection manager \"Dupaco\" failed with error code 0xC0202009.\r\n"

"Data Flow Task" -"component \"OLE DB Destination\" (119) failed validation and returned error code 0xC020801C.\r\n"

"One or more component failed validation.\r\n"

"There were errors during task validation.\r\n"

When i try to execute the same code on the server where all the package are hosted. It is working fine.

More Info:

In thepackage we are using one "Source Flat file connection" and one "Oledb Destination connection"

Please help me to solve this problem, as my client needs it very badly

Thanks
Subin

Subin wrote:

"Dupaco Load MMAs"-"An OLE DB error has occurred. Error code: 0x80004005.\r\nAn OLE DB record is available. Source: \"Microsoft OLE DB Provider for SQL Server\" Hresult: 0x80004005 Description: \"[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.\".\r\n"

The problem appears to be that whatever address or alias was used to create the OLE DB connection can't be resolved from where the package is executing. Maybe it was set up using (local) or something. Maybe the server doesn't allow TCP/IP connections (the default for SQL Server 2005).
|||

Hi Jay,

Earlier We were using sql server name now we have changes that to IP address,

we have saved the password in the "Oledb Destination connection" by checking in the checkbox to save the password.

now we are getting the following Error

"An OLE DB error has occurred. Error code: 0x80040E4D.\r\nAn OLE DB record is available. Source: \"Microsoft OLE DB Provider for SQL Server\" Hresult: 0x80040E4D Description: \"Login failed for user 'sa'.\".\r\n"

|||

Subin wrote:

"An OLE DB error has occurred. Error code: 0x80040E4D.\r\nAn OLE DB record is available. Source: \"Microsoft OLE DB Provider for SQL Server\" Hresult: 0x80040E4D Description: \"Login failed for user 'sa'.\".\r\n"

I've already answered that one today, so all you get is a link.

See if this helps.

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

Subin wrote:

"An OLE DB error has occurred. Error code: 0x80040E4D.\r\nAn OLE DB record is available. Source: \"Microsoft OLE DB Provider for SQL Server\" Hresult: 0x80040E4D Description: \"Login failed for user 'sa'.\".\r\n"

Are you intending to be logging in as the user 'sa'?|||

Subin wrote:

Hello all,

I am trying to execute the ssis packages using code.These packages are hosted on remote server under MSDB in local Network.

...

To clarify possible confusion: you are not executing the package on a remote machine (as the title of your question suggests).

You are loading package from a remote machine and running it locally (where your application runs).

http://blogs.msdn.com/michen/archive/2006/08/11/package-exec-location.aspx

Michael.

|||

Hello Michael,

You are absolutely correct. Sorry for confusion.

I am not executing the package on a remote machine (as the title of question suggests),loading package from a remote machine and running it locally (where my application runs).

Thanks

Subin

|||

Hello All,

Thank you all for help.

I have solved the problem.

The package which i had tried to execute was a password protected package and I was not setting the PackagePassword property of Application class before loading package from remote machine. So that the loaded package does not have sensitive information. That was the problem.

Application app = new Application();

app.PackagePassword = "test";

Package package = app.LoadFromSqlServer(@."PackTest", "Circle-DotNet57", "ssisdeveloper", "circle_123", null);

//package.ProtectionLevel = DTSProtectionLevel.EncryptSensitiveWithPassword;

//package.PackagePassword = "test";

DTSExecResult result = package.Execute();

Thank you

Subin