Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SSIS Package in SQL Server 2005

Posted on 2009-07-10
14
500 Views
Last Modified: 2012-05-07
I have a database that has many tables and for the most part each table has primary keys that are autogenerated.  I have to update a table with many new records.  I am able to open up my management console and manuallly add entries to it.  but the problem is that i have close to 200 records to input.  
I decided to go the SSIS route.  unfortuntately when i go through the wizard i get an error message. which is attached.  

I created a file that has the next auto number manually added.  I have also done an import without filling out anything in the id field.  
table has 5 columns

id                 int           no nulls
costcat       int            nulls
costcode    varchar5  nulls
definition    varchar80  nulls
active         varchar1  nulls


Im not much of an SQL admin but im trying to learn as i go along.  Please let me know what other peice of info might be needed.

sql-import-error.JPG
0
Comment
Question by:ansonindustries
  • 6
  • 3
  • 2
  • +2
14 Comments
 
LVL 11

Expert Comment

by:dodge20
ID: 24824693
Since you are inserting into an identity field, did you enable the check box for identity insert?
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24824702
Seems that you are trying to insert a duplicate or null value to your primary ke "id"
Helped?
Regards,
Pedro
0
 
LVL 7

Expert Comment

by:wilje
ID: 24824789
Remove the id column from your import - and remove the mappings.  The ID column will auto increment when you insert your other data.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:ansonindustries
ID: 24824841
Where do i find the checkbox for identy insert  i see the identity sepcification set to yes
Is identy = yes
identity increment 1
identity seed 1
0
 

Author Comment

by:ansonindustries
ID: 24824874
If i change the mappings this is what i get

- Validating (Error)
Messages
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source - Sheet1$" (1).
 (SQL Server Import and Export Wizard)
 
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Data Conversion 1" (48).
 (SQL Server Import and Export Wizard)
 
0
 
LVL 7

Expert Comment

by:Mohed Sharfi
ID: 24824926
0
 
LVL 11

Expert Comment

by:dodge20
ID: 24825826
If you are doing the import through management studio there is a checkbox called enable identity insert on the edit mappings button.

If through bids on the data source destination there is a checkbox that says keep identity.
0
 

Author Comment

by:ansonindustries
ID: 24840523
I checked the box for identity insert and this is my error message

- Validating (Error)
Messages
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source - Sheet1$" (1).
 (SQL Server Import and Export Wizard)
 
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Data Conversion 1" (51).
 (SQL Server Import and Export Wizard)
0
 
LVL 7

Expert Comment

by:Mohed Sharfi
ID: 24840926
Hi ansonindustries,
How are you
your error occurred when import from Excel to SQL 2005 DB Table.
please try one of these solutions:
1- change your cells format to text in Excel sheet
OR
2- delete the formula fields in your Excel sheet, and the NULL values.
thanks
0
 

Author Comment

by:ansonindustries
ID: 24841194
same error after changing fields to text.  and since ther are no formulas or null values i cant do the second option.

for now im only importing 1 row of data just to test it out.  I am using MS SQL server management studio from my desktop and the actual sql server is on another machine ive been reading stuff about upgrading my sql server to a higher service pack.  any suggestions of how i can find out what type of sql server i have already?  im pretty sure its a chopped down version.  i aslo dont have the full managment console on the server only the free management studio so i cant do import/export from that console.
0
 
LVL 7

Expert Comment

by:wilje
ID: 24841318
Open a query window and execute the following:
SELECT serverproperty('Edition');
SELECT @@version;
0
 
LVL 7

Accepted Solution

by:
Mohed Sharfi earned 500 total points
ID: 24841399
Ok, try to import the sheet into MS Access, and then using MS Access as the import source for MSSQL Server 2005.
thanks
0
 

Author Comment

by:ansonindustries
ID: 24841423
Microsoft SQL Server  2000 - 8.00.760 (Intel X86)   Dec 17 2002 14:22:05   Copyright (c) 1988-2003 Microsoft Corporation  Desktop Engine on Windows NT 5.2 (Build 3790: Service Pack 1)
0
 

Author Closing Comment

by:ansonindustries
ID: 31602127
Thanx that worked great but i would love to find out more on the issue with the import/export wizard in the mmc.

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

809 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