Automatically convert Unicode Strings to Regular Strings

Question

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.

Thanks,

Pat Rogers

Answer 1

0

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]

answered Oct 26 '09 at 06:18 PM

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 '09 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 '09 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 '09 at 08:52 PM Pat Rogers

(comments are locked)

Automatically convert Unicode Strings to Regular Strings

Follow this question

Related Questions