October 13, 2008 at 6:22 pm
We load our data warehouse nightly and everything was working for the last year. Recently the job copying the data from SQL Server to SQL Server started to fail at night. Rerunning the same job in the morning with the exact same data will work. Several nights in a row it works, it then fails, and we rerun the next morning with the same nightly data and it works.
The server is running the 64 version of SQL Server 2005. The data is not showing up in our error table because it is trying to log the inserted date and is dying saying the conversion of that date is invalid. Inserted date was a simple getdate() statement from the previous step.
Any thoughts on how to troubleshoot?
Message
Executed as user: SVR45\CluAdmin. ...0.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:02:46 AM Error: 2008-10-10 02:02:56.41
Code: 0xC0202009
Source: HDW_DFLT_LOB HDW_ERR_TBL [1660]
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". End Error
Error: 2008-10-10 02:02:56.44 Code: 0xC020901C
Source: HDW_DFLT_LOB HDW_ERR_TBL [1660]
Description: There was an error with input column "insertion_date" (2158)
on input "OLE DB Destination Input" (1673).
The column status returned was: "Conversion failed because the data value overflowed
the specified type.". End Error Error: 2008-10-10 02:02:56.44
Code: 0xC0209029
Source: HDW_DFLT_LOB HDW_ERR_TBL [1660] Description: SSI... The package execution fa... The step failed.
November 29, 2008 at 3:47 pm
Hi there, We have been having the same problem with ssis on sql Server 2005 64 bit version. Sometimes it fails with an invalid cast specification on a datetimefield. When i run the package again after it has failed it will work fine on the same data.
Is there a data conversion step in the package that fails?
We also have another weird problem which a data flow at one of our customers. This one doesn't fail. it just inserts empty ('') rows. The data flow has a sourcestep, which is based on a query. The query selects 6 hard coded rows. Select 'a' as code, 'the first letter' as description union select 'b' etc..
The package does not fail but eventhough hard coded just 6 blank rows are added to the table. We only notice because the database grows for a table that joins on the code field and when empty it gets multiplied by 6.
It seems to me sql server 64 bit has some ssis issues. I find many posts with the same problem.. That is the problem of the cast specification and not finding the solution.
Have you found a solution or a workaround?
Greetings,
P
November 30, 2008 at 5:01 pm
Opened up a case with M$ to assist. Hopefully, they will have a better troubleshooting methodology.
December 1, 2008 at 2:42 pm
I get the same errors ,please keep me posted when you find a solution.:unsure:
December 1, 2008 at 2:49 pm
Hi Tim,
I hope Microsoft can help too. We were also thinking about opening a case, but the problem is random and haven't been able to reproduce it.
Grts,
P
December 18, 2008 at 12:40 pm
I ran into a similar problem with SQL 2005 64 Bit, 64 GB memory with multiple instances. I verified the service account that SQL was running under had 'lock pages in memory enabled'
I would get an over flow error after running an ETL for an hour.
(Arithmetic overflow error converting nvarchar to data type numeric) When I would restart the server, the query would return good results until I reran the ETL process.
I enabled AWE (yes i know it says it ignored but other say it has it uses in locking memory pages) I also set the max server memory to 15 GB for this instance and re-ran my ETL. So far that has seemed to clear up the error. It appears that it was a memory issue.
my 2 cents worth....
January 22, 2009 at 12:35 pm
Did you find a resolution for this issue? Did MS help?
We are having a pretty much similar situation, and didn't want to reinvent the wheel..
January 23, 2009 at 5:39 am
I can tell you that we've not had an issue since limiting the memory and max parallel tasks on the 64Bit box. We are running multiple instances and it seems that you must lock pages in memory and set max memory in this situation.
February 4, 2009 at 2:14 pm
the invalid character for cast specification problem we had only seemed to be on Datetime columns.
first thing we did is remove the data conversion steps in some of the package which were converting varchar to nvarchar. The datetimefields were converted from datetime to datetime.... But removing the conversion to the same datatype did not solve the problem, it did happen a lot less.
It also happens when copying from oledb source to oledb destination without a data conversion step. When the source is not based on a query but just copies a table.
After removing the data conversions we also:
- switched on "Lock pages in memory"
- limited the max memory for sql server
- limited the number of parallel executing packages. We had many packages running at the same time.
Now we haven't seen the conversion error for a while (2 weeks) ... so far so good..
February 4, 2009 at 2:51 pm
Paul, thanks - all these mods you made makes sense. I haven't had a recurrence in a while, but now that I said it, you know what's coming.
But atleast I have a few things I can try out now, if it does happen.
June 9, 2009 at 10:39 am
Hi I am having a similar data conversion issue. I use ISNULL in SQL, replacing NULL with 0 (zero) and then trying to load the data into Oracle. It's complaining that I can't insert NULLS but I'm certain I"m not passing NULLS. I tried explicitly converting this using SSIS data conversion task but it still complains that I"m trying to insert NULL values.
I sure hope it's not a bug as I have limited alternatives to loading this data.
December 3, 2010 at 4:21 am
Hi,
We had this problem also and I found this article first which opened my eyes.
Then I searched for a solution of Microsoft.
I found it on technet. Hereby the url:
http://support.microsoft.com/kb/972498
Regards,
Frank
December 6, 2010 at 7:08 am
Thank you for the information. I'll consider this hotfix.
Colleen
June 29, 2011 at 6:42 am
I had a similar problem and it was due to the "LocaleId" property of my package which was set to English UK, whereas the server was set to US, changed my package to US and bingo it worked.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply