SSIS Error: transport-level error...
- From: JJ of Eugene OR <JJwithQuestions@xxxxxxxxxxxxxxxxx>
- Date: Fri, 26 May 2006 13:14:02 -0700
Fellow SSIS Users:
I have a package that (among other things) does these steps:
1) Backup a database. Let's call it: DatabaseStage
2) Run stored procedures which change DatabaseStage
3) Kill connections to DatabaseStage (so that the next step will work)
4) Restore DatabaseStage
5) Run stored procedures which once again change DatabaseStage
This is a major over simplification, but it gives the general idea of what
is happening. The point is that when the package gets to step 5 (after step
4 runs great), I got the following error:
"[Execute SQL Task] Error: Executing the query "Exec proc_MyProc
'InputInfo', @ai_ReturnCode OUTPUT" failed with the following error: "A
transport-level error has occurred when sending the request to the server.
(provider: Shared Memory Provider, error: 0 - No process is on the other end
of the pipe.)". Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly, or
connection not established correctly. "
It sounds like a connection error. SSIS may loose the connection to the
database somewhere in steps 3 or 4 and it seems unable to re-connect to the
database in step 5. But the ADO.NET connection has a property called
'RetainSameConnection'. It is my understanding that when the
RetainSameConnection property is set to the value 'false' (which is the
default), it means that the SSIS package will attempt to re-connect each time
a task is run which uses that connection. So, I would expect the connection
to occur just fine in step 5, even if steps 3 and 4 'broke' connections to
DatabaseStage.
I did some research on the internet for this error message. I couldn't find
a reference to the exact same problem, but I found a blog by someone who had
a similar problem in another environment (SSMS). Louis Davidson's April 14
2006 blog said that he runs a stored proc to force users out of the system.
He goes on to say "The cost of this is that other connections to the server
for the affected database will be immediately severed, so the first time you
try something you will have to reconnect. In SSMS you will likely get [the]
error the first time you try something since the tools don't realize that
they have been cut off. ... But the second time will automatically
reconnect."
The last line gave me an idea. Here's what I did: I added a step between
steps 4 and 5, call it step 4.5. In step 4.5 I ran an Execute SQL task. The
SQL statement was a 'dummy' select query against a small table in the
database. The entire point of task/step 4.5 was simply to get SSIS to
attempt a connection on a step that doesn't matter so that the next time SSIS
tried to connect to the database in step 5, there would not be a problem. I
expected step 4.5 to fail so I changed the precedence constraint between 4.5
and 5 to 'completed' - in other words, I told it to run step 5 even if step
4.5 failed.
This seemed to work! Well, it worked in the sense that the package ran to
the end without stopping prematurely. The problem was that my overall
package was still reporting 'failure', because it had the 'transport-level
error' error at step 4.5. So, I followed the advice of the error message
(nice message to be so helpful) and changed a property on the Execute SQL
task in 4.5: I changed the MaximumErrorCount from the default value of 1 to a
value of 2. The package itself also has a MaximumErrorCount property, but
luckily I found the property at the task-level, which is a safer way to go.
OK. So, now the package is working. My slight concern is that I'm still
just guessing on what the real problem was. It feels creepy/unsafe to me to
change the MaximumErrorCount for a production environment even if it is only
on my dummy query task. Does anyone have an opinion on this? Is there a
better way to handle the problem and force SSIS to reconnect on it's own?
Does anyone have an explanation as to why the 'RetainSameConnection' property
(since it is set to false), doesn't make the package reconnect again by
itself?
This is not a major issue. I have a perfectly fine work-around. I would be
interested though, in hearing ideas and/or explanations for what is going on.
Mostly, I though that by posting this note, I might help someone else who
runs into the same problem.
- JJ
.
- Follow-Ups:
- RE: SSIS Error: transport-level error...
- From: "privatenews"
- RE: SSIS Error: transport-level error...
- Prev by Date: 2005 Integration services configuration problem???
- Next by Date: Re: 2005 Integration services configuration problem???
- Previous by thread: 2005 Integration services configuration problem???
- Next by thread: RE: SSIS Error: transport-level error...
- Index(es):
Relevant Pages
|
Loading
