conversion from nText to varchar

Hi All,

Is it possible to convert a nText column in the source to varchar in the destination. I tried using a DataConversion block but there is no option for Ntext, I think am misising somehting here. Can someone guide me here?

thanks in advance,

Hi..

Use 'derived column'..

It is impossible to change nText column to varchar directly, but to change nText to text and text to varchar column is possible.

If [AAA] column is input, use this expression.

(DT_STR,4000,1252)((DT_TEXT,1252)AAA)

HTH.

ADConsulting / SQLeader.com / Daeseong Han

|||

I believe ntext is considered as unicode string. Please try that out.

Thanks,

S Suresh

|||

It works correctly, if input column type is unicode string type.

http://www.sqlleader.com/pds/board/ss2005ssis/editor/aaaa.jpg

conversion error after promoting to production box

Problem: data conversion component going from unicode DT_WSTR to DT_STR with 1252 codepage using a Source Provider=IBMDADB2.1;

So.. I developed the package on the testdb without any errors. However, when I promote the package to the production box, I get this conversion error. I thought that it was specific to one column, so I set the ignore truncation error in the data flow. The next column provided then caused the same error.

what settings can I look at changing to prevent this error?

what data is being truncated or is the unicode conversion map failing?

the max(length) is 270 on a varchar 300. I also read something about tabs in this type of conversions failing?

Why would this be specific to the box? I have checked the collation on both DB's and they are SQL_Latin1_General_CP1_CI_AS

what is the normal workaround for this? is AlwaysUseDefaultCodePage a part of the solution?

<Error>The "output column "STATUSRENEWAL" (4477)" failed because truncation occurred, and the truncation row disposition on "output column "STATUSRENEWAL" (4477)" specifies failure on truncation.

</Error>

thanks for your time.

To find out the rows causing this, redirect the failing rows to error output and save it e.g. to a file.
The problem could be caused by Unicode characters in DT_WSTR columns that can not be represented by codepage 1252. The workaround would depend on what you are going to do with this? Are you OK with loss of information - then ignore the error; if not - don't convert Unicode data to single-byte code page; or maybe you need to cleanup the source data to avoid international characters that can't be fit to codepage 1252?sqlsql

Conversion error

Hi all,

Basically I am trying to create a package that will

(A) Create a table with specified datatypes

(B) Use a text Source file for the data

(C) on Success \ Completion of the "Execute SQL" transform the data from the text into the table.

Connect to DB <-- [TRANSFROM]-- Text (Source) <-- Execute SQL (Create Table)

It all seems to work now but when I run the package I get the following error

The number of failing rows exceeds the maximum specified.

TransformCopy 'DTSTransformation_6'conversion error: Conversion invalid for datatypes on column on pair 1 (source column 'Col007' (DBTYPE_STR),destination column 'Rec_Amt' (DBTYPE_CY)).

But when I go into the TransformDataTask, under transformation and test that column it all works fine, infact I tested all the columns and they all seem to work fine.

It also seems to be creating the same table twice first in the " Execute SQL" task and then again for some reason in the "DataTransform" task. I dont know if that is realted to the problem or not though.

Any idea's or suggestions I could try ?

Im very new to SQL 2000 & DTS so dont rule out any very newbie errors :)

Thanks

I'm not sure what steps you created, so I'm uncertain as to why it would duplicate the table. I would create one step to read the text file and create the table, and another to fill it. Here's a broad reference with guidelines, and if you have further questions you can check out Books Online for SQL Server 2000 to read more:

http://support.microsoft.com/default.aspx/kb/242377

Buck Woody

Conversion error

Hi all,

Basically I am trying to create a package that will

(A) Create a table with specified datatypes

(B) Use a text Source file for the data

(C) on Success \ Completion of the "Execute SQL" transform the data from the text into the table.

Connect to DB <-- [TRANSFROM]-- Text (Source) <-- Execute SQL (Create Table)

It all seems to work now but when I run the package I get the following error

The number of failing rows exceeds the maximum specified.

TransformCopy 'DTSTransformation_6'conversion error: Conversion invalid for datatypes on column on pair 1 (source column 'Col007' (DBTYPE_STR),destination column 'Rec_Amt' (DBTYPE_CY)).

But when I go into the TransformDataTask, under transformation and test that column it all works fine, infact I tested all the columns and they all seem to work fine.

It also seems to be creating the same table twice first in the " Execute SQL" task and then again for some reason in the "DataTransform" task. I dont know if that is realted to the problem or not though.

Any idea's or suggestions I could try ?

Im very new to SQL 2000 & DTS so dont rule out any very newbie errors :)

Thanks

I'm not sure what steps you created, so I'm uncertain as to why it would duplicate the table. I would create one step to read the text file and create the table, and another to fill it. Here's a broad reference with guidelines, and if you have further questions you can check out Books Online for SQL Server 2000 to read more:

http://support.microsoft.com/default.aspx/kb/242377

Buck Woody

Control Source field as value list

Hi!
I have a strored proc which adds up fields separated by semicolon into a new one and I'd like to use it as a combo box's control source field as a Value list. But it's just insert the whole field into one line like value1;value2;value3 .
Is there any way to get these values selectable in the combo box?

Thanks in advance for any help.

VillWhat interface are you using. Access? You should post this in the appropriate forum, as it is not a SQL Server issue.

Continuous nonstop pumping of data

I have one data pump in a series that was pumping in too many records. Doing an independent query of the source table, I found there was about 140,000 records. My pump uses a variable for the source query, nothing fancy just a simple SELECT * FROM table WHERE DateField > '4/6/2006 12:00:00AM'. The Destination is local on the SQL Server and is set by a variable, and does a fast load. When I went away and checked in BIDS while it was running (the data flow tab where you can see the record count) it was at 28,000,000 and still going!

Any ideas what could be causing this? As I say there are only 140,000 records and no joins in the query--is this a bug someone has run into before?

Thanks,

Kayda

Sounds like you are using a variable to populate the SQL query for the date constraint, but that it isn't getting populated correctly.|||Actually, even without the date constraint the number of records is only 280,000 or so. Actually, I see that this is happening when it loses its connection to the database. This package has to access information through different dialup connections, and these connections are dialed through code in the package. It seems when the dialup connection is dropped then the datapump task that is running just appears to keep going infinitely, saying it is pumping millions of records. Any idea why this would happen and the pump would not just fail?