Data Flow problem - incomplete varchar field

#1

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?

#2

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:

Quote:

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?

#3

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:

Quote:

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?

#4

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:

Quote:

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?

#5

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:

Quote:

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?

#6

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:

Quote:

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?

#7

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:

Quote:

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?

#8

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:

Quote:

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?

#9

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:

Quote:

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?

#10

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 (AT) discussions (DOT) microsoft.com> wrote

Quote:

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?