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?

LVL 3
kkohlAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
arbertConnect With a Mentor Commented:
"looking at the transformation tab in the DTS Local Packages there are no <ignore> options."

This is available on the wizard when the package is created....

"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."

That's the problem....Double click on your transformation line.  Click on the Destination tab, click on Define Columns.  Go to your ROWID column and click on the dropdown--choose ignore....Make sure the "Enable Identity Insert" is not checked on your job....
0
 
arbertCommented:
So do you have ROWID defined as an identity column?  If so, simply choose IGNORE from the column in the wizard....
0
 
arbertCommented:
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...
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
kkohlAuthor Commented:
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

0
 
arbertCommented:
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?


0
 
kkohlAuthor Commented:
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?






0
 
arbertCommented:
"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?
0
 
kkohlAuthor Commented:
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.
0
 
kkohlAuthor Commented:
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.

0
 
arbertCommented:
Good deal!  Sorry it took a bit....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.