kkohl
asked on
DTS Package / Import Wizard
I think I am missing something really stupid here, but here goes...
SQL Server 2000
Windows Server 2003
Access 2003
Need to update - append - a huge table in an existing database.
created an Access DB that has a table with desired data.
Try to use the Import Data Wizard in EM and follow steps to transform the data in what appears to be correct.
Error out with:
The Task reported failure on execution.
The statement has been terminated.
Cannot insert the value NULL into column 'ROWID', table 'xxx'; column does not allow nulls. INSERT fails.
My question is how do I get the transformation to 'ignore' the ROWID and automatically update the ROWID with the correct sequential value???
I have missed something, because an import of data using Access itself works fine. SQL 'knows' that rowid is not supposed to be imported and just updates it in the proper order.
What have I missed?
SQL Server 2000
Windows Server 2003
Access 2003
Need to update - append - a huge table in an existing database.
created an Access DB that has a table with desired data.
Try to use the Import Data Wizard in EM and follow steps to transform the data in what appears to be correct.
Error out with:
The Task reported failure on execution.
The statement has been terminated.
Cannot insert the value NULL into column 'ROWID', table 'xxx'; column does not allow nulls. INSERT fails.
My question is how do I get the transformation to 'ignore' the ROWID and automatically update the ROWID with the correct sequential value???
I have missed something, because an import of data using Access itself works fine. SQL 'knows' that rowid is not supposed to be imported and just updates it in the proper order.
What have I missed?
So do you have ROWID defined as an identity column? If so, simply choose IGNORE from the column in the wizard....
So if you already have the package created, you can also go into the package designer and remove the column from the input and output columns...
ASKER
Yes, that is one of the things that puzzles me. I have the source column set for ignore for the destination column ROWID.
Actually, in the wizard, I am able to check all the transformations.
there are roughly 50 columns in the existing destination table.
Of the 50 columns, there are only 35 of pertinent data I am trying to import.
I have a data value for each column I wish to import (basically a data value for each column that does not allow a NULL)
In the transformation, I have verified each source data column to match up to the destination column as well as verified that <ignore> is set for each source column that I am not importing data for...
And yet, the wizard keeps trying to import a value for ROWID even though I have it set to ignore (actually, it is doing just that... ignoring the import, therefore it is trying to import a NULL value for it... hence the problem)
If I uncheck the Identity check box, it errors out with unable to duplicate a primary key.
Hope this makes some sense with what I am seeing.
Thanks much
Actually, in the wizard, I am able to check all the transformations.
there are roughly 50 columns in the existing destination table.
Of the 50 columns, there are only 35 of pertinent data I am trying to import.
I have a data value for each column I wish to import (basically a data value for each column that does not allow a NULL)
In the transformation, I have verified each source data column to match up to the destination column as well as verified that <ignore> is set for each source column that I am not importing data for...
And yet, the wizard keeps trying to import a value for ROWID even though I have it set to ignore (actually, it is doing just that... ignoring the import, therefore it is trying to import a NULL value for it... hence the problem)
If I uncheck the Identity check box, it errors out with unable to duplicate a primary key.
Hope this makes some sense with what I am seeing.
Thanks much
Are you sure it is setup as an identity column on SQL Server?
"If I uncheck the Identity check box" The checkbox in the wizard that says "Enable Identity Insert"?
If so, this box should be unchecked unless you actually want to insert values into the identity column (and I assume you don't).
"<ignore> is set for each source column that I am not importing data for..."
And you also have it select for the output column as well right?
"If I uncheck the Identity check box" The checkbox in the wizard that says "Enable Identity Insert"?
If so, this box should be unchecked unless you actually want to insert values into the identity column (and I assume you don't).
"<ignore> is set for each source column that I am not importing data for..."
And you also have it select for the output column as well right?
ASKER
I saved the DTS Package and this is how it is set up:
Microsoft Access (Source)
Transform Data Task
MS OLE DB Provider for SQL Server (Destination)
Transform Data Task Properties
Source Tab - I have a query that selects all but onoe column from the source table (needed to import later)
Destination Tab - I have the desired destination table selected and it shows all columns in this table, their type, nullable, size, etc...
Transformation Tab - Shows the correct source to destination links... all desired import data matches up with its corresponding destination. Each Name definition is set to "Copy Column"
Lookups Tab - no information entered or present here
Options Tab - Exception file is blank, File type is 7.0, file format is {CR}{LF}, Vertical Bar and <none> for the row column and text. Data movement: max error 0, fetch buffer 1, first row 0, last row 0; and finally the SQL server options are
Use fast load -Checked
Check constraints -checked
I have tried the Enable Identity Insert both checked and unchecked.
When I leave the enable identity insert unchecked and run the package I get the following error
The task reported failure on execution.
The statement has been terminated.
Violation of PRIMARY KEY constraint 'PK_PART'. Cannot insert duplicate key in object 'PART'.
If I leave the Enable Identity Insert checked and run the package it errors out with the unable to insert NULL into ROWID error.
I performed an import using EM before a long time ago and didn't have this error. It was my impression then that this was pretty easy. With good data to import it was just a matter of pointing to the source, selecting a destination, ensure the columns matchup the desired way and then run it.
with what I have described above, am I missing something stupid? or have a datatype error of somesort?
Microsoft Access (Source)
Transform Data Task
MS OLE DB Provider for SQL Server (Destination)
Transform Data Task Properties
Source Tab - I have a query that selects all but onoe column from the source table (needed to import later)
Destination Tab - I have the desired destination table selected and it shows all columns in this table, their type, nullable, size, etc...
Transformation Tab - Shows the correct source to destination links... all desired import data matches up with its corresponding destination. Each Name definition is set to "Copy Column"
Lookups Tab - no information entered or present here
Options Tab - Exception file is blank, File type is 7.0, file format is {CR}{LF}, Vertical Bar and <none> for the row column and text. Data movement: max error 0, fetch buffer 1, first row 0, last row 0; and finally the SQL server options are
Use fast load -Checked
Check constraints -checked
I have tried the Enable Identity Insert both checked and unchecked.
When I leave the enable identity insert unchecked and run the package I get the following error
The task reported failure on execution.
The statement has been terminated.
Violation of PRIMARY KEY constraint 'PK_PART'. Cannot insert duplicate key in object 'PART'.
If I leave the Enable Identity Insert checked and run the package it errors out with the unable to insert NULL into ROWID error.
I performed an import using EM before a long time ago and didn't have this error. It was my impression then that this was pretty easy. With good data to import it was just a matter of pointing to the source, selecting a destination, ensure the columns matchup the desired way and then run it.
with what I have described above, am I missing something stupid? or have a datatype error of somesort?
"Destination Tab - I have the desired destination table selected and it shows all columns in this table, their type, nullable, size, etc..."
But you don't have the ROWID column listed correct?
But you don't have the ROWID column listed correct?
ASKER
looking at the transformation tab in the DTS Local Packages there are no <ignore> options.
There the source table on the left, the destination table on the right, and a line drawn from source to destination for each source column. All the lines match up correctly,
However,
in the destination table, ALL columns are present, including the ROWID. Only the columns that match up with the source table have a line to them, all other columns have no line matched up with them.
If I look at the transformation via just running the DTS Import/Export wizard I can select <ignore> on the source table side, but am not able to select <ignore> or anything on the destination side.
There the source table on the left, the destination table on the right, and a line drawn from source to destination for each source column. All the lines match up correctly,
However,
in the destination table, ALL columns are present, including the ROWID. Only the columns that match up with the source table have a line to them, all other columns have no line matched up with them.
If I look at the transformation via just running the DTS Import/Export wizard I can select <ignore> on the source table side, but am not able to select <ignore> or anything on the destination side.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Got it.
The issue was rooted in the fact that the table I was importing from had some entries that were already existing in the destination. However, there were issues as you described above as well.
Thanks much for your help. I have a completed import.
The issue was rooted in the fact that the table I was importing from had some entries that were already existing in the destination. However, there were issues as you described above as well.
Thanks much for your help. I have a completed import.
Good deal! Sorry it took a bit....