Don't know which DATA TYPE to use
Hi,
I am importing figures into a table and I have declared it as a FLOAT and DECIMAL but it is still not importing and i need advise as to which data type to use. Be low are some of the figures i have to import.
0.09
6
5.9
6.6
6.8
14.5
10.8
15.1
14.9
23.9
0.59
0.61
0.62
0.81
1.6
1.47
1.66
1.74
2.43
2.2
2.4
2.2
3.1
2.5
0.4
0.5
0.2
0.4
0.9
1.7
1.9
1.8
2.5
2.1
0.49
0.49
0.58
0.67
1.32
1.32
1.53
1.69
2.02
0.02
0.04
0.06
0
0.18
0
0
0.04
0.04
26.119
26.109
26.149
26.258
26.457
26.654
26.759
26.823
26.872
There are zero's and whole numbers and decimals so i have been trying to use all the data types and i just can't seem to get it right.
please helpAshley Niekerk
March 30th, 2011 4:12am
What do you mean "still not importing"? Use DECIMAL for 'safety'Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
Free Windows Admin Tool Kit Click here and download it now
March 30th, 2011 4:29am
This is the error message I am receiving.
I used the DECIMAL data type:
Copying to [dbo].[properties] (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 10.0" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)
Error 0xc020901c: Data Flow Task 1: There was an error with input column "property_value" (116) on input "Destination Input" (69). 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 "input "Destination Input" (69)" failed because error code 0xC020907D occurred, and the error row disposition on "input "Destination Input" (69)" 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 - properties" (56) failed with error code 0xC0209029 while processing input "Destination Input" (69). 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)
Ashley Niekerk
March 30th, 2011 4:38am
But the error clearly says : The value violated the integrity constraints for the column. check the constraint at the destination table and source table ..are you moving data from SQL to SQL or some other DBMS to SQL ...Are you using a flat file or moving
directly from a table ..Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
March 30th, 2011 4:46am
Hi there,
I am importing from Excel to SQL 2008 DB. I have succeffully imported the other files and this seems to be having import issues with. please see the details below.
property_value
depth
cruise_code
vessel_code
station_id
property_code
data_type
14.47
1
OA105
AFR
STN 3
1
1
14.48
10
OA105
AFR
STN 3
1
1
14.04
20
OA105
AFR
STN 3
1
1
13.02
31
OA105
AFR
STN 3
1
1
11.72
50
OA105
AFR
STN 3
1
1
10.77
76
OA105
AFR
STN 3
1
1
9.9
101
OA105
AFR
STN 3
1
1
9.23
126
OA105
AFR
STN 3
1
1
8.73
169
OA105
AFR
STN 3
1
1
35.04
1
OA105
AFR
STN 3
2
1
35.03
10
OA105
AFR
STN 3
2
1
34.96
20
OA105
AFR
STN 3
2
1
34.83
31
OA105
AFR
STN 3
2
1
34.76
50
OA105
AFR
STN 3
2
1
34.79
76
OA105
AFR
STN 3
2
1
34.73
101
OA105
AFR
STN 3
2
1
34.67
126
OA105
AFR
STN 3
2
1
34.63
169
OA105
AFR
STN 3
2
1
0.19
1
OA105
AFR
STN 3
6
1
0.23
10
OA105
AFR
STN 3
6
1
0.23
20
OA105
AFR
STN 3
6
1
0.25
31
OA105
AFR
STN 3
6
1
0.36
50
OA105
AFR
STN 3
6
1
0.08
76
OA105
AFR
STN 3
6
1
0.11
101
OA105
AFR
STN 3
6
1
0.09
126
OA105
AFR
STN 3
6
1
0.09
169
OA105
AFR
STN 3
6
1
6
1
OA105
AFR
STN 3
8
1
5.9
10
OA105
AFR
STN 3
8
1
6.6
20
OA105
AFR
STN 3
8
1
6.8
31
OA105
AFR
STN 3
8
1
14.5
50
OA105
AFR
STN 3
8
1
10.8
76
OA105
AFR
STN 3
8
1
15.1
101
OA105
AFR
STN 3
8
1
14.9
126
OA105
AFR
STN 3
8
1
23.9
169
OA105
AFR
STN 3
8
1
0.59
1
OA105
AFR
STN 3
9
1
0.61
10
OA105
AFR
STN 3
9
1
0.62
20
OA105
AFR
STN 3
9
1
0.81
31
OA105
AFR
STN 3
9
1
1.6
50
OA105
AFR
STN 3
9
1
1.47
76
OA105
AFR
STN 3
9
1
1.66
101
OA105
AFR
STN 3
9
1
1.74
126
OA105
AFR
STN 3
9
1
2.43
169
OA105
AFR
STN 3
9
1
2.2
1
OA105
AFR
STN 3
10
1
2.4
10
OA105
AFR
STN 3
10
1
2.2
20
OA105
AFR
STN 3
10
1
3.1
31
OA105
AFR
STN 3
10
1
2.5
50
OA105
AFR
STN 3
10
1
0.4
1
OA105
AFR
STN 3
13
1
0.5
10
OA105
AFR
STN 3
13
1
0.2
20
OA105
AFR
STN 3
13
1
0.4
31
OA105
AFR
STN 3
13
1
0.9
50
OA105
AFR
STN 3
13
1
1.7
1
OA105
AFR
STN 3
14
1
1.9
10
OA105
AFR
STN 3
14
1
1.8
20
OA105
AFR
STN 3
14
1
2.5
31
OA105
AFR
STN 3
14
1
2.1
50
OA105
AFR
STN 3
14
1
0.49
1
OA105
AFR
STN 3
34
1
0.49
10
OA105
AFR
STN 3
34
1
0.58
20
OA105
AFR
STN 3
34
1
0.67
31
OA105
AFR
STN 3
34
1
1.32
50
OA105
AFR
STN 3
34
1
1.32
76
OA105
AFR
STN 3
34
1
1.53
101
OA105
AFR
STN 3
34
1
1.69
126
OA105
AFR
STN 3
34
1
2.02
169
OA105
AFR
STN 3
34
1
0.02
1
OA105
AFR
STN 3
35
1
0.04
10
OA105
AFR
STN 3
35
1
0.06
20
OA105
AFR
STN 3
35
1
0
31
OA105
AFR
STN 3
35
1
0.18
50
OA105
AFR
STN 3
35
1
0
76
OA105
AFR
STN 3
35
1
0
101
OA105
AFR
STN 3
35
1
0.04
126
OA105
AFR
STN 3
35
1
0.04
169
OA105
AFR
STN 3
35
1
26.119
1
OA105
AFR
STN 3
48
1
26.109
10
OA105
AFR
STN 3
48
1
26.149
20
OA105
AFR
STN 3
48
1
26.258
31
OA105
AFR
STN 3
48
1
26.457
50
OA105
AFR
STN 3
48
1
26.654
76
OA105
AFR
STN 3
48
1
26.759
101
OA105
AFR
STN 3
48
1
26.823
126
OA105
AFR
STN 3
48
1
26.872
169
OA105
AFR
STN 3
48
1
2.7
1
OA105
AFR
STN 3
40
1
The property_value column is the problem I am trying to use teh correct data type but it still isn't accepting the import.
Ashley Niekerk
March 30th, 2011 4:53am
then check the constraint on the column which showing up in the error ..alternatively , please share the complete table structure ..
Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
March 30th, 2011 5:46am
You wrote you tried float as well. any errors on that?
Did you use the switch "IMEX=1" in the ConnectionString?
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.xls;Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1";
March 30th, 2011 10:24am
Abhay is correct - the error clearly describes an issue with constraints. It has nothing to do with data type. The problem also has nothing to do with the source, Christina.
Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
March 30th, 2011 5:11pm