Solved

SSIS Package in SQL Server 2005

Posted on 2009-07-10
14
476 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
 

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

760 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

21 Experts available now in Live!

Get 1:1 Help Now