Excel Source has different data types in one colum

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

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Excel Source has different data types in one colum

Author

Topic

Sonu619
Posting Yak Master

202 Posts

Posted - 2015-04-02 : 16:44:56

Hi Guys,

I am having a problem, client provide a .xlsx file and one file has below sample data

ABC (E.G)
123487
894651
ABC-879655
ED-78545

When I right click on excel source and go to Show advance editor/input and output properties, the column has data type "Double Precision Float" if i keep it same package runs fine however I am getting below data in my destination SQL Table

ABC
123487
894651
NULL
NULL

fyi, I am using below Variables to execute my SSIS Package

@ExcelP1 = Provider=Microsoft.ACE.OLEDB.12.0;Data Source=
@ExcelP2 = ;Extended Properties="Excel 12.0 XML;IMEX=1;HDR=YES";
@FileName = "Where my source file is located"

Then I am using below EXPRESSION in my Excel Connection manager string

Connection String = @ExcelP1+@FileName+@ExcelP2

Still I have a problem. Please advise. Its urgent.

Thank You.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-03 : 16:16:44

What is the datatype of the column in your target table?

Sonu619
Posting Yak Master

202 Posts

Posted - 2015-04-04 : 12:52:15

Target Table datatype is Nvarchar(255).

Thank You.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-04 : 14:18:43

What is the datatype of that column in the package in the excel source adapter?

Sonu619
Posting Yak Master

202 Posts

Posted - 2015-04-05 : 03:03:41

Double Precision Float and sample data is below

ABC (E.G)
123487
894651
ABC-879655
ED-78545

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-05 : 08:10:38

So, chance that data type to DT_wstr

Sonu619
Posting Yak Master

202 Posts

Posted - 2015-04-06 : 09:36:42

I did, but every time the package runs, my Excel source giving problem, Truncation error or can't convert from Double Precision Float To DT_Wstr.

Any other advise?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-06 : 09:50:31

Did you change the input (external) type or the output type?

Also, make sure that the DT-WSTR column is wide enough to hold the conversion results.

Sonu619
Posting Yak Master

202 Posts

Posted - 2015-04-07 : 10:06:32

I changed both. and I am using DT-WSTR (500), I am positive the source data is not that long.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-07 : 20:21:31

do you have an error output flow from your Excel adapter? YOu could put a dataviewer on that and see what row is the problem one.