Transfer SQL Server Objects Task using SMO connection fails on a large table

Question

I am copying 120 tables our production database to another location using Transfer SQL Server Object task. I have also tried Script task with VB.NET Script. SQL Server 2005 is the version.

All the tables get copied except one table = the error I get is -"The file exists". The table stores files like .doc, .ppt, .xls in a field of data type image. The size of the table is 1,156.227 MB. Around 5000 records.

This package was running successfully for almost a yr before we started to get this error. When the task tries to copy the above table I get the error [Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR : errorCode=-2147024816 description=The file exists. helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".

We used SQL Profiler too to debug the failure, but couldn't see the bulk insert statement for this table. All the other tables had the bulk insert statement. The table gets created successfully, it's the copying of the data that fails.

Is there any way to troubleshoot this or another way of copying this table.

Answer 1

As the packages runs and caches files you might have created duplicates from previous runs I would check %SystemRoot%\Temp or %user%\Documents & Settings\Temp\ to see if if there is duplicate files or more than 65,535 files. You might need to use filemon to see where files are being cached