Solved

SSIS Package in SQL Server 2005

Posted on 2009-07-10
14
513 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
[X]
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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

691 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