Data Flow problem - incomplete varchar field

I have tried to set the properties for the column in the Datareader output,
but it won't accept the change. It gives me and error message that it
cannot change the data type. It won't say why.

I appreciate your suggestions. Do you have any others?

Dennis

"Todd C" <ToddC DeleteThis @discussions.microsoft.com> wrote in message
news:C5388437-4B10-4914-8DC8-002FA033ADA1@microsoft.com...
> Hello Dennis:
> I am guessing you have identified the problem with the DT_WSTR vs DT_STR
> data types in SSIS. (Personally, I don't like the fact that SSIS just
> ASSUMES
> that all your text fields are DT_WSTR. I think Microsoft needs to do a
> better
> job of reading and respecting the input data types. Sorry, got on a rant
> there.)
>
> Try right-clicking on the DataReader and select Advance Properties. I
> think
> there is a way to define the Data Type and size for all input columns. (I
> have not used the Data Reader much so am not 100% certain).
>
> HTH
> --
> Todd C
>
>
> "Irvine, Dennis" wrote:
>
>> I am working on migrating a database for SQL 2000 on a Windows 2000
>> server,
>> over to SQL 2005 on a Windows 2003 server
>>
>> A large amount of the data in the database is refreshed everyday by
>> pulling
>> data from an external ODBC source.
>>
>> On the SQL 2000 installation I used a DTS package to import the data from
>> the ODBC source and it works fine.
>>
>> On the SQL 2005 installation I completely re-wrote the DTS package into a
>> SSIS package.
>>
>> My problem is that there is a text field that only imports a portion of
>> the
>> field from the ODBC over to the SQL 2005 database.
>> The field is 400 chars long, but for some reason, only 200 chars or
>> less,
>> will come over to the new SQL 2005 field.
>>
>> I am using an ADO.net ODBC connection manager and a Datareader object to
>> get
>> the data from the ODBC source.
>>
>> Now on the old database I can get the whole 400 chars in this text field
>> to
>> come over. But in the new database it only reads part of it.
>>
>> I am thinking it has something to do with "Unicode" and the SQL 2005
>> package
>> reading the field as a DT_WSTR data type, even though the source field is
>> not that type.
>>
>> Anyone have an ideas?
>>
>>
 >> Stay informed about: Data Flow problem - incomplete varchar field 

Dennis:
The only suggestion I can give you is to steer away from ODBC if at all
possible.

Two reasons:
1) It is at least, what, 15? 20 years old? There are much more efficient
methods to connect to external data sources available natively in SSIS.
2) You need to have the ODBC DSN set up on the server with the same
properties in order for it to work.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Irvine, Dennis" wrote:

> I have tried to set the properties for the column in the Datareader output,
> but it won't accept the change. It gives me and error message that it
> cannot change the data type. It won't say why.
>
> I appreciate your suggestions. Do you have any others?
>
> Dennis
>
> "Todd C" <ToddC.TakeThisOut@discussions.microsoft.com> wrote in message
> news:C5388437-4B10-4914-8DC8-002FA033ADA1@microsoft.com...
> > Hello Dennis:
> > I am guessing you have identified the problem with the DT_WSTR vs DT_STR
> > data types in SSIS. (Personally, I don't like the fact that SSIS just
> > ASSUMES
> > that all your text fields are DT_WSTR. I think Microsoft needs to do a
> > better
> > job of reading and respecting the input data types. Sorry, got on a rant
> > there.)
> >
> > Try right-clicking on the DataReader and select Advance Properties. I
> > think
> > there is a way to define the Data Type and size for all input columns. (I
> > have not used the Data Reader much so am not 100% certain).
> >
> > HTH
> > --
> > Todd C
> >
> >
> > "Irvine, Dennis" wrote:
> >
> >> I am working on migrating a database for SQL 2000 on a Windows 2000
> >> server,
> >> over to SQL 2005 on a Windows 2003 server
> >>
> >> A large amount of the data in the database is refreshed everyday by
> >> pulling
> >> data from an external ODBC source.
> >>
> >> On the SQL 2000 installation I used a DTS package to import the data from
> >> the ODBC source and it works fine.
> >>
> >> On the SQL 2005 installation I completely re-wrote the DTS package into a
> >> SSIS package.
> >>
> >> My problem is that there is a text field that only imports a portion of
> >> the
> >> field from the ODBC over to the SQL 2005 database.
> >> The field is 400 chars long, but for some reason, only 200 chars or
> >> less,
> >> will come over to the new SQL 2005 field.
> >>
> >> I am using an ADO.net ODBC connection manager and a Datareader object to
> >> get
> >> the data from the ODBC source.
> >>
> >> Now on the old database I can get the whole 400 chars in this text field
> >> to
> >> come over. But in the new database it only reads part of it.
> >>
> >> I am thinking it has something to do with "Unicode" and the SQL 2005
> >> package
> >> reading the field as a DT_WSTR data type, even though the source field is
> >> not that type.
> >>
> >> Anyone have an ideas?
> >>
> >>
>
>
 >> Stay informed about: Data Flow problem - incomplete varchar field 

Yes, I realize that Todd. but the data is in a proprietary database and
the vendor only provides an ODBC driver to access it. Otherwise I would
need to do a "manual" process of generating a text file and importing the
text file.

What sticks in my craw is that this whole process works just fine on SQL
2000 server, but causes nothing but problems on a SQL 2005 server.

