September 20, 2015 at 8:34 am
Very early days beginning SQL Server / SSIS, working through tutorials and experimenting in the office and I've hit a problem very early on.
Got a small sample table in SQL Server: Name (VCHAR[50]) and LastName(NVARCHAR [50]) with a couple of records in. I've set them up specifically as different data types to try and get a handle on understanding conversions.
I'm trying to use a dataflow to move the contents to an Excel spreadsheet (set up with two columns called outputName and outputLastName.
An OLE DB Source pointing at the table and retrieving the 2 columns (working fine)
An Excel Destination pointing at the spreadsheet. Linked, and columns mapped
When I try and execute it I get the error message "Columns Name and outputName cannot convert between unicode and non-unicode strings data types"
So next step is to put the Derived Column transformation in between the two...
I thought I would be able to cast "Name" to a unicode datatype using:
Derived Column Name --> Name
Derived Column --> Replace 'Name'
Expression --> (DT_WSTR,50)Name
So that I would still only have two output columns...I thought that this would push out the column as a DT_WTSR datatype, rather than the DT_STR type it's coming in as...but the error still persists.
The only way I can make the transform work is to do the transformation as
Derived Column Name --> Name_New
Derived Column --> [add as new column]
Expression --> (DT_WSTR,50)Name
So that I end up with three columns: Name, LastName and Name_New (and I just ignore the original Name column)
Is this the only way I could do this in this situation?
This is probably explained badly - I can rattle off screen shots if it makes it easier. Appreciate any help you can offer.
September 20, 2015 at 10:20 am
Rather than a derived column, you should consider using a data conversion transformation.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 20, 2015 at 10:31 am
Thanks Phil, I'll give that a try when I get back in the office tomorrow. But out interest, am I right in thinking that a derived column can do things to the column (remove nulls, concatenate strings, etc etc etc) but it can't actually change the output type in the way I described?
My example came from a tutorial exercise in the book that I couldn't get to work...the tutorial asked you to bring back NVARCHAR column data back to an xls file, but I had accidentally created the source table as Varchar, hence the error....
Before i went back and changed the table to NVARCHAR to get on with the tutorial, I wondered if I could cheat and cast it in the derived column transformation using (DT_WSTR,50)xxxxxxx. But it wouldn't let me.
I'm just curious as to the reason behind things not working. Don't want to plough on with the book without nailing those questions as I hit them.
Chances are when I get back to the text book, the next thing under discussion will be data conversions transformations... 🙂
Thanks for your time.
September 20, 2015 at 10:41 am
ChunkyLover1974 (9/20/2015)
Thanks Phil, I'll give that a try when I get back in the office tomorrow. But out interest, am I right in thinking that a derived column can do things to the column (remove nulls, concatenate strings, etc etc etc) but it can't actually change the output type in the way I described?My example came from a tutorial exercise in the book that I couldn't get to work...the tutorial asked you to bring back NVARCHAR column data back to an xls file, but I had accidentally created the source table as Varchar, hence the error....
Before i went back and changed the table to NVARCHAR to get on with the tutorial, I wondered if I could cheat and cast it in the derived column transformation using (DT_WSTR,50)xxxxxxx. But it wouldn't let me.
I'm just curious as to the reason behind things not working. Don't want to plough on with the book without nailing those questions as I hit them.
Chances are when I get back to the text book, the next thing under discussion will be data conversions transformations... 🙂
Thanks for your time.
As far as I can see, it should work. But for obvious reasons, I don't use it that way & therefore can't be certain.
One important point to note is that any derived columns which you create do not exactly 'do things to a column'. Instead, they create new columns based on existing ones, which can be used subsequently in your data flow.
So in your case, after creating your derived column, it would be necessary to replace the mapping from the base column to the Excel column with the derived column, if that makes sense.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 20, 2015 at 10:52 am
I understand that it doesn't 'do things to a column' as such (sorry, my poor use of language there!) - I'm trying to understand what it exactly does do.
I know it wouldn't actually affect the source data in any way..I'm assuming it creates a 'derived' column for the output destination to consume.
I can see two options in the derived column transformation editor- replace the original column or create a new one.
Creating a new column with the DT_WSTR casting works fine.
But even if I start from scratch, use the DT_WSTR,50 casting to replace the existing column, and THEN map that output to the excel column, I get the unicode / non-unicode warning and failure again.
As far as I'm concerned this should work, but from a novice point of view i know there are probably some obvious reasons why it doesn't (and that's what I'm trying to understand). This would be loads easier if we had the screen in front of us.
Not to worry Phil - I'll have a stab with your suggestion tomorrow and I think there maybe a visiting SQL consultant in the building at some point this week who I can grab for 10 minutes. Thanks again for you time - it's really appreciated.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply