Why do I keep losing connections in my SSIS package?

Question

I have a very simple SSIS package where I take data from a view in Oracle, minimally transform it with a script component, and load that into a table in SQL Server 2008.

Both my DB Source and my DB Destination components have connected successfully with their respective databases earlier today.

Now I get the following error, while debugging in BIDS (VS 2008).

Data Flow Task: ADO NET Destination has failed to acquire the connection {046C4CA9-2BBF-46FD-9FEA-06F1B173BB35}. The connection may have been corrupted.

I fixed a similar one with my Oracle connection by deleting the connection component and creating a new one. The ADO.NET connection appears to be more stubborn. I have made the User Name and Password part of my configuration for deployment. Is BIDS typically this unstable an environment to work in? I need reliable connnections so that I can fix the other bugs I've created. Thanks in advance for any help.

Answer 1

Try using OLE DB for all connections unless you have a specific need for ADO.NET... not sure why, but it always seemed more reliable for me...

Answer 2

You might be better off if you just write your own load in C# or C++ - you might spend slightly more time writing code, but you will spent much less time chasing elusive bugs and googling for workarounds.

Edit: to those who downvoted, would you mind explaining your reasons?

Answer 3

In your DataFlow Task in control flow view set in properties tab the value for delay Validation to true.

Answer 4

set delay validation = true in the properties window worked for me