> 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,
>>
>> 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
>>> 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.
>>>

"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 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,
> >>
> >> 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
> >>> 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.
> >>>
>
>