I am converting a simple (at least in the SQL Server 2000 DTS days) to this
new SSIS. I have a data source (which is Oracle 8 using .Net ODBC provider)
and am trying to pump the data into a SQL Server table. I get an error that
it cannot convert between unicode and non-unicode data types. The data type
is text(26) in Oracle and the same on SQL. Looking at what it is trying to
map it goes from DT_WSTR (26) to DT_STR (50). I've searched the BOL and read
something about using the pipeline??? I tried defining a Data Conversion Data
Flow but couldn't figure it out. I've also gone through the tutorials, which
of course don't cover this. Can someone please point me in the right
direction? Thanks.

Hello Phill,

The thing about DTS is that it massaged the data for you. It wasn't as type
strict. SSIS is and that is one of the reasons you get the fantastic performance.

Yes you will need a Data Conversion transform in the pipeline.

In the transform check the box next to the column(s) you want to convert
In the grid at the bottom drop down the "Data Type" combo box and choose
the "Convert To" datatype.
This will create another column in the output from this transform that you
should use going downstream.


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Thanks for the quick response Allan. So for simple column copies I need to
add a Data Conversion between the Source and Destination objects. How do I
handle columns that required a little more logic? For example, my old DTS
package had an IF statement that looked at column A and depending on the
value assigned Column B a value. Or sometimes I have to use the instr
function to format a column. Is this done in the Script task, derived
column, or something else. I've looked at both and think it can be done in
the script task but I don't see how to reference the Input field. Thanks.

"Allan Mitchell" wrote:

Hello Phill,

OK there is some confusing terminology here

1. Column Copies i.e. copy a column are done using the "Copy Column" transform
and that simply puts another column into the pipeline with a different name
that is a replica of an existing column

2. The Data Conversion Transform converts data from one type to another.

3. The Derived Column allows you to introduce a column into the pipeline
that either replaces an existing column or is added in addition to the other
columns

If you want to do #3 but the value of the column is based on a condition
then you do this


<Test Condition> ? <True Part> : <False Part>

i.e.

Column1 == "Is this right" ? "Yes" : "No"


Make sense?


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Thanks. I think I'm getting the hang of it.

"Allan Mitchell" wrote: