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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Excel Source has different data types in one colum
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sonu619
Posting Yak Master

202 Posts

Posted - 04/02/2015 :  16:44:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 04/03/2015 :  16:16:44  Show Profile  Reply with Quote
What is the datatype of the column in your target table?
Go to Top of Page

Sonu619
Posting Yak Master

202 Posts

Posted - 04/04/2015 :  12:52:15  Show Profile  Reply with Quote
Target Table datatype is Nvarchar(255).

Thank You.
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 04/04/2015 :  14:18:43  Show Profile  Reply with Quote
What is the datatype of that column in the package in the excel source adapter?
Go to Top of Page

Sonu619
Posting Yak Master

202 Posts

Posted - 04/05/2015 :  03:03:41  Show Profile  Reply with Quote
Double Precision Float and sample data is below

ABC (E.G)
123487
894651
ABC-879655
ED-78545
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 04/05/2015 :  08:10:38  Show Profile  Reply with Quote
So, chance that data type to DT_wstr
Go to Top of Page

Sonu619
Posting Yak Master

202 Posts

Posted - 04/06/2015 :  09:36:42  Show Profile  Reply with Quote
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?
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 04/06/2015 :  09:50:31  Show Profile  Reply with Quote
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.

Edited by - gbritton on 04/06/2015 09:51:06
Go to Top of Page

Sonu619
Posting Yak Master

202 Posts

Posted - 04/07/2015 :  10:06:32  Show Profile  Reply with Quote
I changed both. and I am using DT-WSTR (500), I am positive the source data is not that long.
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 04/07/2015 :  20:21:31  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000