Re: 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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:B289D6F8-A082-4C70-9B85-0C06ABB97442@xxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C5388437-4B10-4914-8DC8-002FA033ADA1@xxxxxxxxxxxxxxxx
> 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?
>>
>>



.

Relevant Pages

  • Re: Sybase Source. What "data flow source" to use?
    ... "Allan Mitchell" wrote: ... when I right-click over a SQL 2005 database and select Import ... datasources defined in ODBC Datasources on my computer, ...
    (microsoft.public.sqlserver.dts)
  • Re: Type Mismatch Error Message
    ... >> Using Text as SQL data type. ... I created a database on a SQL server manually and matched the column names ... I thought you "didn't use the database wizard?" ... >> Data type in form is No Constraints. ...
    (microsoft.public.frontpage.programming)
  • Re: Sybase Source. What "data flow source" to use?
    ... when I right-click over a SQL 2005 ... database and select Import Data, the list of providers does not include ... either A) the list of datasources defined in ODBC Datasources on my computer, ... I've been so excited about SSIS and telling my co-workers how ...
    (microsoft.public.sqlserver.dts)
  • Re: Sybase Source. What "data flow source" to use?
    ... but could still use DTS to move data. ... when I right-click over a SQL 2005 database and select Import ... datasources defined in ODBC Datasources on my computer, ...
    (microsoft.public.sqlserver.dts)
  • RE: Linking to an SQL Database
    ... registry entry to change the JET engine ODBC Table ... >|>| When linking to a SQL database through MS Access ... >|>| database it connects to has been upgrade to an SQL ...
    (microsoft.public.access.externaldata)
Quantcast