Wondering if anyone can help with a strange exception thrown while using the SqlBulkCopy class. I am using the class to transfer records from a DataTable in memory (approx 11,000 rows) into a SQL Server 2005 table.
Initially, the WriteToServer method was timing out a la KB913177 (http://support.microsoft.com/default.aspx/kb/913177), however I downloaded the hotfix, which eliminated this issue.
Now, I get a new exception thrown, as follows:
System.Data.SqlClient.SqlException: OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].Power_Avg'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)
I can't see anything wrong with the data I have. The column "Power_Avg" is of type "float". When forming the DataTable, I cast my data to float expcilitly in C#.
Other things to know:
- I am using SQL Server Express (2005)
- This same code works fine with SQL Server 2000 (MSDE)
- My code makes all modifications inside a single transaction of type "Snapshot" (I have activated SNAPSHOT READ COMMITTED in the database)
- I have not activated MARS in the connection
Any ideas / suggestions?
- Moved by Bob BeaucheminMVP Thursday, November 10, 2011 5:17 PM Thanks, Ryszard. I'm going to move this to forum where folks would be more likely to benefit from your finding. (From:.NET Framework inside SQL Server)
I'll answer my own question...
It turned out to be that there were a couple of records in the DataTable that had float values of float.NaN. These were the result of a divide by zero - which for some reason didn't throw an error in my C# code - presumably the float datatype is happy to translate a divide by zero into float.NaN.
Of course, SQL Server 2005 is not so easy to please, and it was this that was breaking the SqlBulkCopy.WriteToServer method. It is a pity that the exception thrown wasn't a little more helpful. References to OLE DB providers, 'STREAM's and linked servers was not exactly useful in trouble shooting.
Watch out for this exception!
Interesting that before the hot fix, this same input data was causing the bulk copy to timeout - perhaps we know a little more about what the hotfix does...
Solution for varchar column.
Your case is related to float, and my case is related to varchar column.
If your data does not contain other language, you may try to:
1) set the "Auto Translate" to true in the destination connection.
2) or, you may set the destination column to nvarchar
3) or else, you may try to bound the output length by using left([col_nam], <<field length>>) in your source query to remove all trailing unknown char. e.g. select left(haha, 100) from hohoho
**sth interesting if using 3. as the "left" is a must for the SSIS to work, you may think that some data in destination column should have lenght 100. However, you can find none. Comparing the lenght of data in source and destination column can assure that the data is not manipulated
Hi, I am absolute beginer so have no idea if what I've done makes any sense but it seems to solve the problem so I feel like to share with others: For some reason destination table( created automatically with New button in Destination Editor) problematic column had real data type, not float as I specified in input Flat FIle Connection Manager. After changing it to float everything was fine. :)