UKC

Always happy to try for the 'post most unrelated to climbing' award. I know there are several DB geeks on here

Very early days for me in terms of SSIS so this is probably achingly obvious.

As a dummy example I've got a very simple table with two colums (Name - VARCHAR (50) and LastName - NVARCHAR (50)) and a spreadsheet ready to be the dataflow destination for the data in the table with columns outputName and outputLastName.

Got the Dataflow task on the Control Flow canvas, and on the Control Flow canvas I've got:

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
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 around this I can see 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)


Got some screen shots which might make it clearer. Virtual beer in it if anyone can help, maybe even a real one if you live in the manc area.

In reply to ThunderCat:

Couple of things for you to look at.

Why would you use VARCHAR for Name and NVARCHAR for Lastname? Unless you are expecting someone's surname to include Unicode characters (possible if you are storing multi language names) but not expecting them to be used in the person's name???

Dumping huge amounts of data into excel is a bit of an archaic way of manipulating data. If you are doing this for a large number of users I would suggest Qlikview to allow users to easily manipulate the data down to manageable subsets or if you only need to supply the data to a few users I would go for Microsoft Access and AODB dsn-less connections to manipulate the data.

In reply to mgco3:

None of this is real data, it's just me sitting down with a book and knocking up silly examples to try and figure out why (and how) some things work and something's don't. There was a tutorial in a book I couldn't get to work so I boiled it down to those two columns. It won't let me push the varchar column through to the Excel destinataion, put it will let me push the nvarchar column through.

In reply to ThunderCat:
Easiest way to remove the unneccesary column is within your derived column transformation:

"Derived Column Name --> Name_New
Derived Column --> [add as new column]
Expression --> (DT_WSTR,50)Name"

Replace the "[add as new colum]" to "Replace Name"

If you have any issues with the output column datatype you can right-click on the derived column task and click "show advanced editor". On the "input and output properties" tab expand "Derived Column Output" and "Output Columns" then click on the Name field and change the data type.

Good luck.

In reply to ThunderCat:

You don't say what version of SQL Server you're using, but Excel connections can be fiddly pre SSIS 2012. Do you have SQL Server Developer Edition installed on your machine? If so, a great way to learn how to build Excel Import/Exports in SSIS is to use the SQL Wizard to generate an SISS package for you which you can take a look at. If you right click the database, go Tasks, Export Data, follow the wizard through and on the final screen select save package rather than run. You can then add the .dtsx file to your solution to open it in BIDs and see how the package has been built. It's going to be down to Unicode datatypes I'd have thought.