Solved

DTS Package / Import Wizard

Posted on 2004-10-05
10
1,631 Views
Last Modified: 2013-11-30
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?

0
Comment
Question by:kkohl
  • 6
  • 4
10 Comments
 
LVL 34

Expert Comment

by:arbert
Comment Utility
So do you have ROWID defined as an identity column?  If so, simply choose IGNORE from the column in the wizard....
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
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
 
LVL 3

Author Comment

by:kkohl
Comment Utility
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
 
LVL 34

Expert Comment

by:arbert
Comment Utility
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
 
LVL 3

Author Comment

by:kkohl
Comment Utility
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 34

Expert Comment

by:arbert
Comment Utility
"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
 
LVL 3

Author Comment

by:kkohl
Comment Utility
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
 
LVL 34

Accepted Solution

by:
arbert earned 125 total points
Comment Utility
"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
 
LVL 3

Author Comment

by:kkohl
Comment Utility
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
 
LVL 34

Expert Comment

by:arbert
Comment Utility
Good deal!  Sorry it took a bit....
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now