Automatically convert Unicode Strings to Regular Strings

Is there a way in SSIS 2008 to automatically convert Unicode strings to regular strings? Is there perhaps a package setting or something?

I am reading several Jet datbases and all the string fields are coming across as Unicode Strings and the destination table they are being inserted into are defined as regular strings.

I don't want to create a data conversion transformation for every string field in all these tables I'm importing.

I'm just hoping there is a property that states to treat unicode as the default code page of 1252.


Pat Rogers

more ▼

asked Oct 26, 2009 at 05:47 PM in Default

Pat Rogers gravatar image

Pat Rogers
31 2 2 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Perhaps I am missing something, but as long as you aren't using characters that require Unicode, nothing prevents you from assigning the values to "standard" columns or variables. The implicit conversion is done for you.

declare @source as nvarchar(100) declare @target as varchar(100)

set @source = 'Paradimethylaminobenzaldehyde' set @target = @source

select @Target as [@Target], @source as [@source]
more ▼

answered Oct 26, 2009 at 06:18 PM

Bob Hovious gravatar image

Bob Hovious
1.6k 5 6 9

I'm not doing it via code (c#, vb.net). It was all in the SSIS package reading from Access databases and inserting into Sql Server 2008 db. In Sql Server, the fields are defined as varchar, but for whatever reason, using the OLEDB connection manager on the Jet Database, the string fields are coming in as unicode strings.
Oct 26, 2009 at 07:07 PM Pat Rogers
At this point, I can't tell you anymore than that T-SQL will implicitly do the conversion for you. What sort of error(s) are you running up against?
Oct 26, 2009 at 07:40 PM Bob Hovious
In the SSIS package, I am using an OleDB Connection to a MSAccess database for the source. I am also using an OLEDB Connection to Sql Server 2008 for the destination. The mapping fails since it states it cannon convert the Unicode value to string value.
Oct 26, 2009 at 08:52 PM Pat Rogers
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 26, 2009 at 05:47 PM

Seen: 3510 times

Last Updated: Oct 26, 2009 at 11:41 PM