Flat File to SQL Server Causing HRESULT: 0x80004005
I have a new package I was working on that was simply a copy of a working one. However in this new import the source is an Oracle database to a CSV. The CSV is then imported to a SQL Server. You may ask why not go directly? Answer is because
it is incredibly faster to do the intermediate file for some reason. The transfer of records from the CSV to the database makes it through the first two batches (roughly 10,000) records and then it dies with the following error. Anyone know what
might be causing the problem?
SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred.
Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005
Description: "Unspecified error".
I was able to pick out some random rows from the CSV and add them to an insert statement manually and it worked. I am sure the problem lies in a specific row having some kind of invalid data. Does anyone know how to find out without trying to go
through 100k rows?
March 28th, 2011 11:53pm
check the below documentation for this issue.
http://blogs.msdn.com/b/dataaccesstechnologies/archive/2009/11/09/ssis-error-code-dts-e-oledberror-an-ole-db-error-has-occurred-reasons-and-troubleshooting.aspx
Free Windows Admin Tool Kit Click here and download it now
March 28th, 2011 11:56pm
The raw file give an error when I try to attach the Oracle source. It says the source is returning over 130k rows and then gives an HRESULT. The CSV creates in only a few seconds and imports just as fast, typically. This is the first
import problem like this I have had in all my SSIS packages.
March 29th, 2011 8:34am
I was able to fix the problem when I made all the fields in the destination table nullable. I now need to go through the columns and figure out where the NULL field is that wasn't supposed to be. Why the error did not reflect a null field and
simply said "Unspecified error" I have no idea.
Free Windows Admin Tool Kit Click here and download it now
March 29th, 2011 8:52am
For those interested in finding out which columns contain null values to better set the nullable fields here is a query. It was written in SQL Server 2008 but should work in 2005.
DECLARE @ColumnName varchar(100)
DECLARE @Command varchar(max)
DECLARE @TableName varchar(100)
SET @TableName = <TABLE NAME HERE>
DECLARE C_COLUMNS CURSOR FOR
SELECT C.NAME
FROM SYS.TABLES T
JOIN SYS.COLUMNS C ON T.OBJECT_ID = C.OBJECT_ID
WHERE T.NAME = @TableName
OPEN C_COLUMNS
FETCH NEXT FROM C_COLUMNS INTO @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Command = 'IF (SELECT COUNT(*) FROM [' + @TableName + '] WHERE [' + @ColumnName + '] IS NULL) > 0 BEGIN SELECT ''' + @ColumnName + ''' END'
EXEC(@Command)
FETCH NEXT FROM C_COLUMNS INTO @ColumnName
END
CLOSE C_COLUMNS
DEALLOCATE C_COLUMNS
March 29th, 2011 9:15am
If you are using the CSV is an intermediate step, wouldn't it be much easier/faster I've you used the RAW file Destination and Source instead? That file format is native to SSIS, so it will parse the data much faster.MCTS, MCITP - Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
March 29th, 2011 9:33am