March 8, 2006 at 9:33 am
I'm getting a bit lost in SSIS. I've got an Excel source file that I'm trying to load into a table. I keep getting validation errors that warn about not being able to convert between unicode and non-unicode string data types.
I'm trying figure out where I have to change this and am frankly confused. It seems SSIS is selecting various columns as unicode/WSTR data types, but I want them to import as regular string types.
On the Data Flow tab in SSIS, I right-click on the source Data Flow component (the Excel file) and select Show Advanced Editor. Then on the last tab, Input and Output Properties, there's a tree view for the Excel output. There are "External Columns" and "Output Columns" containers in the tree view.
I tried setting some of these but they don't seem to "take". Do I need to change the data type for each column under both the External and Output columns?
That seems like a lot of work! And, as I say, I tried setting some, but I still get the same validation errors. Then I go back to this spot and my changes seem to have been lost.
Any help would be appreciated!
March 9, 2006 at 2:05 am
Use a data conversion transform to change the data type to DT_STR
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
March 9, 2006 at 5:07 am
Thanks for the info on the transormation, which I did discover too.
I was just wondering if there wasn't a more native way to do it. I can see using a transformation to cast a string from a flat file to a datetime or numeric, but from string to string, simply because, by default, the Excel source provider is defining things as wide strings?
Also, I'm wondering now what the "Input and Output Properties" tab on the Advanced Editor is supposed to be used for then? And when would you edit the External columns vs the Output columns or both?
So many questions, and BOL isn't really helpful in this manner!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply