|
Home > Archive > MS SQL Server DTS > June 2005 > Cannot execute a DTS that imports
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Cannot execute a DTS that imports
|
|
|
| Hello:
I have the following table:
CREATE TABLE LANGUAGE (
Language_ID_N smallint IDENTITY(1,1),
Culture_Name_C varchar(20) NOT NULL,
Culture_ID_C varchar(20) NULL,
ISO_Language_CD_C varchar(3) NULL,
Language_Country_Reg
ion_C varchar(50) NOT NULL,
Status_CD_C char(1) NULL
CONSTRAINT Status467
DEFAULT 'A'
CONSTRAINT Status_ACD467
CHECK
(CHARINDEX(UPPER(Sta
tus_CD_C), 'ACD') > 0),
Created_By_C varchar(20) NOT NULL,
Created_DT datetime NULL,
Updated_By_C varchar(20) NOT NULL,
Updated_DT datetime NULL,
CONSTRAINT XPKLANGUAGE
PRIMARY KEY CLUSTERED (Language_ID_N ASC)
)
When I created a DTS to import data into this table, I get the following
error:
"the number of failing rows exceeds the maximum specified. Insert error,
column 1 ('Language_ID_N', DBType_I2), status 10: Integrity violation;
attempt to insert NULL data or data which violates constraints.
Unspecified error."
I am stumped. Language_ID_N is set as an Identity column.
Please help.
Venki
| |
| Helge C. Rutz 2005-06-28, 8:24 pm |
| Hi venki,
"vvenk" wrote:
> Hello:
>
> I have the following table:
>
> CREATE TABLE LANGUAGE (
> Language_ID_N smallint IDENTITY(1,1),
> Culture_Name_C varchar(20) NOT NULL,
> Culture_ID_C varchar(20) NULL,
> ISO_Language_CD_C varchar(3) NULL,
> Language_Country_Reg
ion_C varchar(50) NOT NULL,
> Status_CD_C char(1) NULL
> CONSTRAINT Status467
> DEFAULT 'A'
> CONSTRAINT Status_ACD467
> CHECK
> (CHARINDEX(UPPER(Sta
tus_CD_C), 'ACD') > 0),
> Created_By_C varchar(20) NOT NULL,
> Created_DT datetime NULL,
> Updated_By_C varchar(20) NOT NULL,
> Updated_DT datetime NULL,
> CONSTRAINT XPKLANGUAGE
> PRIMARY KEY CLUSTERED (Language_ID_N ASC)
> )
>
> When I created a DTS to import data into this table, I get the
> following error:
>
> "the number of failing rows exceeds the maximum specified. Insert
> error, column 1 ('Language_ID_N', DBType_I2), status 10: Integrity
> violation; attempt to insert NULL data or data which violates
> constraints. Unspecified error."
>
> I am stumped. Language_ID_N is set as an Identity column.
could you please exactly describe how you import data to this table?
Did you assigned any values to this column? Which options are set for the
import?
Helge
| |
| Allan Mitchell 2005-06-28, 8:24 pm |
| How do the transformations look?
Can this article help?
Problems With IDENTITY() and the DataPump task.
(http://www.sqldts.com/default.aspx?293)
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"vvenk" <vvenk@discussions.microsoft.com> wrote in message
news:EAC74668-4E36-4749-8F02- 8EA538E9734B@microso
ft.com...
> Hello:
>
> I have the following table:
>
> CREATE TABLE LANGUAGE (
> Language_ID_N smallint IDENTITY(1,1),
> Culture_Name_C varchar(20) NOT NULL,
> Culture_ID_C varchar(20) NULL,
> ISO_Language_CD_C varchar(3) NULL,
> Language_Country_Reg
ion_C varchar(50) NOT NULL,
> Status_CD_C char(1) NULL
> CONSTRAINT Status467
> DEFAULT 'A'
> CONSTRAINT Status_ACD467
> CHECK
> (CHARINDEX(UPPER(Sta
tus_CD_C), 'ACD') > 0),
> Created_By_C varchar(20) NOT NULL,
> Created_DT datetime NULL,
> Updated_By_C varchar(20) NOT NULL,
> Updated_DT datetime NULL,
> CONSTRAINT XPKLANGUAGE
> PRIMARY KEY CLUSTERED (Language_ID_N ASC)
> )
>
> When I created a DTS to import data into this table, I get the following
> error:
>
> "the number of failing rows exceeds the maximum specified. Insert error,
> column 1 ('Language_ID_N', DBType_I2), status 10: Integrity violation;
> attempt to insert NULL data or data which violates constraints.
> Unspecified error."
>
> I am stumped. Language_ID_N is set as an Identity column.
>
> Please help.
>
> Venki
>
| |
|
| Helge:
The DTS has a ActiveX-script:
Function Main()
DTSDestination("Culture_Name_C") = DTSSource("Culture Name")
DTSDestination("Culture_ID_C") = DTSSource("Culture Identifier")
DTSDestination("ISO_Language_CD_C") = DTSSource("ISO Language")
DTSDestination(" Language_Country_Reg
ion_C") =
DTSSource("Language-Country/Region")
DTSDestination("Created_By_C") = "vvenk"
DTSDestination("Updated_By_C") = "vvenk"
Main = DTSTransformStat_OK
End Function
Enable identiy Insert is turned off; check constraint is checked.
I did have some unique indexes on this table but I dropped all these. Even
then, the DTS job fails.
Thanks for your help.
Venki
"Helge C. Rutz" wrote:
> Hi venki,
>
> "vvenk" wrote:
>
> could you please exactly describe how you import data to this table?
> Did you assigned any values to this column? Which options are set for the
> import?
>
> Helge
>
>
| |
|
| Allan
The DTS has a ActiveX-script:
Function Main()
DTSDestination("Culture_Name_C") = DTSSource("Culture Name")
DTSDestination("Culture_ID_C") = DTSSource("Culture Identifier")
DTSDestination("ISO_Language_CD_C") = DTSSource("ISO Language")
DTSDestination(" Language_Country_Reg
ion_C") =
DTSSource("Language-Country/Region")
DTSDestination("Created_By_C") = "vvenk"
DTSDestination("Updated_By_C") = "vvenk"
Main = DTSTransformStat_OK
End Function
Enable identiy Insert is turned off; check constraint is checked.
I did have some unique indexes on this table but I dropped all these. Even
then, the DTS job fails.
Thanks for your help.
Venki
"Allan Mitchell" wrote:
> How do the transformations look?
>
> Can this article help?
>
> Problems With IDENTITY() and the DataPump task.
> (http://www.sqldts.com/default.aspx?293)
>
>
>
> --
>
>
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
> www.konesans.com - Consultancy from the people who know
>
>
> "vvenk" <vvenk@discussions.microsoft.com> wrote in message
> news:EAC74668-4E36-4749-8F02- 8EA538E9734B@microso
ft.com...
>
>
>
| |
| Allan Mitchell 2005-06-28, 8:24 pm |
| What you have in here will not necessarily tell you that you have a problem.
Your column (IDENTITY()) may still this you are doing something with it as
per the article.
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"vvenk" <vvenk@discussions.microsoft.com> wrote in message
news:7E600562-C7A1-425D-B548- CF48225E2827@microso
ft.com...[color=darkred]
> Allan
>
> The DTS has a ActiveX-script:
>
> Function Main()
> DTSDestination("Culture_Name_C") = DTSSource("Culture Name")
> DTSDestination("Culture_ID_C") = DTSSource("Culture Identifier")
> DTSDestination("ISO_Language_CD_C") = DTSSource("ISO Language")
> DTSDestination(" Language_Country_Reg
ion_C") =
> DTSSource("Language-Country/Region")
> DTSDestination("Created_By_C") = "vvenk"
> DTSDestination("Updated_By_C") = "vvenk"
> Main = DTSTransformStat_OK
> End Function
>
> Enable identiy Insert is turned off; check constraint is checked.
>
> I did have some unique indexes on this table but I dropped all these. Even
> then, the DTS job fails.
>
> Thanks for your help.
>
> Venki
>
>
> "Allan Mitchell" wrote:
>
| |
|
| That's what gets me stumped!
"Allan Mitchell" wrote:
> What you have in here will not necessarily tell you that you have a problem.
> Your column (IDENTITY()) may still this you are doing something with it as
> per the article.
>
>
> --
>
>
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
> www.konesans.com - Consultancy from the people who know
>
>
> "vvenk" <vvenk@discussions.microsoft.com> wrote in message
> news:7E600562-C7A1-425D-B548- CF48225E2827@microso
ft.com...
>
>
>
| |
|
| Allan:
Aha! I saw that the identity column was in the list of destination columns.
When I deleted that and made sure that Enable Identity Insert was turned off,
it worked fine!
Thanks a lot for referring me to that article.
Venki
"Allan Mitchell" wrote:
> What you have in here will not necessarily tell you that you have a problem.
> Your column (IDENTITY()) may still this you are doing something with it as
> per the article.
>
>
> --
>
>
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
> www.konesans.com - Consultancy from the people who know
>
>
> "vvenk" <vvenk@discussions.microsoft.com> wrote in message
> news:7E600562-C7A1-425D-B548- CF48225E2827@microso
ft.com...
>
>
>
|
|
|
|
|