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

Hello,

This issue seems to be normal since the original connection killed is not
aware by the following steps. I think the workaround you provided might be
the only option currently and this situation has been reported to the
proper channel. Thank you for your input and this will surely benefit the
community.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===



This posting is provided "AS IS" with no warranties, and confers no rights.