Data Flow problem - incomplete varchar field

Data Flow problem - incomplete varchar field

Post by Irvine, De » Sun, 12 Oct 2008 05:10:17


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?
 
 
 

Data Flow problem - incomplete varchar field

Post by VG9kZCB » Tue, 14 Oct 2008 23:25:01

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

 
 
 

Data Flow problem - incomplete varchar field

Post by Irvine, De » Thu, 16 Oct 2008 03:06:51

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
 
 
 

Data Flow problem - incomplete varchar field

Post by VG9kZCB » Thu, 16 Oct 2008 03:46:01

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]
 
 
 

Data Flow problem - incomplete varchar field

Post by Irvine, De » Fri, 17 Oct 2008 08:09:02

es, 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" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...

 
 
 

Data Flow problem - incomplete varchar field

Post by den » Fri, 17 Oct 2008 11:39:06

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