Running SQL Server 2005 Enterprise 64 bit Windows 2003 SR2 64 Bit The issue I'm having is I have a standard update insert process I'm building using a customer table that has over 5 million rows in it. The setup is looking at the customer key and looking across to another server, both servers are using SA logins. (Yes, I know this is not the prefered way but this is how the firm I'm consulting for has it setup and will not use domain logins.) In the lookup I have it doing a redirect to an ole db destination with a maximum commit rows of 10000. The OLD DB Command has the following SQL: UPDATE [dbo].[DimCustomer] SET [BranchKey] = ? ,[PriceColumnKey] = ? ,[CustomerName] = ? ,[CustomerNumber] = ? ,[FirstSaleDateKey] = ? ,[LastSaleDateKey] = ? ,[PrimaryMarket] = ? ,[City] = ? ,[State] = ? ,[SalesRep] = ? ,[Warehouse] = ? WHERE CustomerKey = ? The fields are mapped correctly to the parameters created. The error that I get when running the package for this large set up data is as follows: Error: 0xC0202009 at Data Flow Task, Dim Customer Updates [197]: 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: "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". 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: "Named Pipes Provider: No process is on the other end of the pipe. I have looked all over the web I have found the following microsoft kb: http://support.microsoft.com/kb/2006769 This didn't work! I have also was looking at the SQL Profiler and the only error I get in there is Changed database context to 'CaptainSCR_Dev'. and change of language. I see that is does run the following SQL when run. exec sp_executesql N'UPDATE [dbo].[DimCustomer] SET [BranchKey] = @P1 ,[PriceColumnKey] = @P2 ,[CustomerName] = @P3 ,[CustomerNumber] = @P4 ,[FirstSaleDateKey] = @P5 ,[LastSaleDateKey] = @P6 ,[PrimaryMarket] = @P7 ,[City] = @P8 ,[State] = @P9 ,[SalesRep] = @P10 ,[Warehouse] = @P11 WHERE CustomerKey = @P12' Took this part out...... I have also check to make sure that the server does allow remote connection and the timeout is set correctly for the server, and have also did change the network packet size from the default 4089 to the 32K.... As the kb mentioned with a restart of the sql server between the changes, and a remote login validation. I have no problems with doing this on smaller tables with the same connections in the same SSIS package. Also, the lookup is using a full cache lookup I have tried none and partial and this make the package data flow fail in other area of either the oringination or the file OLE DB destination. Any help would be greatly appreciated on this, for I'm at a loss of this one and have not run into this one before.

Why not trying to switch to doing the update with exec sp_executesql ? Or I think better use a stored procedure.Arthur My Blog

That is the way sql server is doing. That was an output from the SQL profiler to see what is was doing when it was executing the statment on the server from the OLE DB Command.

Then this is what I thought: you have simple connectivity issue VAWolf. Could be security related. First step is to try to connect from the same machine where you execute SSIS using the same account by the means of UDL. Test the connection is it works.Arthur My Blog

The UDL I'm sorry is not making since to me is this a C# program?

Ok I found another way of make a UDL by renaming a text file and double clicking on it to configure it. I was able to connect without any issue using a UDL file. Next thoughts?

I've seen this problem when using Caching on the lookup. Try turning of caching it'll make it go much slower though.

I did try that and it causes the whole process to error out also.

Have you tried a smaller comitt size? I know server 2003 isn't as good as 2008 when it comes to packets, it also could just be a connectivity issue still, is the network interface dropping any packets?

How do you change the commit size on a OLE DB Command function I tried the Memory size limit in the lookup and give me the same error.

I checked by pinging the server and no network packets were being lost in the ping.

Try to avoid using the lookup and do it through T-SQL.Arthur My Blog

I would look at the interface itself. It should tell you if there are dropped packets. Your network people can also look on the switch side of things.

This is a good point - if all the data is in the same database a MERGE statement could be a good option here as well.

The problem is that this is a small company and they do not have network people to say. I have full control of the box, I have tried a linked server and it seems to work for the OLE DB command but the lookup is causing the issue. What gets me that this is a built in feature and should handle 5 million records.

Could you get me a little more information from your log?

The work around I have done is to do a table copy and then replace the current production table with an SP_Rename. But the information from the log is shown above. That is the exact error I get through the SSIS package.