?
Solved

Loading from excel sheet to sql server using ssis

Posted on 2009-02-11
14
Medium Priority
?
1,528 Views
Last Modified: 2013-11-10
I am trying to load excel sheet to sql server2005. I converted data types of excel souce to corresponding type of OLEDB Destination.But still I am getting errors.
I enclosed excel sheet ,SSIS Package and sql for creating  table.Can any one Try it.
Change the Package extension from 'txt' to 'dtsx' and open SSIS Package

I got the following errors

[Data Conversion [246]] Error: Data conversion failed while converting column "Preferred_Status" (56) to column "Copy of Preferred_Status" (363).  The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".



[OLE DB Destination [1774]] Error: There was an error with input column "Phase_Out_Date" (1830) on input "OLE DB Destination Input" (1787). The column status returned was: "The value could not be converted because of a potential loss of data.".


Thanks and Regards,
Coventri
1-SC-20084-NMPI-List.xls
Package.txt
create-table-sql.txt
0
Comment
Question by:coventri
  • 9
  • 5
14 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23612267
Coventri,
You are trying to convert a empty string into date... is not possible...
You should have a data in the field Phase_Out_Date.

Helped?
regards,
Pedro
SSIS-Interface.JPG
0
 
LVL 1

Author Comment

by:coventri
ID: 23612926
Hi Pedro,
1)In the excel sheet the data in phase_out _date column is of type DATE,But why it was changing to
DT_WSTR in Excel Source OutPut.
2) I am getting error converting preferred_status column from DT_WSTR to DT_STR

Thanks and Regards,
Coventri
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23612986
1) because is empty
2) I first use the error with phase_out_date... I will check if you have problems with others
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 1

Author Comment

by:coventri
ID: 23628118
Hi Pedro,
I removed data conversion for phase_out_date.And I try to load the file .I got the following error.

[OLE DB Destination [222]] Error: There was an error with input column "Phase_Out_Date" (655) on input "OLE DB Destination Input" (235). The column status returned was: "The value could not be converted because of a potential loss of data.".

And I try with other file it also contains no data in phase_out_column.And It was loading succussfully.
i attached new file.can you please test it

Thanks,
coventri
Preferd.xls
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23631720
you cannot insert null in date fields...
attach here an example of source and destination file if you want me to give you more support.
Regards,
Pedro
www.pedrocgd.blogspot.com
0
 
LVL 1

Author Comment

by:coventri
ID: 23632618
Hi Pedro,
I have two excel sheets .I am able to load excel sheet 1 and I am unable to load excel sheet 2.
Both excel sheets have null in date fields.And my  destination table will accept null ( as default) in date field.
I attached excel sheets,package,destination table.

can you please check why i am unable to laod sheet 2.
thanks,
coventri
Preferd.xls
1-SC-20084-NMPI-List.xls
Package.txt
create-table-sql.txt
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23632662
Ok.. I will see now...
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23635010
HI Conventri,
Sorry for the delay... Today I'm very busy.
I attached the package that run correctly for your needs!
Regards,
Pedro
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23635016
The package
Package-EE88-dtsx.txt
0
 
LVL 1

Author Comment

by:coventri
ID: 23649701
Hi Pedro,

I am able to Load now.But If I load preferred status column by converting from (DT_WSTR to DT_STR).

I got the following error

[Data Conversion [239]] Error: Data conversion failed while converting column "Preferred_Status" (43) to column "Copy of Preferred_Status" (504).  The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

You can use the same package which forwarded to me, for testing of (preferred_status) column in excel sheet

Thanks ,
coventri

0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23649749
I will check...:-)
0
 
LVL 22

Accepted Solution

by:
PedroCGD earned 2000 total points
ID: 23649831
ok.. see the attached package with the problem resolved.
Helped?
regards,
Pedro
Package-EE88b-dtsx.txt
0
 
LVL 1

Author Comment

by:coventri
ID: 23659442
Hi Pedro,

It was loading now.
Thanks for helping me a lot

Thanks,
coventri
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23659528
Hi COnventri,
I'm here for that! :-)
Visit my blog and leave a comment to know you better!
Pedro
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

850 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