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.
| Author |
Topic |
|
nman47
Starting Member
8 Posts |
 Posted - 2009-12-12 : 16:37:43
|
| I have an SSIS package which imports or loads data from a flat TEXT file to a SQL 2008 table. But when I try to run the package, am getting the following errorSSIS 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".The weird part is it loads about 6000 rows and fails. Most of the blogs I’ve read state that the error code relates to permission but it does not make sense why it would load a portion of the file and then fail. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-12-12 : 16:44:20
|
| Open the text file and see what's going on at the row. If you can't find the bad row easily, you'll probably need to resort to bcp.exe or BULK INSERT if SSIS doesn't have a way of specifying which rows to import. With bcp.exe and BULK INSERT, you can specify the first and last rows to import so you can narrow the file down to the problematic row(s).Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
nman47
Starting Member
8 Posts |
Posted - 2009-12-13 : 18:41:40
|
| I tried loading a different file but with the same schema. From this new file, 18036 records were loaded from a total of 35859 records. So, I split the file after 18036 records and tried loading just the second half and it still failed.Next thing I tried was create a new table with the exact number of fields and field names. The same file loaded without any issue. The only difference being is that my original table is partitioned and the new table I created is not. Could that have cause a problem? I have loaded the same table with ober 80 million records before and I didn't have any problems so not sure what could be causing the problm. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
nman47
Starting Member
8 Posts |
Posted - 2009-12-13 : 20:03:02
|
| It is just a regular table partition and am using a partitioning column to be a datetime field. Another change I which I had forgotten to mention on my post was that I had dropped an unused field (auto incremented value) from the table which I had added a week before. Since we were limited on space, I had removed it. And I had gone into the SSIS package to update the it. Thereon after, the load stopped failing. So, I am guessing if the removal of the field screwed of the my process somewhere.I also tried partitiong the new table using the same partitioning scheme and function as the original table and then tried loading the file. This also loaded fine with no issues.So, now I am in the process of -- dumping the existing data from the original to a separate table. -- drop the original table.-- recreate the table using the same partioning scheme and function and see if the loading works.Hope this works. |
|
|
|
nman47
Starting Member
8 Posts |
Posted - 2009-12-14 : 11:43:38
|
| Just as I thought, dropping the table and recreating it seems to have resolved the issue. I was able to use the same file with the same SSIS package with no issues. So, I wonder if it is bug on SQL 2008 where the SSIS package doesn't sync up or something with a table where one of fields get dropped using T-SQL. But do thank you for your comments!. |
|
|
|
|
|
|
|
|
Error code: 0x80004005 when importing data