Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  SSIS and Import/Export (2005)  Transfer SQL Server Objects - Error

Author  Topic  coalesce Starting Member 1 Post Posted - 2010-08-01 : 19:57:28 Every night, we 'refresh' around 500 tables using the Transfer SQL Server Objects task to copy objects from a database to another. This is done via a job scheduled to run in the early hours of the morning and there is no parallel jobs running. The problem is that the package fails at random times. However, when I run the job manually, everything works perfectly. I have enabled logging and it's giving this piece of information: ----- OnInformation,,,{E6196E86-0022-4A7F-8B17-B0D1BB8B4373},{19A4B14B-59EC-4049-93B5-5C91D91F8B51},14/07/2010 3:00:20 AM,14/07/2010 3:00:20 AM,1073935390,0x,Truncating table "ind_inducement" OnError,,,Transfer SQL Server Objects Task,{D69E0C65-4617-42D3-BDA8-7BDC17B8DEF8},{19A4B14B-59EC-4049-93B5-5C91D91F8B51},14/07/2010 3:06:42 AM,14/07/2010 3:06:42 AM,-1073548507,0x,Execution failed with the following error: "ERROR : errorCode=-1071636471 description=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: "Invalid character value for cast specification". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}". ----- I had a look at other times it failed, and it seems that this table "ind_inducement" is the main culprit. However, this table contain 33 rows of static data that do not change. It's more like a utility table where parameters are defined. The table has rows that contains NULL data. But so does other tables and they did not fail. So, I'm not too sure why the process is stuck 5 mins trying to truncate the table and after that giving me an "Invalid character value for cast specification" error. Any idea? The server is running the 64 bit version of SQL Server 2005.