August 14, 2013 at 7:09 am
I am currently using the wizard to import some data from excel to SQL.
typical values im importing..
1677318362192777425
1611488028192773776
1688578064192811089
1611654561192977000
it all works fine until the third fourth colum has a letter involved such as
161122555116401127X
If i was doing this through script.. i would obviously put the fourth value in single quotes '16401127X' which works fine. And in this case if i insert it manually.. it works fine.
But as there are thousands of rows this isnt practical. What do i need to do for the import\export wizard to recognise these values and insert them properly?
August 14, 2013 at 7:22 am
What's the error message you are getting?
August 14, 2013 at 7:24 am
Save the result of the wizard as a SSIS package and edit it in Visual Studio.
Change the data type to string instead of integer.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 14, 2013 at 7:53 am
The column status returned was: "The value violated the integrity constraints for the column.".
(SQL Server Import and Export Wizard)
is the error message.
When I open it up in Visio it is already set to string for that column.
Cheers for the suggestions though
August 14, 2013 at 7:55 am
Are you able to post the CREATE TABLE DDL statement for the table you're trying to import to and a sample file?
August 14, 2013 at 7:57 am
Are you trying to put empty values (=NULL) into a non-nullable column?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 14, 2013 at 8:01 am
CREATE TABLE [dbo].[SystemProducts](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SystemID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[Code] [varchar](50) NOT NULL )
I have also uploaded a few rows in a file..
August 14, 2013 at 8:02 am
Koen Verbeeck (8/14/2013)
Are you trying to put empty values (=NULL) into a non-nullable column?
No 🙂 there are no Nulls to insert.. it just doesnt like it when there is a Letter as part of a value.
But as mentioned.. If i do the insert manually.. it works. Just not through the wizard. The wizard doesn't put it in single quotes like I do manually
August 14, 2013 at 8:18 am
It seems if you open the package in SSDT then preview the source data it is trying to insert a NULL. I can't say i much understand why SSIS would interpret the data as a NULL. But that's why you're getting the integrity violation.
August 14, 2013 at 8:28 am
What Data Type does the wizard think that the column is ? I think it should be string (DT_STR) if you are expecting non-numeric data.
August 14, 2013 at 8:29 am
homebrew01 (8/14/2013)
What Data Type does the wizard think that the column is ? I think it should be string (DT_STR) if you are expecting non-numeric data.
Thats correct, its set like that.
August 14, 2013 at 8:32 am
Hi,
This is really strange. I've just created an SSIS package manually (i.e. not through the import wizard). If i say that the excel source has column headers in the first row then everything works fine. If i say that the first row doesn't contain column headers then i get the same problem.
Where is this data coming from? Any chance of getting it in CSV or having an XLSX with column headers?
Thanks,
Simon
August 14, 2013 at 8:42 am
s_osborne2 (8/14/2013)
Hi,This is really strange. I've just created an SSIS package manually (i.e. not through the import wizard). If i say that the excel source has column headers in the first row then everything works fine. If i say that the first row doesn't contain column headers then i get the same problem.
Where is this data coming from? Any chance of getting it in CSV or having an XLSX with column headers?
Thanks,
Simon
I was just given the excel by a client and asked to insert into one of his databases lol. I have never seen this issue before either.
The File did orginonally have headers, and the wizard inserted all the data upto the point of a value with a letter then stopped. So I still got the same issue with that. I tried this a few times with and without the header and I got the same problem.
From what you have said, it seems it may be the wizard doing something it shouldn't.
August 14, 2013 at 8:51 am
I have just saved the file into a CSV file and ran a bulk insert and it went straight in.
I wish I knew why the export wizard didn't work though!
August 14, 2013 at 8:53 am
I avoid that wizard where possible if i'm honest. Glad you got the data inserted! 🙂
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply