SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Unble to import data in table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

asp__developer
Posting Yak Master

108 Posts

Posted - 08/06/2013 :  15:46:13  Show Profile  Reply with Quote
I am trying to use import / export feature in sql server 2012 express management studio.

I want to import data from excel file to database table.

Everytime I try to import the excel file data, I get error about data type mismatch and the process fails.

Just to make sure if the datatype / format is good for importing, I exported the existing data from the table and tries to import the same exported file - still I got the data type mismatch, why ?

Here is my table

CREATE TABLE [dbo].[SnDiscussionTopic](
	[Id] [uniqueidentifier] NOT NULL,
	[SnDiscussionBoardId] [uniqueidentifier] NOT NULL,
	[Title] [nvarchar](500) NOT NULL,
	[UserId] [uniqueidentifier] NULL,
	[DateCreated] [datetime] NOT NULL,
	[Ip] [nvarchar](50) NULL,
	[Referrer] [nvarchar](500) NULL,
	[UserAgent] [nvarchar](255) NULL,
	[IsApproved] [bit] NOT NULL,
	[IsSpam] [bit] NOT NULL,
	[Spaminess] [decimal](18, 0) NOT NULL,
	[Signature] [nvarchar](max) NULL,
	[TimesViewed] [int] NULL,
	[IsClosed] [bit] NOT NULL,
	[IsPinned] [bit] NOT NULL,
	[PinnedOn] [datetime] NULL,
	[PinnedByUserId] [uniqueidentifier] NULL,

James K
Flowing Fount of Yak Knowledge

3549 Posts

Posted - 08/06/2013 :  16:13:19  Show Profile  Reply with Quote
Most often when I see the errors they are because of mis-interpretation of data. For example, if you have an excel file that has numeric data in the first few rows of a column but has non-numeric data after that, the data type of the column may be interpreted as numeric because the sampling of the first few rows show the data to be numeric. I have no evidence that that is what is happening to you; I am simply citing an example.

You can examine each column and assign the correct data types etc., but most often what I do is import the data into a staging table that has all varchar or nvarchar columns of sufficient width to hold the data. Once it is in the staging table, a simple insert statement will insert that data into your final destination table.

SSRS purists would certainly pooh-pooh this, and I am not claiming that it is beautiful or elegant, but it works.
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 08/06/2013 :  16:42:06  Show Profile  Reply with Quote
For the unsuccessful import I am getting following errors:

Validating error:
- Validating (Warning)
Messages
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "Ip" with a length of 255 to database column "Ip" with a length of 50.
 (SQL Server Import and Export Wizard)
 
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "Ip" with a length of 255 to database column "Ip" with a length of 50.
 (SQL Server Import and Export Wizard)
 
Warning 0x80049304: Data Flow Task 1: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available.  To resolve, run this package as an administrator, or on the system's console.
 (SQL Server Import and Export Wizard)
 





Error while copying data:

- Copying to [dbo].[SnDiscussionTopic] (Error)
Messages
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Unspecified error".
 (SQL Server Import and Export Wizard)
 
Error 0xc020901c: Data Flow Task 1: There was an error with Destination - SnDiscussionTopic.Inputs[Destination Input].Columns[Id] on Destination - SnDiscussionTopic.Inputs[Destination Input]. The column status returned was: "The value violated the integrity constraints for the column.".
 (SQL Server Import and Export Wizard)
 
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Destination - SnDiscussionTopic.Inputs[Destination Input]" failed because error code 0xC020907D occurred, and the error row disposition on "Destination - SnDiscussionTopic.Inputs[Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination - SnDiscussionTopic" (65) failed with error code 0xC0209029 while processing input "Destination Input" (78). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)
 


Edited by - asp__developer on 08/06/2013 16:42:41
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 08/06/2013 :  16:45:11  Show Profile  Reply with Quote
My data looks like this (a few columns to give an example)

SnDiscussionBoardId - {AAC75C8E-F064-47FD-AEDA-A21200ED635A}
Title	- this is also a question
UserId	- {45FA1109-3R60-49AC-A430-335D3032J4A5}
DateCreated - 1/12/2012
	
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 08/07/2013 :  16:52:42  Show Profile  Reply with Quote
anyone ?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3549 Posts

Posted - 08/07/2013 :  17:01:08  Show Profile  Reply with Quote
The error message tells you what you need to do. Import process is seeing that the column Ip is 50 characters wide, but it is seeing data that is 255 characters long in your file destined for that column. So you need to increase the width of column Ip.

If you exported existing data to an Excel file and then re-imported it, and if it is still complaining, the only thing I can think of is that Excel somehow mangled your data.
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 08/08/2013 :  09:21:59  Show Profile  Reply with Quote
Yes but like I mentioned before that I exported the data from the same table and then trying to re-import so the length is not changes or anything.

Seems like excel is creating the problem. Any way overcome this problem ?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3549 Posts

Posted - 08/08/2013 :  09:47:10  Show Profile  Reply with Quote
quote:
Originally posted by asp__developer

Yes but like I mentioned before that I exported the data from the same table and then trying to re-import so the length is not changes or anything.

Seems like excel is creating the problem. Any way overcome this problem ?

I don't really know the answer to that question - hopefully someone else will chime in.

One thing you can try for testing purposes is to save the excel file as a csv file, then look at it using a text editor to see if in fact there are any rows where Ip column is 255 characters long.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/08/2013 :  09:58:48  Show Profile  Reply with Quote
quote:
Originally posted by asp__developer

Yes but like I mentioned before that I exported the data from the same table and then trying to re-import so the length is not changes or anything.

Seems like excel is creating the problem. Any way overcome this problem ?


try applying derived column transform in data flow task to change it to length you want.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3549 Posts

Posted - 08/08/2013 :  10:07:57  Show Profile  Reply with Quote
Visakh, I think he is using Import/Export wizard.
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 08/09/2013 :  09:54:36  Show Profile  Reply with Quote
yes I am using import / export wizard
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 08/09/2013 :  09:56:21  Show Profile  Reply with Quote
Found the solution: since excel was messing up the data while importing. I copied the rows and columns from excel and pasted in table data and my data got transferred.

So simple solution but very effective and shorted all the steps involved in importing process.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/16/2013 :  14:12:35  Show Profile  Reply with Quote
quote:
Originally posted by asp__developer

yes I am using import / export wizard


still you could save it as a package and edit it in BIDs to do the required change if you want

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Jerrybald
Starting Member

United Kingdom
1 Posts

Posted - 10/09/2013 :  06:09:28  Show Profile  Reply with Quote
I've seen the same problem. It should be a simple export of a database to a NEW database but columns in a few tables complain that the target column width is too small. I happen to remember that these columns have been ALTERed in the source database. It appears that the export process created these columns with the original width.
I wonder if there is some simple way to have sql deal with the problem or if I must edit the package.

Jerry
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/09/2013 :  07:01:21  Show Profile  Reply with Quote
quote:
Originally posted by Jerrybald

I've seen the same problem. It should be a simple export of a database to a NEW database but columns in a few tables complain that the target column width is too small. I happen to remember that these columns have been ALTERed in the source database. It appears that the export process created these columns with the original width.
I wonder if there is some simple way to have sql deal with the problem or if I must edit the package.

Jerry


You need to first script out new lengths for columns from source and apply them to destination
You need to then open package and refresh metadata and remap columns. then it will work fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000