Extract data from Sybase ASE 15 using SSIS 2008 in Unicode format

Question

I'm attempting to use SQL Server Integration Services (SSIS) 2008 R2 to extract data from a Sybase ASE 15 database.

I've managed to configure the OLE DB Source with the correct connection information and can see the tables and data. However no matter what I try it always returns DT_STR columns.

I would like to have the data returned in Unicode format, without using the Derived Column / Data Conversion task, as the destination tables are all defined with NVARCHAR (DT_WSTR) column and it would be a bit of a pain to have to go through every column just to change the type.

Is there a way to define the connection string / set defaults on the login / other method to ensure that the OLE DB Data Source returns DT_WSTR columns instead of DT_STR when running a query?

Many thanks, John

TsSkTo · Accepted Answer · 2013-10-07 15:04:20Z

0

Right click on the OLE DB Data source and select the Advanced Editor. Then go to the Input and output Properties tab. Select the column that you're importing and change its dataType on the properties display on the right.

Alternatively you could have your OLE DB Source use a query to return its columns. Your SELECT statement can then CAST() your columns into the type you need.

I don't know why you dont want to use a derived column/data transformation transformation tho. It seems like it would be just the same amount if not more work

edited Oct 7, 2013 at 15:04

answered Oct 7, 2013 at 14:33

TsSkTo

1,42013 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Cynicszm Over a year ago

Thanks for the suggestion. I don't want to use a derived columns / data conversion / editing the columns in the Advanced editor. I want to know if there is anything I can set in the connection details, server settings or SSIS code page so I don't have to mess around with this kind of setting and just work with DT_WSTR returned from the query.

TsSkTo Over a year ago

I added another method, but i still dont see why you would want to do it like that. SSIS makes it extremely easy to manipulate data with its components. But oh well

Cynicszm Over a year ago

I want the type set on the Ole db source so I don't have to manually mess around with other components for each string column. There are around 1000 columns!

Collectives™ on Stack Overflow

Extract data from Sybase ASE 15 using SSIS 2008 in Unicode format

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related