Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Unble to import data in table

Author  Topic 

asp__developer
Posting Yak Master

108 Posts
Posted - 2013-08-06 : 15:46:13
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
Master Smack Fu Yak Hacker

3873 Posts
Posted - 2013-08-06 : 16:13:19
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 - 2013-08-06 : 16:42:06
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)

Go to Top of Page

asp__developer
Posting Yak Master

108 Posts
Posted - 2013-08-06 : 16:45:11
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 - 2013-08-07 : 16:52:42
anyone ?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts
Posted - 2013-08-07 : 17:01:08
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 - 2013-08-08 : 09:21:59
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
Master Smack Fu Yak Hacker

3873 Posts
Posted - 2013-08-08 : 09:47:10
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

52326 Posts
Posted - 2013-08-08 : 09:58:48
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
Master Smack Fu Yak Hacker

3873 Posts
Posted - 2013-08-08 : 10:07:57
Visakh, I think he is using Import/Export wizard.
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts
Posted - 2013-08-09 : 09:54:36
yes I am using import / export wizard
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts
Posted - 2013-08-09 : 09:56:21
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

52326 Posts
Posted - 2013-08-16 : 14:12:35
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

1 Post
Posted - 2013-10-09 : 06:09:28
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

52326 Posts
Posted - 2013-10-09 : 07:01:21
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
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site