Solved

SSIS Package in SQL Server 2005

Posted on 2009-07-10
14
488 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

896 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

18 Experts available now in Live!

Get 1:1 Help Now