?
Solved

SSIS Package in SQL Server 2005

Posted on 2009-07-10
14
Medium Priority
?
518 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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 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 1500 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

771 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