There must be a way to turn off this "unicode" default so that it will look
at the ODBC source and use a DT_STR datatype. The datatype on the ODBC
source is indeed a DT_STR type (VARCHAR) but the SSIS Datareader object
refuses to "see" it as a DT_STR type. It keeps insisting that it is a
DT_WSTR type. I think there must be some kind of switch or parameter I
can use to make it "see" it as a DT_STR type.

I will have to keep digging.

Thanks for your help

Dennis

"Todd C" <ToddC RemoveThis @discussions.microsoft.com> wrote in message
news:B289D6F8-A082-4C70-9B85-0C06ABB97442@microsoft.com...
> Dennis:
> The only suggestion I can give you is to steer away from ODBC if at all
> possible.
>
> Two reasons:
> 1) It is at least, what, 15? 20 years old? There are much more efficient
> methods to connect to external data sources available natively in SSIS.
> 2) You need to have the ODBC DSN set up on the server with the same
> properties in order for it to work.
>
> HTH
> --
> Todd C
>
> [If this response was helpful, please indicate by clicking the appropriate
> answer at the bottom]
>
>
> "Irvine, Dennis" wrote:
>
>> I have tried to set the properties for the column in the Datareader
>> output,
>> but it won't accept the change. It gives me and error message that it
>> cannot change the data type. It won't say why.
>>
>> I appreciate your suggestions. Do you have any others?
>>
>> Dennis
>>
>> "Todd C" <ToddC RemoveThis @discussions.microsoft.com> wrote in message
>> news:C5388437-4B10-4914-8DC8-002FA033ADA1@microsoft.com...
>> > Hello Dennis:
>> > I am guessing you have identified the problem with the DT_WSTR vs
>> > DT_STR
>> > data types in SSIS. (Personally, I don't like the fact that SSIS just
>> > ASSUMES
>> > that all your text fields are DT_WSTR. I think Microsoft needs to do a
>> > better
>> > job of reading and respecting the input data types. Sorry, got on a
>> > rant
>> > there.)
>> >
>> > Try right-clicking on the DataReader and select Advance Properties. I
>> > think
>> > there is a way to define the Data Type and size for all input columns.
>> > (I
>> > have not used the Data Reader much so am not 100% certain).
>> >
>> > HTH
>> > --
>> > Todd C
>> >
>> >
>> > "Irvine, Dennis" wrote:
>> >
>> >> I am working on migrating a database for SQL 2000 on a Windows 2000
>> >> server,
>> >> over to SQL 2005 on a Windows 2003 server
>> >>
>> >> A large amount of the data in the database is refreshed everyday by
>> >> pulling
>> >> data from an external ODBC source.
>> >>
>> >> On the SQL 2000 installation I used a DTS package to import the data
>> >> from
>> >> the ODBC source and it works fine.
>> >>
>> >> On the SQL 2005 installation I completely re-wrote the DTS package
>> >> into a
>> >> SSIS package.
>> >>
>> >> My problem is that there is a text field that only imports a portion
>> >> of
>> >> the
>> >> field from the ODBC over to the SQL 2005 database.
>> >> The field is 400 chars long, but for some reason, only 200 chars or
>> >> less,
>> >> will come over to the new SQL 2005 field.
>> >>
>> >> I am using an ADO.net ODBC connection manager and a Datareader object
>> >> to
>> >> get
>> >> the data from the ODBC source.
>> >>
>> >> Now on the old database I can get the whole 400 chars in this text
>> >> field
>> >> to
>> >> come over. But in the new database it only reads part of it.
>> >>
>> >> I am thinking it has something to do with "Unicode" and the SQL 2005
>> >> package
>> >> reading the field as a DT_WSTR data type, even though the source field
>> >> is
>> >> not that type.
>> >>
>> >> Anyone have an ideas?
>> >>
>> >>
>>
>>
 >> Stay informed about: Data Flow problem - incomplete varchar field 

As a further bit of update information. I am beginning to believe that
the problem I am having is due to the subject field on the ODBC database
having nulls embedded in the field. So the DT_WSTR data type is reading
the field up to the null and stopping.
Is there some other data type or some othe way to approach reading this
column of data even with the embedded nulls in the field?

Dennis

"Irvine, Dennis" <dennis.irvine RemoveThis @plumasnt.com> wrote in message
news:eu8Q4QxKJHA.1556@TK2MSFTNGP03.phx.gbl...
>I am working on migrating a database for SQL 2000 on a Windows 2000 server,
>over to SQL 2005 on a Windows 2003 server
>
> A large amount of the data in the database is refreshed everyday by
> pulling data from an external ODBC source.
>
> On the SQL 2000 installation I used a DTS package to import the data from
> the ODBC source and it works fine.
>
> On the SQL 2005 installation I completely re-wrote the DTS package into a
> SSIS package.
>
> My problem is that there is a text field that only imports a portion of
> the field from the ODBC over to the SQL 2005 database.
> The field is 400 chars long, but for some reason, only 200 chars or less,
> will come over to the new SQL 2005 field.
>
> I am using an ADO.net ODBC connection manager and a Datareader object to
> get the data from the ODBC source.
>
> Now on the old database I can get the whole 400 chars in this text field
> to come over. But in the new database it only reads part of it.
>
> I am thinking it has something to do with "Unicode" and the SQL 2005
> package reading the field as a DT_WSTR data type, even though the source
> field is not that type.
>
> Anyone have an ideas?
 >> Stay informed about: Data Flow problem - incomplete varchar field