May 15, 2011 at 11:22 pm
In a DFT i have excel source and tried mapping the phone number columns(ph1,ph2,ph3) to P1, P2 and P3 fields in SQL Server table. The three destination columns are of type varchar(17).
Few of the 4000 rows in my excel source have NULLs in ph1,ph2 and ph3 columns.
When did a straight mapping it errored out:
Cannot load data between unicode and nonunicode columns.
So i converted the phone number fields using a data conversion component to Dt_STR string
Then mapped a derived column on to them
It does not error out this time but loads everything in the Phone number columns as NULL
Whats happening ? where am i messing up ?
I tried changing manually cell type in excel from general to text and number.
Any help would be appreciated.
Data conversion scrn shots r attached.
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
May 15, 2011 at 11:32 pm
Convert to "Unicode String [DT_WSTR]" instead of [DT_STR]
__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!
May 16, 2011 at 5:31 am
Anjan Wahwar (5/15/2011)
Convert to "Unicode String [DT_WSTR]" instead of [DT_STR]
What's the difference between the 2? >>> never used ssis b4.
May 16, 2011 at 6:58 am
Ninja's_RGR'us (5/16/2011)
Anjan Wahwar (5/15/2011)
Convert to "Unicode String [DT_WSTR]" instead of [DT_STR]What's the difference between the 2? >>> never used ssis b4.
From BOL:
DT_STR
A null-terminated ANSI/MBCS character string with a maximum length of 8000 characters. (If a column value contains additional null terminators, the string will be truncated at the occurrence of the first null.)
DT_WSTR
A null-terminated Unicode character string with a maximum length of 4000 characters. (If a column value contains additional null terminators, the string will be truncated at the occurrence of the first null.)
SSIS does not like mixing the two!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 16, 2011 at 7:04 am
Ho ya I remember reading something about that 4 years ago. Does the W stand for wide?
May 16, 2011 at 7:18 am
Ninja's_RGR'us (5/16/2011)
Ho ya I remember reading something about that 4 years ago. Does the W stand for wide?
An excellent question and one which has just diverted me from my day job for ten minutes.
If W meant 'wide', it would be somewhat odd (wide = 4,000 chars, 'not wide' = 8,000 chars :w00t:)
But I can't find any expansion for WSTR other than "Wall Systolic Thickening Rate, " which I have my doubts about in this context 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 16, 2011 at 7:25 am
Ninja's_RGR'us (5/16/2011)
Anjan Wahwar (5/15/2011)
Convert to "Unicode String [DT_WSTR]" instead of [DT_STR]What's the difference between the 2? >>> never used ssis b4.
Addition to the difference given by phil:
When it come to mapping of SSIS data type and SQL Server datatype then nchar, nvarchar, sql_variant, xml will be mapped directly to DT_WSTR (SSIS data type). Any mapping given apart from DT_WSTR for varchar or nvarchar will through you an error.
That why you will be converting to DT_WSTR instead of DT_STR.
__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!
May 16, 2011 at 7:33 am
When it come to mapping of SSIS data type and SQL Server datatype then nchar, nvarchar, sql_variant, xml will be mapped directly to DT_WSTR (SSIS data type). Any mapping given apart from DT_WSTR for varchar or nvarchar will through you an error.
I can't see how that applies in this case, as it relates to data mapped from Excel (data type = lucky dip), not from SQL Server.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 16, 2011 at 7:33 am
Phil Parkin (5/16/2011)
Ninja's_RGR'us (5/16/2011)
Ho ya I remember reading something about that 4 years ago. Does the W stand for wide?An excellent question and one which has just diverted me from my day job for ten minutes.
If W meant 'wide', it would be somewhat odd (wide = 4,000 chars, 'not wide' = 8,000 chars :w00t:)
But I can't find any expansion for WSTR other than "Wall Systolic Thickening Rate, " which I have my doubts about in this context 🙂
I was thinking more on the line of wide character. And yes I had seen the irony in the 4k / 8k limits :w00t:.
May 16, 2011 at 9:05 am
Anjan Wahwar (5/16/2011)
Ninja's_RGR'us (5/16/2011)
Anjan Wahwar (5/15/2011)
Convert to "Unicode String [DT_WSTR]" instead of [DT_STR]What's the difference between the 2? >>> never used ssis b4.
Addition to the difference given by phil:
When it come to mapping of SSIS data type and SQL Server datatype then nchar, nvarchar, sql_variant, xml will be mapped directly to DT_WSTR (SSIS data type). Any mapping given apart from DT_WSTR for varchar or nvarchar will through you an error.
That why you will be converting to DT_WSTR instead of DT_STR.
Tired doing that it errors out saying cannot convert between unicode and non unicode values.
Any other alternatives ?
I am able to load adjacent columns from da excel file where i have adress strings .. but only these three columns which have phone numbers are being PIA.
I also made sure there are no spaces by validating the columns in excel file.
What now ? :unsure:
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
May 16, 2011 at 9:24 am
Hope this helps to reproduce the error.
Atatched is a sample excel file. The columns Phone1, Phone 2 and Phone 3 are to be mapped with the Phone1, Phone2 and Phone3 columns of Master table.
Thanks
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Master](
[SequenceNbr] [int] IDENTITY(1,1) NOT NULL,
[Phone1] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone2] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone3] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone4] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
May 16, 2011 at 9:27 am
Tired the following in teh derived column
(SUBSTRING([CONVERTED PHONE1],1,10))
(SUBSTRING([CONVERTED PHONE1],1,10))
(SUBSTRING([CONVERTED PHONE1],1,10))
Converted : to DT_STR
This didnt help either.
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
May 16, 2011 at 7:42 pm
You're coming FROM Excel, right? Everything in Excel uses Unicode, so the problem is your destination is NON-Unicode. make your datatype nvarchar in the master table, and you should be fine.
And Ninja's, I think W=Wierd...:-P
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
May 16, 2011 at 10:23 pm
Phil Parkin (5/16/2011)
When it come to mapping of SSIS data type and SQL Server datatype then nchar, nvarchar, sql_variant, xml will be mapped directly to DT_WSTR (SSIS data type). Any mapping given apart from DT_WSTR for varchar or nvarchar will through you an error.
I can't see how that applies in this case, as it relates to data mapped from Excel (data type = lucky dip), not from SQL Server.
Agree Phil, but eventually, destination of data is SQL Server only.:cool:
__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!
May 17, 2011 at 7:18 am
Well, one problem I see right off the bat is if I open your posted Excel file, I get a popup that the file format doesn't match the file extension. If I save the file as a *.xlsx file, the popup doesn't occur, from which I conclude that your file is actually an Excel 2007 file saved with the wrong extension.
What happens when you first save the file to Excel 97-2003 format (which is the correct designation for *.xls files) and process that?
Edit: I confirmed this is an xlsx file. I renamed the posted file as a *.zip compressed file and was able to open the zipped XML files, just like an xlsx file.
Rich
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply