Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:The number of failing rows exceeds the maximum specified.
(Microsoft Data Transformation Services (DTS) Data Pump (80040e21): Insert error,
column 1 ('ServerId', DBTYPE_I8), status 9: Permission denied.)
(Microsoft OLE DB Provider for SQL Server (80040e21):
Multiple-step OLE DB operation generated errors.
Check each OLE DB status value, if available. No work was done.)
Step Error code: 8004206A
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:0

If anyone could help, thanks,


CarlWalden

__________________
CarlWalden

Are you running the package under sa? Does the SQL Server machine account have access to the source file (I'm assuming this is the Excel file in your previous message)?

Yes, the package is running under SA, and the Server has access to the source and it is Excel. Thanks for your help.

CarlWalden

In the options panel for the Data Transformation task, do you have the "Enable Identity Insert" turned on?

Thanks,
Jeff

Joe Fawcett made the following comments:
If an identity column number is used it cannot be re-used, even if the record is deleted without manual intervention. Are you sure that no records were inserted and then removed, even via a transaction that was rolled back? Perhaps you could create a new table of identical structure except without the identity, import your old data and then set the identity seed etc? I was able to create the tbl and I got the following warning:
Warning: The table 'tblServer' has been created but its maximum row size (19003) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'tblServer' has been created but its maximum row size (19003) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes. What does it mean? Regarding Jbelina queries in the options panel I have only ticked 'Source Error rows' and then on the SQL Server, I have ticked check constraints but the enable identity insert is not on (untick). Thanks for all your help. I shall remain.



CarlWalden

SQL has a row size limitation, and it appears you are going WAY over it for one of your rows. How many fields do you have in the table, and how big is each field (large, medium, small, etc)? You may have to normalize the table.

Bmains,

Thanks for your inputs if you could further help me here how do I go about the normalisation procedures? If you put couple of line towards the procedures I would be really happy. Thanks and I look forward to hearing from you.

Regards,

CarlWalden

Hello,

You'll probably want to get a good database book to go over normalization. I can't discuss all the theory here (as it would be to long), but basically:

1. Identify the repeating fields
2. Remove repeating fields and put in a separate table. For example, you duplicate the name of a credit card company in the table, which has the values Visa, MasterCard, etc., you rip out the credit card companies from the field, put the companies in a separate table and add an ID field, as such:
CreditCardCompany
-------------------
ID int identity
Name varchar(50)

and in the table, link to the ID as such

Table CreditCardCompany
--------------- ---------------------
..
..
FK:CreditCardID -> PK:CreditCardID

Hope this helps, let me know,

Brian

The following article should help you learn more about normalization:

http://www.databasejournal.com/sqlet...le.php/1428511

Thanks folks for all your help but I have another questions some of you suggested normalization. That meant total redesign of the entire database, but what I have been able to do was the optimization of the entire tables and I can honestly say to you that all the kind of warnings and bottlenecks, etc, etc, have now been sorted. Now my question is this what are the difference between optimization & normalization? My basic understanding of normalization is to maintain referential integrity while optimization is on-going procedures to turn the database as best as possible. Am I right in thinking towards such direction?


CarlWalden