1

SQL Server 05 SP3, Windows Server 2008 SP1 I have an SSIS package where I’m taking an excel file, and trying to import the data to a dimension table in my data warehouse. I have an execute sql task to truncate the table (this is for initial load), and excel source, data conversion, and sql destination. My table has a primary key with identity on. The SSIS package errors on the sql destination. I’m getting these errors… [DataWarehouse [66]] Error: The attempt to send a row to SQL Server failed with error code 0x80004005. [DataWarehouse [66]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: ““Microsoft SQL Native Client”” Hresult: 0x80040E14 Description: ““Cannot fetch a row from OLE DB provider ““BULK”” for linked server “”(null)””.“”. An OLE DB record is available. Source: ““Microsoft SQL Native Client”” Hresult: 0x80040E14 Description: ““The OLE DB provider ““BULK”” for linked server “”(null)”” reported an error. The provider did not give any information about the error.“”. An OLE DB record is available. Source: ““Microsoft SQL Native Client”” Hresult: 0x80040E14 Description: ““Cannot bulk load because the maximum number of errors (10) was exceeded.””. An OLE DB record is available. Source: ““Microsoft SQL Native Client”” Hresult: 0x80040E14 Description: ““The bulk load failed. Unexpected NULL value in data file row 11, column 12. The destination column (DENSITY) is defined as NOT NULL.””. An OLE DB record is available. Source: ““Microsoft SQL Native Client”” Hresult: 0x80040E14 Description: ““The bulk load failed. Unexpected NULL value in data file row 10, column 12. The destination column (DENSITY) is defined as NOT NULL.””. An OLE DB record is available. Source: ““Microsoft SQL Native Client”” Hresult: 0x80040E14 Description: ““The bulk load failed. Unexpected NULL value in data file row 9, column 12. The destination column (DENSITY) is defined as NOT NULL.””. An OLE DB record is available. Source: ““Microsoft SQL Native Client”” Hresult: 0x80040E14 Description: ““The bulk load failed. Unexpected NULL value in data file row 8, column 12. The destination column (DENSITY) is defined as NOT NULL.””. An OLE DB record is available. Source: ““Microsoft SQL Native Client”” Hresult: 0x80040E14 Description: ““The bulk load failed. Unexpected NULL value in data file row 7, column 12. The destination column (DENSITY) is defined as NOT NULL.””. An OLE DB record is available. Source: ““Microsoft SQL Native Client”” Hresult: 0x80040E14 Description: ““The bulk load failed. Unexpected NULL value in data file row 6, column 12. The destination column (DENSITY) is defined as NOT NULL.””. An OLE DB record is available. Source: ““Microsoft SQL Native Client”” Hresult: 0x80040E14 Description: ““The bulk load failed. Unexpected NULL value in data file row 5, column 12. The destination column (DENSITY) is defined as NOT NULL.””. An OLE DB record is available. Source: ““Microsoft SQL Native Client”” Hresult: 0x80040E14 Description: ““The bulk load failed. Unexpected NULL value in data file row 4, column 12. The destination column (DENSITY) is defined as NOT NULL.””. An OLE DB record is available. Source: ““Microsoft SQL Native Client”” Hresult: 0x80040E14 Description: ““The bulk load failed. Unexpected NULL value in data file row 3, column 12. The destination column (DENSITY) is defined as NOT NULL.””. An OLE DB record is available. Source: ““Microsoft SQL Native Client”” Hresult: 0x80040E14 Description: ““The bulk load failed. Unexpected NULL value in data file row 2, column 12. The destination column (DENSITY) is defined as NOT NULL.””. An OLE DB record is available. Source: ““Microsoft SQL Native Client”” Hresult: 0x80040E14 Description: ““The bulk load failed. Unexpected NULL value in data file row 1, column 12. The destination column (DENSITY) is defined as NOT NULL.””. Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component ““DataWarehouse”” (66) failed with error code 0xC02020C7. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. Profiler shows this: SQL:BatchCompleted select collationname(0x0904D00034)

2

Well right now I’m working in a development environment. I’m new to data warehousing, so if I say something wrong, correct me. We’re in the middle of converting from our old system to SQL Server backend. My manager is not converting some history data, which I’m going and pulling into a data warehouse myself (Customer/Vendor invoicing, and some other things)… Right now, I’m just working on a process for loading all of this history into the appropriate dimension and fact tables that I’ve created. Right before we cut over, I’ll go through this process so that I have all history in my data warehouse. In the next few weeks, I’ll have another process that only INSERTS the NEW records into the data warehouse. I have a ways to go but I’m getting there.

3

Here is what I suggest.

  1. Use the SSIS import/export wizard to build a package from Excel to
    Staging table in db.
  2. Call (execute) the new package from your current SSIS package
  3. Write a data flow to take data from staging to final destination.
    Kalman Toth, SQL Server & Business Intelligence Architect
    SQL Server 2008 Training - SQLUSA: sqlusa.com - sqlusa Resources and Information.
4

You have a destination column (DENSITY) that is defined as NOT NULL and your source file has null values for that column. If you have rights to modify your destination table you could change the constraint on that column to accept nulls, otherwise you can use a DERIVED COLUMN TRANFORMATION to convert those null values into something else.

De: bombtrk via sql-server-l [mailto:sql-server-l@Groups.ITtoolbox.com]
Enviado el: mircoles, 11 de noviembre de 2009 9:37
Para: Miguel Angel Centellas Vacaflor
Asunto: [sql-server-l] SSIS error (trying to import from excel to sql)
[http://userimages.toolbox.com/user/b_1206390.jpg]
Posted by bombtrk
on Nov 11 at 8:38 AM
SQL Server 05 SP3, Windows Server 2008 SP1
I have an SSIS package where I’m taking an excel file, and trying to import the data to a dimension table in my data warehouse. I have an execute sql task to truncate the table (this is for initial load), and excel source, data conversion, and sql destination. My table has a primary key with identity on. The SSIS package errors on the sql destination. I’m getting these errors…
[DataWarehouse [66]] Error: The attempt to send a row to SQL Server failed with error code 0x80004005.
[DataWarehouse [66]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80040E14 Description: “Cannot fetch a row from OLE DB provider “BULK” for linked server “(null)”.”. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80040E14 Description: “The OLE DB provider “BULK” for linked server “(null)” reported an error. The provider did not give any information about the error.”. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80040E14 Description: “Cannot bulk load because the maximum number of errors (10) was exceeded.”. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80040E14 Description: “The bulk load failed. Unexpected NULL value in data file row 11, column 12. The destination column (DENSITY) is defined as NOT NULL.”. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80040E14 Description: “The bulk load failed. Unexpected NULL value in data file row 10, column 12. The destination column (DENSITY) is defined as NOT NULL.”. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80040E14 Description: “The bulk load failed. Unexpected NULL value in data file row 9, column 12. The destination column (DENSITY) is defined as NOT NULL.”. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80040E14 Description: “The bulk load failed. Unexpected NULL value in data file row 8, column 12. The destination column (DENSITY) is defined as NOT NULL.”. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80040E14 Description: “The bulk load failed. Unexpected NULL value in data file row 7, column 12. The destination column (DENSITY) is defined as NOT NULL.”. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80040E14 Description: “The bulk load failed. Unexpected NULL value in data file row 6, column 12. The destination column (DENSITY) is defined as NOT NULL.”. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80040E14 Description: “The bulk load failed. Unexpected NULL value in data file row 5, column 12. The destination column (DENSITY) is defined as NOT NULL.”. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80040E14 Description: “The bulk load failed. Unexpected NULL value in data file row 4, column 12. The destination column (DENSITY) is defined as NOT NULL.”. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80040E14 Description: “The bulk load failed. Unexpected NULL value in data file row 3, column 12. The destination column (DENSITY) is defined as NOT NULL.”. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80040E14 Description: “The bulk load failed. Unexpected NULL value in data file row 2, column 12. The destination column (DENSITY) is defined as NOT NULL.”. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80040E14 Description: “The bulk load failed. Unexpected NULL value in data file row 1, column 12. The destination column (DENSITY) is defined as NOT NULL.”.
Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “DataWarehouse” (66) failed with error code 0xC02020C7. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Profiler shows this:
SQL:BatchCompleted select collationname(0x0904D00034)

5

I’m sorry guys, I got it…
I reread through those errors and seen that I have NULL values in the excel file… Sorry for that.