August 12, 2011 at 8:48 am
Hi all,
Have a bit of a tricky situation to deal with. I have a package, which, at the end of its execution, calls an Execute Package task to email the results.
In this Execute Package task, the results are emailed to the necessary people, and then another Execute Package task is run, to log the results into the database.
The problem that I'm getting is that, for both of the sub-packages, I'm getting this error:
"Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error Code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe. ".
I'm guessing that the error is because both sub-packages are using a connection manager to the same target server and database.
From what I've read, the solution is to turn on Named Pipes, though I don't really know if I believe that, because the first connection is working fine, it only seems to be the problem with the two sub-packages trying to use the same connection.
So I guess I have two questions. First, will turning on Named Pipes fix the problem? And second, assuming it does not, is there any way I can kill the connection manager at the end of the first package, so that when the second package starts, it doesn't have that conflict? I can't see any option to do so, but maybe there's a WMI task or a .NET script or command I can run that would do this?
August 12, 2011 at 9:20 am
Actually I should add something to this -
When I run the package from Visual Studio, it works completely fine. It's only when I try running the package by executing it directly from the dtsx file, or by doing it through a SQL Server Agent job, that it fails.
August 12, 2011 at 9:38 am
I don't think I have all your answers but I do have some.
I don't believe named pipes can fix this, it is using the shared memory provider which is a much better option than named pipes.
As far as I know, connection managers are not shared between packages, they might have the same name and even internal GUID but they don't have the same connection.
Is the connection option "retain same connection" on the connection manager set to true?
Have yo looked at these links:
http://support.microsoft.com/kb/933835
http://stackoverflow.com/questions/634265/ssis-sql-native-client-error-cant-diagnose-cause
Could you have a timing problem, maybe add a 5 seond wait at the end?
CEWII
August 12, 2011 at 10:11 am
Well, I've found the solution. Though I'm not entirely sure on *why* this solution works.
When messing around with the project, and trying to open it on my machine and then on the machine which was using it, I realised that I had set EncryptSensitiveWithUserKey. Thus when opening on the other machine, it was failing because of the lack of password.
Now, the weird part is, even though it was failing when opening the package in visual studio, it was still successfully running when executing it directly from the DTSX file.
I changed the option to EncryptAllWithPassword instead, and then tried running it from SQL Server Agent. It was a bit finicky with remembering the password, but after a couple of tries I finally got it to run. And voila! worked like a charm.
My guess is that the reason it was failing on the sub-packages is because for whatever reason, the main package accepted the UserKey, while the sub-packages did not. As a result, they could not connect to the database.
August 12, 2011 at 11:11 am
Great, thanks for the update.
CEWII
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply