Date: 06/20/07 (SQL Server)    Keywords: sql, web, microsoft

SSIS.

Can anyone please explain to me what the above error means and how to correct it? Microsoft's error reference is its usual helpful self, and it seems no one has gotten this error (or asked about it anywhere on the web) in my particular situation. Before someone suggests it, no, I cannot permanently change the type of output.

I'm trying to take the restuls of a stored procedure and export them to Excel in a data flow task. This is a common thing at my workplace, and we have hundreds of them that work correctly. Every once in a while, though, one of them starts dying, and it always throws the error in the subject line, invariably preceded by a DTS_E_OLEDBERROR. The going wisdom for people using OLEDB or SQL SERVER destinations is that it actually is what it says it is-- there's a problem with a transformation. So I figured I'd give that a shot with the Excel Destination. I switched it over to Fast Load (in order to be able to get to the MaxBatchSize parameter, to set it to 1-- I don't usually use it), dropped all the columns but one, and tried to execute the task with that single mapping, of a four-byte signed integer. I redirected the error output to a text file, but it's a four-byte signed integer, how can anything go wrong?

Here's a piece of the text file that comes out.

,,,,,,0,,,,,,,,,,,,-1071607685,-1
,,,,,,0,,,,,,,,,,,,-1071607685,-1
,,,,,,1,,,,,,,,,,,,-1071607685,-1
,,,,,,2,,,,,,,,,,,,-1071607685,-1
,,,,,,2,,,,,,,,,,,,-1071607685,-1
,,,,,,0,,,,,,,,,,,,-1071607685,-1
,,,,,,0,,,,,,,,,,,,-1071607685,-1
,,,,,,0,,,,,,,,,,,,-1071607685,-1

Obviously, the problem is not with four bytes being too small to hold the integers that are failing out on me. The brilliant -1071607685 error code translates to "no status is available." Which tells me less than nothing.

I have deleted and recreated the destination spreadhseet to make sure there's no corruption in a template or anything. I've tried this single-mapping approach with every column (Unicode string, four-byte signed int, and currency), and, of course, with all columns. I've read every page I can find on the internet about DTS_E_OLEDBERROR, DTS_E_INDUCEDTRANSFORMFAILUREONERROR, and -1071607685, and tried every applicable suggestion. It works wonderfully when I send the output to a flat file destination, but as I said previously, that is not an option that's available to me in production, only in testing-- I must use the Excel Destination for this client.

I don't even know where to begin looking at this point. I've exhausted all my possibilities. Anyone have any other ideas? Thanks.

Source: http://community.livejournal.com/sqlserver/60479.html