May 26, 2018 at 8:26 pm
This is my table
CREATE TABLE [dbo].[Geo_Property]
(
[Property_Name] NVARCHAR(1000) NOT NULL ,
[C_Assignment_Num] INT NOT NULL ,
[Property_Type_Name] NVARCHAR(1000) NOT NULL ,
[Property_Description] NVARCHAR(1000) NOT NULL ,
[In_Game_Description] NVARCHAR(1000) NOT NULL ,
[Partner_Fee_Amount] FLOAT NOT NULL ,
[NW_Latitude_Boundary] FLOAT NOT NULL ,
[NE_Latutude_Boundary] FLOAT NOT NULL ,
[SW_Latitude_Boundary] FLOAT NOT NULL ,
[SE_Latitude_Boundary] FLOAT NOT NULL ,
[NW_Longitude_Boundary] FLOAT NOT NULL ,
[NE_Longitude_Boundary] FLOAT NOT NULL ,
[SW_Longitude_Boundary] FLOAT NOT NULL ,
[SE_Longitude_Boundary] FLOAT NOT NULL ,
[Partner_Fee_Type_Code] NVARCHAR(1000) NOT NULL ,
[Geo_Partner_Full_Name] NVARCHAR(1000) NOT NULL ,
[P_Assignment_Num] INT NOT NULL ,
CONSTRAINT [PK_Geo_Property] PRIMARY KEY CLUSTERED ([Property_Name] ASC,
[C_Assignment_Num] ASC,
[Property_Type_Name] ASC),
CONSTRAINT [FK_149] FOREIGN KEY ([Partner_Fee_Type_Code])
REFERENCES [dbo].[Partner_Fee_Type]([Partner_Fee_Type_Code]),
CONSTRAINT [FK_163] FOREIGN KEY ([Geo_Partner_Full_Name])
REFERENCES [dbo].[Geo_Partner_Info]([Geo_Partner_Full_Name]),
CONSTRAINT [FK_173] FOREIGN KEY ([Property_Type_Name])
REFERENCES [dbo].[Property_Type]([Property_Type_Name]),
CONSTRAINT [FK_177] FOREIGN KEY ([C_Assignment_Num])
REFERENCES [dbo].[Current_Assignment]([C_Assignment_Num]),
CONSTRAINT [FK_183] FOREIGN KEY ([P_Assignment_Num])
REFERENCES [dbo].[Potential_Assignment]([P_Assignment_Num])
);
GO
It is associated with several other tables as can be seen via the constraints and clustered primary key area.
The error I am getting during import is as follows
- Copying to [dbo].[Geo_Property] (Error)
Messages
* Error 0xc0202009: Data Flow Task 1: 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 11.0" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)
* Error 0xc020901c: Data Flow Task 1: There was an error with Destination - Geo_Property.Inputs[Destination Input].Columns[Property_Name] on Destination - Geo_Property.Inputs[Destination Input].
The column status returned was: "The value violated the integrity constraints for the column.".
(SQL Server Import and Export Wizard)
* Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.
The "Destination - Geo_Property.Inputs[Destination Input]" failed because error code 0xC020907D occurred, and the error row disposition on "Destination - Geo_Property.Inputs[Destination Input]" specifies failure on error.
An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
* Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - Geo_Property" (65) failed with error code 0xC0209029 while processing input "Destination Input" (78).
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.
(SQL Server Import and Export Wizard)
- Post-execute (Success)
Messages
* Information 0x4004300b: Data Flow Task 1: "Destination - Geo_Property" wrote 11 rows.
(SQL Server Import and Export Wizard)
My understanding of the three field composite key is that one or two of the fields can contain non-unique data, but the third field can not. That is why I added the third field, so that the combination of all 3 is always unique, but one or two are using duplicate data.
I am at a complete loss for how to determine what the problem is that is preventing the import.
What constraint is being violated? The property names are under 500 characters long. I even paired it down to a single record with a 20 character property name and I still get the error.
Any suggestions or advice would be greatly appreciated.
May 26, 2018 at 11:59 pm
Most likely some values are null.
try and create a new table without any of the constraints e.g. set all to null and do not have any of the fk's or the PK on it, load to that one, and then look at how the data looks after load.
That will help you identify exactly where the problem is.
May 27, 2018 at 1:54 am
Alternatively you could set the destination to redirect the row on error rather than have the package fail and then add a destination, perhaps another table, for the failed rows so that you can examine them separately.
...
May 27, 2018 at 11:19 am
Thanks. There is definitely something messed up with the spreadsheet import.
I ended up converting your advice into the form of a manual insertion script and that resolved the issue.
My gut feeling is that for some reason either the blank rows at the very end of the spreadsheet were also being imported OR there was some other malformation of some kind with the spreadsheet itself and that was the root of the problem.
I ended up copying the data into a script and then find/replaced the spaces with single quotes and comma to build the insert script and that was the faster way to resolution. There were only a couple thousand records so I got it done!
Thanks for your suggestion and advice!
May 29, 2018 at 7:57 am
When I import from Excel, I create a table with all the columns as nvarchar(255) NULL. This is the same data type as the cells. Once I have the data, I proceed to do things with it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply