Error data types do not match

I have a flatfile source with qty,title and author..i add a lookup and in that i establish relation between title and titel of pubs database..but i am getting an error..
one or more columns do not hav supported data types,or their datatypes do not match..i checked both hav dt_str,and in database pubs title is varchr..so why this eror?I think the error is quite clear. Open your lookup and navigate to the olumns tab. Now hover over each column you wish to join on the left and right hand sides. The tooltip will give you the SSIS types concerned. Do they match? If not what are they and why do you think they should? If they do not match use the Data Conversion or Derived Column transforms to convert as required.|||hi daaren,
they do match..both dt_str..why still error?
thks|||

Check the error rmessage carefully, maybe it is not the lookup component which is raising the error, or is this in the Lookup UI, in which case it obviously is?

Are the lengths and code pages appropiate?

Failing that post some sample data from the file, the buffer types used on the file output path, and the DDL for the lookup table and I'll try and reproduce to see the issue.

Error data types do not match

I have a flatfile source with qty,title and author..i add a lookup and in that i establish relation between title and titel of pubs database..but i am getting an error..
one or more columns do not hav supported data types,or their datatypes do not match..i checked both hav dt_str,and in database pubs title is varchr..so why this eror?I think the error is quite clear. Open your lookup and navigate to the olumns tab. Now hover over each column you wish to join on the left and right hand sides. The tooltip will give you the SSIS types concerned. Do they match? If not what are they and why do you think they should? If they do not match use the Data Conversion or Derived Column transforms to convert as required.|||hi daaren,
they do match..both dt_str..why still error?
thks|||

Check the error rmessage carefully, maybe it is not the lookup component which is raising the error, or is this in the Lookup UI, in which case it obviously is?

Are the lengths and code pages appropiate?

Failing that post some sample data from the file, the buffer types used on the file output path, and the DDL for the lookup table and I'll try and reproduce to see the issue.

Error converting data type varchar to numeric.

HI
i have a very big problem.
i have a cloumn name Revenue and i have differt types of revenue in it such as A, B, C.
what i want to do is that i want to say that when the Revenue column is A then sum the tola mount and put in in a new column name A when b then again same thing.
this is my code

case when Revenue='A' then Sum(total_amt) else ' ' end as A

but it is giving me and error Error converting data type varchar to numeric.
please help

Quote:

Originally Posted by voroojak

HI
i have a very big problem.
i have a cloumn name Revenue and i have differt types of revenue in it such as A, B, C.
what i want to do is that i want to say that when the Revenue column is A then sum the tola mount and put in in a new column name A when b then again same thing.
this is my code

case when Revenue='A' then Sum(total_amt) else ' ' end as A

but it is giving me and error Error converting data type varchar to numeric.
please help

first, why the error.

because your case statement said when revenue = 'A' return the sum of total_amt else return blank...the case statement should return (the then part and the else part) the same datatype or at least can be converted to the same datatype.

try ...else sum(0) end as A, ...or ....else 0 end as A

Error between string data types

I reinstalled SQL Server, setup new connetions in my existing project and then pointed the existing controls in my SSIS packege to my new OLE DB Connection manager.

When I run my package, now I get:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "TransactionDate" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "TransactionTime" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "AccountNumber" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "TransactionCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "FieldCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "NewValue" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "InternalExternalFlag" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "RecovererCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "AS_400_UserID" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "ProductLoanTypeCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "NotUsed" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [DTS.Pipeline]: "component "OLE DB Source 1" (73)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task 1 [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task 1: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

and this error. I did create a new connection but also made sure all my components were using it so not sure why I still get this:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Package: The connection "{35FE7FF5-A1F5-4016-8C11-0B88A90AE3F7}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Error at Package: The connection "{35FE7FF5-A1F5-4016-8C11-0B88A90AE3F7}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Error at Execute SQL Task [Execute SQL Task]: Connection manager "{35FE7FF5-A1F5-4016-8C11-0B88A90AE3F7}" does not exist.

Error at Execute SQL Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

nevermind, missed a control.|||

Just curious, what component did you miss. I am having the same issue and cannot figure out the solution.

|||

I'm curious also, having the same issue

John R. wrote:

Just curious, what component did you miss. I am having the same issue and cannot figure out the solution.

|||

Robert,

Please be more specific. What are you trying to do and what error message are you getting?

If your error messages are similar to above it is probably because you are trying to do implicit conversions between unicode and non-unicode data. You can do explicit conversions using the Data Conversion or Derived Column components.

-Jamie

|||

Jamie,

I'm using SQL Server 2005 Developer Edition.

I'm using the 'Tasks-Export Data..' context menu to export all tables from my SQL Server Database to an empty Access 2003 database.

The operation fails on step 'Validating'. When I click messages the following message is displayed (among others):

Error 0xc02020f6: Data Flow Task: Column "Foutcode" cannot convert between unicode and non-unicode string data types.
(SQL Server Import and Export Wizard)

Can you point me to the right direction for finding the Data Conversion or Derived Column components? They do not seem to be part of the 'Tasks-Export Data' wizard?

__

Robert

|||

Those components are SSIS component that are available in the the Business Intelligence Development Studio (BIDS). You can use them in packages that you build yourself rather than with the wizards in SSMS (which I can't claim to know much about to be honest).

You will find that SSIS is a bit stricter than DTS was by not allowing implicit data conversions that DTS previously WOULD have allowed. This is a best practise initiative though one side affect is that it *could* cause the problems you are seeing now.

Open the package that the wizard creates for you and see if there are any implicit conversions going on.

-Jamie

|||

I would understand if I would get this error while importing lets say from Oracle to SQL Server.

But man, this is Microsoft Access and Microsoft SQL Server, don't you guys talk to each other?

In the worst case you could make an option in a wizard to allow implicit conversions. Now I have to spend many hours to fix wizard created package, while in previous DTS I could have done this in a minute.

The other "feature" of new SSIS is that "Drop and recreate table" feature does not work anymore.

Next time I will remove some of the features in my program and call it a brand new completely redesigned program. "One of the feature of my program is that it does not have this feature anymore" how do you like that? :)

|||

This is insane, you can't use SSIS anymore, it complains about everything. Where DTS would not give a single error, SSIS spits out 200 and you have to deal with even when you know implicit conversion is ok and would not damage your data.

SSIS stops the whole package on about every single warning. You should guys implement "skip error" option. It is taking sooooo much time, and for nothing.

|||

Hi jamie,

I'm having the same problem. I'm trying to import from an XLS file to an Oracle database (this error happens even if I go from XLS to SQL as well).

Here is what I've got:

Excel File Source

Data Conversion Object

OLE DB Destination

When I got the error at first- I added the "Data Conversion" Object - within that object the defaut setting was "Unicode string [DT_WSTR]" - I quickly changed this option to "String [DT_STR]" and still get the same error.

Oracle data type on the destination is Varchar2

Please Help.

steve

|||

I am also experiancing these stupid messages! (ie: Error 0xc02020f6: Data Flow Task: Column cannot convert between unicode and non-unicode string data types.)

It's insane that the "new" DTS (called SQL Server Import and Export Wizard) actually works worse than the old DTS. You'd think the guys making it would have tried harder to make it a better product (instead they made it worse).

I actually saved all the EXE and DLL files that the old DTS (from SS2000) used. I probably use the old DTS 90% of the time - because it works. Occasionaly i'll try the new DTS again, it'll throw these stupid exceptions, i'll close it, open the old DTS, and it will load the data without a problem.

If you're getting these errors, i suggest switching back to the old DTS - you'll find everything works.

|||

MrGTI wrote:

I am also experiancing these stupid messages! (ie: Error 0xc02020f6: Data Flow Task: Column cannot convert between unicode and non-unicode string data types.)

It's insane that the "new" DTS (called SQL Server Import and Export Wizard) actually works worse than the old DTS. You'd think the guys making it would have tried harder to make it a better product (instead they made it worse).

I actually saved all the EXE and DLL files that the old DTS (from SS2000) used. I probably use the old DTS 90% of the time - because it works. Occasionaly i'll try the new DTS again, it'll throw these stupid exceptions, i'll close it, open the old DTS, and it will load the data without a problem.

If you're getting these errors, i suggest switching back to the old DTS - you'll find everything works.

This is a bad piece of advice -- and it's uninformed at best. Sorry.

DTS has been replaced by SSIS, not the SQL Server Import and Export Wizard. And it works FAR, FAR, FAR better than DTS when you understand how it operates and why it operates the way it does.

Do you want to know how to fix the unicode/non-unicode issue, or did you just come here to rant?|||

I disagree. The old DTS was replaced by the SQL Server Import and Export Wizard. Take a look:

They're both designed to be simple ways of loading or exporting data. SSIS is (from what i understand), the more complex way of creating packages in Management Studio to automate the import/export. You're expected to run them many times. The DTS i know is used for simple 1 time import/export tasks.

And as for ranting - people searching the web for a solution to the same problem will want a solution. This thread never gave any solution. My response gives people a solution - switch to the old DTS, and you'll have your data loaded without wasting any more time on the problem.

|||

MrGTI wrote:

I disagree. The old DTS was replaced by the SQL Server Import and Export Wizard. Take a look:

I see you're point, but still, DTS was replaced by SSIS. Each has their own Import/Export wizard.

And if you're going against Excel files, then this is a driver issue and it has nothing to do with SSIS.|||

I had the data in tab delimited text files. Plain text in a TXT file, into an exisiting table in SS2005. It doesn't get any more basic than that.

So when when something as simple as that doesn't work, you can understand why switching back to the old DTS (from SS200) makes sense - because it still works, without an error.

Error between string data types

I reinstalled SQL Server, setup new connetions in my existing project and then pointed the existing controls in my SSIS packege to my new OLE DB Connection manager.

When I run my package, now I get:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "TransactionDate" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "TransactionTime" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "AccountNumber" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "TransactionCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "FieldCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "NewValue" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "InternalExternalFlag" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "RecovererCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "AS_400_UserID" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "ProductLoanTypeCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "NotUsed" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [DTS.Pipeline]: "component "OLE DB Source 1" (73)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task 1 [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task 1: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

and this error. I did create a new connection but also made sure all my components were using it so not sure why I still get this:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Package: The connection "{35FE7FF5-A1F5-4016-8C11-0B88A90AE3F7}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Error at Package: The connection "{35FE7FF5-A1F5-4016-8C11-0B88A90AE3F7}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Error at Execute SQL Task [Execute SQL Task]: Connection manager "{35FE7FF5-A1F5-4016-8C11-0B88A90AE3F7}" does not exist.

Error at Execute SQL Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

nevermind, missed a control.|||

Just curious, what component did you miss. I am having the same issue and cannot figure out the solution.

|||

I'm curious also, having the same issue

John R. wrote:

Just curious, what component did you miss. I am having the same issue and cannot figure out the solution.

|||

Robert,

Please be more specific. What are you trying to do and what error message are you getting?

If your error messages are similar to above it is probably because you are trying to do implicit conversions between unicode and non-unicode data. You can do explicit conversions using the Data Conversion or Derived Column components.

-Jamie

|||

Jamie,

I'm using SQL Server 2005 Developer Edition.

I'm using the 'Tasks-Export Data..' context menu to export all tables from my SQL Server Database to an empty Access 2003 database.

The operation fails on step 'Validating'. When I click messages the following message is displayed (among others):

Error 0xc02020f6: Data Flow Task: Column "Foutcode" cannot convert between unicode and non-unicode string data types.
(SQL Server Import and Export Wizard)

Can you point me to the right direction for finding the Data Conversion or Derived Column components? They do not seem to be part of the 'Tasks-Export Data' wizard?

__

Robert

|||

Those components are SSIS component that are available in the the Business Intelligence Development Studio (BIDS). You can use them in packages that you build yourself rather than with the wizards in SSMS (which I can't claim to know much about to be honest).

You will find that SSIS is a bit stricter than DTS was by not allowing implicit data conversions that DTS previously WOULD have allowed. This is a best practise initiative though one side affect is that it *could* cause the problems you are seeing now.

Open the package that the wizard creates for you and see if there are any implicit conversions going on.

-Jamie

|||

I would understand if I would get this error while importing lets say from Oracle to SQL Server.

But man, this is Microsoft Access and Microsoft SQL Server, don't you guys talk to each other?

In the worst case you could make an option in a wizard to allow implicit conversions. Now I have to spend many hours to fix wizard created package, while in previous DTS I could have done this in a minute.

The other "feature" of new SSIS is that "Drop and recreate table" feature does not work anymore.

Next time I will remove some of the features in my program and call it a brand new completely redesigned program. "One of the feature of my program is that it does not have this feature anymore" how do you like that? :)

|||

This is insane, you can't use SSIS anymore, it complains about everything. Where DTS would not give a single error, SSIS spits out 200 and you have to deal with even when you know implicit conversion is ok and would not damage your data.

SSIS stops the whole package on about every single warning. You should guys implement "skip error" option. It is taking sooooo much time, and for nothing.

|||

Hi jamie,

I'm having the same problem. I'm trying to import from an XLS file to an Oracle database (this error happens even if I go from XLS to SQL as well).

Here is what I've got:

Excel File Source

Data Conversion Object

OLE DB Destination

When I got the error at first- I added the "Data Conversion" Object - within that object the defaut setting was "Unicode string [DT_WSTR]" - I quickly changed this option to "String [DT_STR]" and still get the same error.

Oracle data type on the destination is Varchar2

Please Help.

steve

|||

I am also experiancing these stupid messages! (ie: Error 0xc02020f6: Data Flow Task: Column cannot convert between unicode and non-unicode string data types.)

It's insane that the "new" DTS (called SQL Server Import and Export Wizard) actually works worse than the old DTS. You'd think the guys making it would have tried harder to make it a better product (instead they made it worse).

I actually saved all the EXE and DLL files that the old DTS (from SS2000) used. I probably use the old DTS 90% of the time - because it works. Occasionaly i'll try the new DTS again, it'll throw these stupid exceptions, i'll close it, open the old DTS, and it will load the data without a problem.

If you're getting these errors, i suggest switching back to the old DTS - you'll find everything works.

|||

MrGTI wrote:

I am also experiancing these stupid messages! (ie: Error 0xc02020f6: Data Flow Task: Column cannot convert between unicode and non-unicode string data types.)

It's insane that the "new" DTS (called SQL Server Import and Export Wizard) actually works worse than the old DTS. You'd think the guys making it would have tried harder to make it a better product (instead they made it worse).

I actually saved all the EXE and DLL files that the old DTS (from SS2000) used. I probably use the old DTS 90% of the time - because it works. Occasionaly i'll try the new DTS again, it'll throw these stupid exceptions, i'll close it, open the old DTS, and it will load the data without a problem.

If you're getting these errors, i suggest switching back to the old DTS - you'll find everything works.

This is a bad piece of advice -- and it's uninformed at best. Sorry.

DTS has been replaced by SSIS, not the SQL Server Import and Export Wizard. And it works FAR, FAR, FAR better than DTS when you understand how it operates and why it operates the way it does.

Do you want to know how to fix the unicode/non-unicode issue, or did you just come here to rant?|||

I disagree. The old DTS was replaced by the SQL Server Import and Export Wizard. Take a look:

They're both designed to be simple ways of loading or exporting data. SSIS is (from what i understand), the more complex way of creating packages in Management Studio to automate the import/export. You're expected to run them many times. The DTS i know is used for simple 1 time import/export tasks.

And as for ranting - people searching the web for a solution to the same problem will want a solution. This thread never gave any solution. My response gives people a solution - switch to the old DTS, and you'll have your data loaded without wasting any more time on the problem.

|||

MrGTI wrote:

I disagree. The old DTS was replaced by the SQL Server Import and Export Wizard. Take a look:

I see you're point, but still, DTS was replaced by SSIS. Each has their own Import/Export wizard.

And if you're going against Excel files, then this is a driver issue and it has nothing to do with SSIS.|||

I had the data in tab delimited text files. Plain text in a TXT file, into an exisiting table in SS2005. It doesn't get any more basic than that.

So when when something as simple as that doesn't work, you can understand why switching back to the old DTS (from SS200) makes sense - because it still works, without an error.

Error between string data types

I reinstalled SQL Server, setup new connetions in my existing project and then pointed the existing controls in my SSIS packege to my new OLE DB Connection manager.

When I run my package, now I get:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "TransactionDate" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "TransactionTime" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "AccountNumber" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "TransactionCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "FieldCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "NewValue" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "InternalExternalFlag" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "RecovererCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "AS_400_UserID" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "ProductLoanTypeCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "NotUsed" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [DTS.Pipeline]: "component "OLE DB Source 1" (73)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task 1 [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task 1: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

and this error. I did create a new connection but also made sure all my components were using it so not sure why I still get this:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Package: The connection "{35FE7FF5-A1F5-4016-8C11-0B88A90AE3F7}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Error at Package: The connection "{35FE7FF5-A1F5-4016-8C11-0B88A90AE3F7}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Error at Execute SQL Task [Execute SQL Task]: Connection manager "{35FE7FF5-A1F5-4016-8C11-0B88A90AE3F7}" does not exist.

Error at Execute SQL Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

nevermind, missed a control.|||

Just curious, what component did you miss. I am having the same issue and cannot figure out the solution.

|||

I'm curious also, having the same issue

John R. wrote:

Just curious, what component did you miss. I am having the same issue and cannot figure out the solution.

|||

Robert,

Please be more specific. What are you trying to do and what error message are you getting?

If your error messages are similar to above it is probably because you are trying to do implicit conversions between unicode and non-unicode data. You can do explicit conversions using the Data Conversion or Derived Column components.

-Jamie

|||

Jamie,

I'm using SQL Server 2005 Developer Edition.

I'm using the 'Tasks-Export Data..' context menu to export all tables from my SQL Server Database to an empty Access 2003 database.

The operation fails on step 'Validating'. When I click messages the following message is displayed (among others):

Error 0xc02020f6: Data Flow Task: Column "Foutcode" cannot convert between unicode and non-unicode string data types.
(SQL Server Import and Export Wizard)

Can you point me to the right direction for finding the Data Conversion or Derived Column components? They do not seem to be part of the 'Tasks-Export Data' wizard?

__

Robert

|||

Those components are SSIS component that are available in the the Business Intelligence Development Studio (BIDS). You can use them in packages that you build yourself rather than with the wizards in SSMS (which I can't claim to know much about to be honest).

You will find that SSIS is a bit stricter than DTS was by not allowing implicit data conversions that DTS previously WOULD have allowed. This is a best practise initiative though one side affect is that it *could* cause the problems you are seeing now.

Open the package that the wizard creates for you and see if there are any implicit conversions going on.

-Jamie

|||

I would understand if I would get this error while importing lets say from Oracle to SQL Server.

But man, this is Microsoft Access and Microsoft SQL Server, don't you guys talk to each other?

In the worst case you could make an option in a wizard to allow implicit conversions. Now I have to spend many hours to fix wizard created package, while in previous DTS I could have done this in a minute.

The other "feature" of new SSIS is that "Drop and recreate table" feature does not work anymore.

Next time I will remove some of the features in my program and call it a brand new completely redesigned program. "One of the feature of my program is that it does not have this feature anymore" how do you like that? :)

|||

This is insane, you can't use SSIS anymore, it complains about everything. Where DTS would not give a single error, SSIS spits out 200 and you have to deal with even when you know implicit conversion is ok and would not damage your data.

SSIS stops the whole package on about every single warning. You should guys implement "skip error" option. It is taking sooooo much time, and for nothing.

|||

Hi jamie,

I'm having the same problem. I'm trying to import from an XLS file to an Oracle database (this error happens even if I go from XLS to SQL as well).

Here is what I've got:

Excel File Source

Data Conversion Object

OLE DB Destination

When I got the error at first- I added the "Data Conversion" Object - within that object the defaut setting was "Unicode string [DT_WSTR]" - I quickly changed this option to "String [DT_STR]" and still get the same error.

Oracle data type on the destination is Varchar2

Please Help.

steve

|||

I am also experiancing these stupid messages! (ie: Error 0xc02020f6: Data Flow Task: Column cannot convert between unicode and non-unicode string data types.)

It's insane that the "new" DTS (called SQL Server Import and Export Wizard) actually works worse than the old DTS. You'd think the guys making it would have tried harder to make it a better product (instead they made it worse).

I actually saved all the EXE and DLL files that the old DTS (from SS2000) used. I probably use the old DTS 90% of the time - because it works. Occasionaly i'll try the new DTS again, it'll throw these stupid exceptions, i'll close it, open the old DTS, and it will load the data without a problem.

If you're getting these errors, i suggest switching back to the old DTS - you'll find everything works.

|||

MrGTI wrote:

I am also experiancing these stupid messages! (ie: Error 0xc02020f6: Data Flow Task: Column cannot convert between unicode and non-unicode string data types.)

It's insane that the "new" DTS (called SQL Server Import and Export Wizard) actually works worse than the old DTS. You'd think the guys making it would have tried harder to make it a better product (instead they made it worse).

I actually saved all the EXE and DLL files that the old DTS (from SS2000) used. I probably use the old DTS 90% of the time - because it works. Occasionaly i'll try the new DTS again, it'll throw these stupid exceptions, i'll close it, open the old DTS, and it will load the data without a problem.

If you're getting these errors, i suggest switching back to the old DTS - you'll find everything works.

This is a bad piece of advice -- and it's uninformed at best. Sorry.

DTS has been replaced by SSIS, not the SQL Server Import and Export Wizard. And it works FAR, FAR, FAR better than DTS when you understand how it operates and why it operates the way it does.

Do you want to know how to fix the unicode/non-unicode issue, or did you just come here to rant?|||

I disagree. The old DTS was replaced by the SQL Server Import and Export Wizard. Take a look:

They're both designed to be simple ways of loading or exporting data. SSIS is (from what i understand), the more complex way of creating packages in Management Studio to automate the import/export. You're expected to run them many times. The DTS i know is used for simple 1 time import/export tasks.

And as for ranting - people searching the web for a solution to the same problem will want a solution. This thread never gave any solution. My response gives people a solution - switch to the old DTS, and you'll have your data loaded without wasting any more time on the problem.

|||

MrGTI wrote:

I disagree. The old DTS was replaced by the SQL Server Import and Export Wizard. Take a look:

I see you're point, but still, DTS was replaced by SSIS. Each has their own Import/Export wizard.

And if you're going against Excel files, then this is a driver issue and it has nothing to do with SSIS.|||

I had the data in tab delimited text files. Plain text in a TXT file, into an exisiting table in SS2005. It doesn't get any more basic than that.

So when when something as simple as that doesn't work, you can understand why switching back to the old DTS (from SS200) makes sense - because it still works, without an error.

Error between string data types

I reinstalled SQL Server, setup new connetions in my existing project and then pointed the existing controls in my SSIS packege to my new OLE DB Connection manager.

When I run my package, now I get:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "TransactionDate" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "TransactionTime" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "AccountNumber" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "TransactionCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "FieldCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "NewValue" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "InternalExternalFlag" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "RecovererCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "AS_400_UserID" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "ProductLoanTypeCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "NotUsed" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [DTS.Pipeline]: "component "OLE DB Source 1" (73)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task 1 [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task 1: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

and this error. I did create a new connection but also made sure all my components were using it so not sure why I still get this:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Package: The connection "{35FE7FF5-A1F5-4016-8C11-0B88A90AE3F7}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Error at Package: The connection "{35FE7FF5-A1F5-4016-8C11-0B88A90AE3F7}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Error at Execute SQL Task [Execute SQL Task]: Connection manager "{35FE7FF5-A1F5-4016-8C11-0B88A90AE3F7}" does not exist.

Error at Execute SQL Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

nevermind, missed a control.|||

Just curious, what component did you miss. I am having the same issue and cannot figure out the solution.

|||

I'm curious also, having the same issue

John R. wrote:

Just curious, what component did you miss. I am having the same issue and cannot figure out the solution.

|||

Robert,

Please be more specific. What are you trying to do and what error message are you getting?

If your error messages are similar to above it is probably because you are trying to do implicit conversions between unicode and non-unicode data. You can do explicit conversions using the Data Conversion or Derived Column components.

-Jamie

|||

Jamie,

I'm using SQL Server 2005 Developer Edition.

I'm using the 'Tasks-Export Data..' context menu to export all tables from my SQL Server Database to an empty Access 2003 database.

The operation fails on step 'Validating'. When I click messages the following message is displayed (among others):

Error 0xc02020f6: Data Flow Task: Column "Foutcode" cannot convert between unicode and non-unicode string data types.
(SQL Server Import and Export Wizard)

Can you point me to the right direction for finding the Data Conversion or Derived Column components? They do not seem to be part of the 'Tasks-Export Data' wizard?

__

Robert

|||

Those components are SSIS component that are available in the the Business Intelligence Development Studio (BIDS). You can use them in packages that you build yourself rather than with the wizards in SSMS (which I can't claim to know much about to be honest).

You will find that SSIS is a bit stricter than DTS was by not allowing implicit data conversions that DTS previously WOULD have allowed. This is a best practise initiative though one side affect is that it *could* cause the problems you are seeing now.

Open the package that the wizard creates for you and see if there are any implicit conversions going on.

-Jamie

|||

I would understand if I would get this error while importing lets say from Oracle to SQL Server.

But man, this is Microsoft Access and Microsoft SQL Server, don't you guys talk to each other?

In the worst case you could make an option in a wizard to allow implicit conversions. Now I have to spend many hours to fix wizard created package, while in previous DTS I could have done this in a minute.

The other "feature" of new SSIS is that "Drop and recreate table" feature does not work anymore.

Next time I will remove some of the features in my program and call it a brand new completely redesigned program. "One of the feature of my program is that it does not have this feature anymore" how do you like that? :)

|||

This is insane, you can't use SSIS anymore, it complains about everything. Where DTS would not give a single error, SSIS spits out 200 and you have to deal with even when you know implicit conversion is ok and would not damage your data.

SSIS stops the whole package on about every single warning. You should guys implement "skip error" option. It is taking sooooo much time, and for nothing.

|||

Hi jamie,

I'm having the same problem. I'm trying to import from an XLS file to an Oracle database (this error happens even if I go from XLS to SQL as well).

Here is what I've got:

Excel File Source

Data Conversion Object

OLE DB Destination

When I got the error at first- I added the "Data Conversion" Object - within that object the defaut setting was "Unicode string [DT_WSTR]" - I quickly changed this option to "String [DT_STR]" and still get the same error.

Oracle data type on the destination is Varchar2

Please Help.

steve

|||

I am also experiancing these stupid messages! (ie: Error 0xc02020f6: Data Flow Task: Column cannot convert between unicode and non-unicode string data types.)

It's insane that the "new" DTS (called SQL Server Import and Export Wizard) actually works worse than the old DTS. You'd think the guys making it would have tried harder to make it a better product (instead they made it worse).

I actually saved all the EXE and DLL files that the old DTS (from SS2000) used. I probably use the old DTS 90% of the time - because it works. Occasionaly i'll try the new DTS again, it'll throw these stupid exceptions, i'll close it, open the old DTS, and it will load the data without a problem.

If you're getting these errors, i suggest switching back to the old DTS - you'll find everything works.

|||

MrGTI wrote:

I am also experiancing these stupid messages! (ie: Error 0xc02020f6: Data Flow Task: Column cannot convert between unicode and non-unicode string data types.)

It's insane that the "new" DTS (called SQL Server Import and Export Wizard) actually works worse than the old DTS. You'd think the guys making it would have tried harder to make it a better product (instead they made it worse).

I actually saved all the EXE and DLL files that the old DTS (from SS2000) used. I probably use the old DTS 90% of the time - because it works. Occasionaly i'll try the new DTS again, it'll throw these stupid exceptions, i'll close it, open the old DTS, and it will load the data without a problem.

If you're getting these errors, i suggest switching back to the old DTS - you'll find everything works.

This is a bad piece of advice -- and it's uninformed at best. Sorry.

DTS has been replaced by SSIS, not the SQL Server Import and Export Wizard. And it works FAR, FAR, FAR better than DTS when you understand how it operates and why it operates the way it does.

Do you want to know how to fix the unicode/non-unicode issue, or did you just come here to rant?|||

I disagree. The old DTS was replaced by the SQL Server Import and Export Wizard. Take a look:

They're both designed to be simple ways of loading or exporting data. SSIS is (from what i understand), the more complex way of creating packages in Management Studio to automate the import/export. You're expected to run them many times. The DTS i know is used for simple 1 time import/export tasks.

And as for ranting - people searching the web for a solution to the same problem will want a solution. This thread never gave any solution. My response gives people a solution - switch to the old DTS, and you'll have your data loaded without wasting any more time on the problem.

|||

MrGTI wrote:

I disagree. The old DTS was replaced by the SQL Server Import and Export Wizard. Take a look:

I see you're point, but still, DTS was replaced by SSIS. Each has their own Import/Export wizard.

And if you're going against Excel files, then this is a driver issue and it has nothing to do with SSIS.|||

I had the data in tab delimited text files. Plain text in a TXT file, into an exisiting table in SS2005. It doesn't get any more basic than that.

So when when something as simple as that doesn't work, you can understand why switching back to the old DTS (from SS200) makes sense - because it still works, without an error.

Error between string data types

I reinstalled SQL Server, setup new connetions in my existing project and then pointed the existing controls in my SSIS packege to my new OLE DB Connection manager.

When I run my package, now I get:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "TransactionDate" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "TransactionTime" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "AccountNumber" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "TransactionCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "FieldCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "NewValue" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "InternalExternalFlag" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "RecovererCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "AS_400_UserID" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "ProductLoanTypeCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "NotUsed" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [DTS.Pipeline]: "component "OLE DB Source 1" (73)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task 1 [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task 1: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

and this error. I did create a new connection but also made sure all my components were using it so not sure why I still get this:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Package: The connection "{35FE7FF5-A1F5-4016-8C11-0B88A90AE3F7}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Error at Package: The connection "{35FE7FF5-A1F5-4016-8C11-0B88A90AE3F7}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Error at Execute SQL Task [Execute SQL Task]: Connection manager "{35FE7FF5-A1F5-4016-8C11-0B88A90AE3F7}" does not exist.

Error at Execute SQL Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

nevermind, missed a control.|||

Just curious, what component did you miss. I am having the same issue and cannot figure out the solution.

|||

I'm curious also, having the same issue

John R. wrote:

Just curious, what component did you miss. I am having the same issue and cannot figure out the solution.

|||

Robert,

Please be more specific. What are you trying to do and what error message are you getting?

If your error messages are similar to above it is probably because you are trying to do implicit conversions between unicode and non-unicode data. You can do explicit conversions using the Data Conversion or Derived Column components.

-Jamie

|||

Jamie,

I'm using SQL Server 2005 Developer Edition.

I'm using the 'Tasks-Export Data..' context menu to export all tables from my SQL Server Database to an empty Access 2003 database.

The operation fails on step 'Validating'. When I click messages the following message is displayed (among others):

Error 0xc02020f6: Data Flow Task: Column "Foutcode" cannot convert between unicode and non-unicode string data types.
(SQL Server Import and Export Wizard)

Can you point me to the right direction for finding the Data Conversion or Derived Column components? They do not seem to be part of the 'Tasks-Export Data' wizard?

__

Robert

|||

Those components are SSIS component that are available in the the Business Intelligence Development Studio (BIDS). You can use them in packages that you build yourself rather than with the wizards in SSMS (which I can't claim to know much about to be honest).

You will find that SSIS is a bit stricter than DTS was by not allowing implicit data conversions that DTS previously WOULD have allowed. This is a best practise initiative though one side affect is that it *could* cause the problems you are seeing now.

Open the package that the wizard creates for you and see if there are any implicit conversions going on.

-Jamie

|||

I would understand if I would get this error while importing lets say from Oracle to SQL Server.

But man, this is Microsoft Access and Microsoft SQL Server, don't you guys talk to each other?

In the worst case you could make an option in a wizard to allow implicit conversions. Now I have to spend many hours to fix wizard created package, while in previous DTS I could have done this in a minute.

The other "feature" of new SSIS is that "Drop and recreate table" feature does not work anymore.

Next time I will remove some of the features in my program and call it a brand new completely redesigned program. "One of the feature of my program is that it does not have this feature anymore" how do you like that? :)

|||

This is insane, you can't use SSIS anymore, it complains about everything. Where DTS would not give a single error, SSIS spits out 200 and you have to deal with even when you know implicit conversion is ok and would not damage your data.

SSIS stops the whole package on about every single warning. You should guys implement "skip error" option. It is taking sooooo much time, and for nothing.

|||

Hi jamie,

I'm having the same problem. I'm trying to import from an XLS file to an Oracle database (this error happens even if I go from XLS to SQL as well).

Here is what I've got:

Excel File Source

Data Conversion Object

OLE DB Destination

When I got the error at first- I added the "Data Conversion" Object - within that object the defaut setting was "Unicode string [DT_WSTR]" - I quickly changed this option to "String [DT_STR]" and still get the same error.

Oracle data type on the destination is Varchar2

Please Help.

steve

|||

I am also experiancing these stupid messages! (ie: Error 0xc02020f6: Data Flow Task: Column cannot convert between unicode and non-unicode string data types.)

It's insane that the "new" DTS (called SQL Server Import and Export Wizard) actually works worse than the old DTS. You'd think the guys making it would have tried harder to make it a better product (instead they made it worse).

I actually saved all the EXE and DLL files that the old DTS (from SS2000) used. I probably use the old DTS 90% of the time - because it works. Occasionaly i'll try the new DTS again, it'll throw these stupid exceptions, i'll close it, open the old DTS, and it will load the data without a problem.

If you're getting these errors, i suggest switching back to the old DTS - you'll find everything works.

|||

MrGTI wrote:

I am also experiancing these stupid messages! (ie: Error 0xc02020f6: Data Flow Task: Column cannot convert between unicode and non-unicode string data types.)

It's insane that the "new" DTS (called SQL Server Import and Export Wizard) actually works worse than the old DTS. You'd think the guys making it would have tried harder to make it a better product (instead they made it worse).

I actually saved all the EXE and DLL files that the old DTS (from SS2000) used. I probably use the old DTS 90% of the time - because it works. Occasionaly i'll try the new DTS again, it'll throw these stupid exceptions, i'll close it, open the old DTS, and it will load the data without a problem.

If you're getting these errors, i suggest switching back to the old DTS - you'll find everything works.

This is a bad piece of advice -- and it's uninformed at best. Sorry.

DTS has been replaced by SSIS, not the SQL Server Import and Export Wizard. And it works FAR, FAR, FAR better than DTS when you understand how it operates and why it operates the way it does.

Do you want to know how to fix the unicode/non-unicode issue, or did you just come here to rant?|||

I disagree. The old DTS was replaced by the SQL Server Import and Export Wizard. Take a look:

They're both designed to be simple ways of loading or exporting data. SSIS is (from what i understand), the more complex way of creating packages in Management Studio to automate the import/export. You're expected to run them many times. The DTS i know is used for simple 1 time import/export tasks.

And as for ranting - people searching the web for a solution to the same problem will want a solution. This thread never gave any solution. My response gives people a solution - switch to the old DTS, and you'll have your data loaded without wasting any more time on the problem.

|||

MrGTI wrote:

I disagree. The old DTS was replaced by the SQL Server Import and Export Wizard. Take a look:

I see you're point, but still, DTS was replaced by SSIS. Each has their own Import/Export wizard.

And if you're going against Excel files, then this is a driver issue and it has nothing to do with SSIS.|||

I had the data in tab delimited text files. Plain text in a TXT file, into an exisiting table in SS2005. It doesn't get any more basic than that.

So when when something as simple as that doesn't work, you can understand why switching back to the old DTS (from SS200) makes sense - because it still works, without an error.

Error between string data types

I reinstalled SQL Server, setup new connetions in my existing project and then pointed the existing controls in my SSIS packege to my new OLE DB Connection manager.

When I run my package, now I get:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "TransactionDate" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "TransactionTime" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "AccountNumber" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "TransactionCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "FieldCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "NewValue" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "InternalExternalFlag" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "RecovererCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "AS_400_UserID" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "ProductLoanTypeCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "NotUsed" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [DTS.Pipeline]: "component "OLE DB Source 1" (73)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task 1 [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task 1: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

and this error. I did create a new connection but also made sure all my components were using it so not sure why I still get this:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Package: The connection "{35FE7FF5-A1F5-4016-8C11-0B88A90AE3F7}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Error at Package: The connection "{35FE7FF5-A1F5-4016-8C11-0B88A90AE3F7}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Error at Execute SQL Task [Execute SQL Task]: Connection manager "{35FE7FF5-A1F5-4016-8C11-0B88A90AE3F7}" does not exist.

Error at Execute SQL Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

nevermind, missed a control.|||

Just curious, what component did you miss. I am having the same issue and cannot figure out the solution.

|||

I'm curious also, having the same issue

John R. wrote:

Just curious, what component did you miss. I am having the same issue and cannot figure out the solution.

|||

Robert,

Please be more specific. What are you trying to do and what error message are you getting?

If your error messages are similar to above it is probably because you are trying to do implicit conversions between unicode and non-unicode data. You can do explicit conversions using the Data Conversion or Derived Column components.

-Jamie

|||

Jamie,

I'm using SQL Server 2005 Developer Edition.

I'm using the 'Tasks-Export Data..' context menu to export all tables from my SQL Server Database to an empty Access 2003 database.

The operation fails on step 'Validating'. When I click messages the following message is displayed (among others):

Error 0xc02020f6: Data Flow Task: Column "Foutcode" cannot convert between unicode and non-unicode string data types.
(SQL Server Import and Export Wizard)

Can you point me to the right direction for finding the Data Conversion or Derived Column components? They do not seem to be part of the 'Tasks-Export Data' wizard?

__

Robert

|||

Those components are SSIS component that are available in the the Business Intelligence Development Studio (BIDS). You can use them in packages that you build yourself rather than with the wizards in SSMS (which I can't claim to know much about to be honest).

You will find that SSIS is a bit stricter than DTS was by not allowing implicit data conversions that DTS previously WOULD have allowed. This is a best practise initiative though one side affect is that it *could* cause the problems you are seeing now.

Open the package that the wizard creates for you and see if there are any implicit conversions going on.

-Jamie

|||

I would understand if I would get this error while importing lets say from Oracle to SQL Server.

But man, this is Microsoft Access and Microsoft SQL Server, don't you guys talk to each other?

In the worst case you could make an option in a wizard to allow implicit conversions. Now I have to spend many hours to fix wizard created package, while in previous DTS I could have done this in a minute.

The other "feature" of new SSIS is that "Drop and recreate table" feature does not work anymore.

Next time I will remove some of the features in my program and call it a brand new completely redesigned program. "One of the feature of my program is that it does not have this feature anymore" how do you like that? :)

|||

This is insane, you can't use SSIS anymore, it complains about everything. Where DTS would not give a single error, SSIS spits out 200 and you have to deal with even when you know implicit conversion is ok and would not damage your data.

SSIS stops the whole package on about every single warning. You should guys implement "skip error" option. It is taking sooooo much time, and for nothing.

|||

Hi jamie,

I'm having the same problem. I'm trying to import from an XLS file to an Oracle database (this error happens even if I go from XLS to SQL as well).

Here is what I've got:

Excel File Source

Data Conversion Object

OLE DB Destination

When I got the error at first- I added the "Data Conversion" Object - within that object the defaut setting was "Unicode string [DT_WSTR]" - I quickly changed this option to "String [DT_STR]" and still get the same error.

Oracle data type on the destination is Varchar2

Please Help.

steve

|||

I am also experiancing these stupid messages! (ie: Error 0xc02020f6: Data Flow Task: Column cannot convert between unicode and non-unicode string data types.)

It's insane that the "new" DTS (called SQL Server Import and Export Wizard) actually works worse than the old DTS. You'd think the guys making it would have tried harder to make it a better product (instead they made it worse).

I actually saved all the EXE and DLL files that the old DTS (from SS2000) used. I probably use the old DTS 90% of the time - because it works. Occasionaly i'll try the new DTS again, it'll throw these stupid exceptions, i'll close it, open the old DTS, and it will load the data without a problem.

If you're getting these errors, i suggest switching back to the old DTS - you'll find everything works.

|||

MrGTI wrote:

I am also experiancing these stupid messages! (ie: Error 0xc02020f6: Data Flow Task: Column cannot convert between unicode and non-unicode string data types.)

It's insane that the "new" DTS (called SQL Server Import and Export Wizard) actually works worse than the old DTS. You'd think the guys making it would have tried harder to make it a better product (instead they made it worse).

I actually saved all the EXE and DLL files that the old DTS (from SS2000) used. I probably use the old DTS 90% of the time - because it works. Occasionaly i'll try the new DTS again, it'll throw these stupid exceptions, i'll close it, open the old DTS, and it will load the data without a problem.

If you're getting these errors, i suggest switching back to the old DTS - you'll find everything works.

This is a bad piece of advice -- and it's uninformed at best. Sorry.

DTS has been replaced by SSIS, not the SQL Server Import and Export Wizard. And it works FAR, FAR, FAR better than DTS when you understand how it operates and why it operates the way it does.

Do you want to know how to fix the unicode/non-unicode issue, or did you just come here to rant?|||

I disagree. The old DTS was replaced by the SQL Server Import and Export Wizard. Take a look:

They're both designed to be simple ways of loading or exporting data. SSIS is (from what i understand), the more complex way of creating packages in Management Studio to automate the import/export. You're expected to run them many times. The DTS i know is used for simple 1 time import/export tasks.

And as for ranting - people searching the web for a solution to the same problem will want a solution. This thread never gave any solution. My response gives people a solution - switch to the old DTS, and you'll have your data loaded without wasting any more time on the problem.

|||

MrGTI wrote:

I disagree. The old DTS was replaced by the SQL Server Import and Export Wizard. Take a look:

I see you're point, but still, DTS was replaced by SSIS. Each has their own Import/Export wizard.

And if you're going against Excel files, then this is a driver issue and it has nothing to do with SSIS.|||

I had the data in tab delimited text files. Plain text in a TXT file, into an exisiting table in SS2005. It doesn't get any more basic than that.

So when when something as simple as that doesn't work, you can understand why switching back to the old DTS (from SS200) makes sense - because it still works, without an error.

Error between string data types

I reinstalled SQL Server, setup new connetions in my existing project and then pointed the existing controls in my SSIS packege to my new OLE DB Connection manager.

When I run my package, now I get:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "TransactionDate" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "TransactionTime" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "AccountNumber" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "TransactionCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "FieldCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "NewValue" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "InternalExternalFlag" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "RecovererCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "AS_400_UserID" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "ProductLoanTypeCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [OLE DB Source 1 [73]]: Column "NotUsed" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task 1 [DTS.Pipeline]: "component "OLE DB Source 1" (73)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task 1 [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task 1: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

and this error. I did create a new connection but also made sure all my components were using it so not sure why I still get this:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Package: The connection "{35FE7FF5-A1F5-4016-8C11-0B88A90AE3F7}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Error at Package: The connection "{35FE7FF5-A1F5-4016-8C11-0B88A90AE3F7}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Error at Execute SQL Task [Execute SQL Task]: Connection manager "{35FE7FF5-A1F5-4016-8C11-0B88A90AE3F7}" does not exist.

Error at Execute SQL Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

nevermind, missed a control.|||

Just curious, what component did you miss. I am having the same issue and cannot figure out the solution.

|||

I'm curious also, having the same issue

John R. wrote:

Just curious, what component did you miss. I am having the same issue and cannot figure out the solution.

|||

Robert,

Please be more specific. What are you trying to do and what error message are you getting?

If your error messages are similar to above it is probably because you are trying to do implicit conversions between unicode and non-unicode data. You can do explicit conversions using the Data Conversion or Derived Column components.

-Jamie

|||

Jamie,

I'm using SQL Server 2005 Developer Edition.

I'm using the 'Tasks-Export Data..' context menu to export all tables from my SQL Server Database to an empty Access 2003 database.

The operation fails on step 'Validating'. When I click messages the following message is displayed (among others):

Error 0xc02020f6: Data Flow Task: Column "Foutcode" cannot convert between unicode and non-unicode string data types.
(SQL Server Import and Export Wizard)

Can you point me to the right direction for finding the Data Conversion or Derived Column components? They do not seem to be part of the 'Tasks-Export Data' wizard?

__

Robert

|||

Those components are SSIS component that are available in the the Business Intelligence Development Studio (BIDS). You can use them in packages that you build yourself rather than with the wizards in SSMS (which I can't claim to know much about to be honest).

You will find that SSIS is a bit stricter than DTS was by not allowing implicit data conversions that DTS previously WOULD have allowed. This is a best practise initiative though one side affect is that it *could* cause the problems you are seeing now.

Open the package that the wizard creates for you and see if there are any implicit conversions going on.

-Jamie

|||

I would understand if I would get this error while importing lets say from Oracle to SQL Server.

But man, this is Microsoft Access and Microsoft SQL Server, don't you guys talk to each other?

In the worst case you could make an option in a wizard to allow implicit conversions. Now I have to spend many hours to fix wizard created package, while in previous DTS I could have done this in a minute.

The other "feature" of new SSIS is that "Drop and recreate table" feature does not work anymore.

Next time I will remove some of the features in my program and call it a brand new completely redesigned program. "One of the feature of my program is that it does not have this feature anymore" how do you like that? :)

|||

This is insane, you can't use SSIS anymore, it complains about everything. Where DTS would not give a single error, SSIS spits out 200 and you have to deal with even when you know implicit conversion is ok and would not damage your data.

SSIS stops the whole package on about every single warning. You should guys implement "skip error" option. It is taking sooooo much time, and for nothing.

|||

Hi jamie,

I'm having the same problem. I'm trying to import from an XLS file to an Oracle database (this error happens even if I go from XLS to SQL as well).

Here is what I've got:

Excel File Source

Data Conversion Object

OLE DB Destination

When I got the error at first- I added the "Data Conversion" Object - within that object the defaut setting was "Unicode string [DT_WSTR]" - I quickly changed this option to "String [DT_STR]" and still get the same error.

Oracle data type on the destination is Varchar2

Please Help.

steve

|||

I am also experiancing these stupid messages! (ie: Error 0xc02020f6: Data Flow Task: Column cannot convert between unicode and non-unicode string data types.)

It's insane that the "new" DTS (called SQL Server Import and Export Wizard) actually works worse than the old DTS. You'd think the guys making it would have tried harder to make it a better product (instead they made it worse).

I actually saved all the EXE and DLL files that the old DTS (from SS2000) used. I probably use the old DTS 90% of the time - because it works. Occasionaly i'll try the new DTS again, it'll throw these stupid exceptions, i'll close it, open the old DTS, and it will load the data without a problem.

If you're getting these errors, i suggest switching back to the old DTS - you'll find everything works.

|||

MrGTI wrote:

I am also experiancing these stupid messages! (ie: Error 0xc02020f6: Data Flow Task: Column cannot convert between unicode and non-unicode string data types.)

It's insane that the "new" DTS (called SQL Server Import and Export Wizard) actually works worse than the old DTS. You'd think the guys making it would have tried harder to make it a better product (instead they made it worse).

I actually saved all the EXE and DLL files that the old DTS (from SS2000) used. I probably use the old DTS 90% of the time - because it works. Occasionaly i'll try the new DTS again, it'll throw these stupid exceptions, i'll close it, open the old DTS, and it will load the data without a problem.

If you're getting these errors, i suggest switching back to the old DTS - you'll find everything works.

This is a bad piece of advice -- and it's uninformed at best. Sorry.

DTS has been replaced by SSIS, not the SQL Server Import and Export Wizard. And it works FAR, FAR, FAR better than DTS when you understand how it operates and why it operates the way it does.

Do you want to know how to fix the unicode/non-unicode issue, or did you just come here to rant?|||

I disagree. The old DTS was replaced by the SQL Server Import and Export Wizard. Take a look:

They're both designed to be simple ways of loading or exporting data. SSIS is (from what i understand), the more complex way of creating packages in Management Studio to automate the import/export. You're expected to run them many times. The DTS i know is used for simple 1 time import/export tasks.

And as for ranting - people searching the web for a solution to the same problem will want a solution. This thread never gave any solution. My response gives people a solution - switch to the old DTS, and you'll have your data loaded without wasting any more time on the problem.

|||

MrGTI wrote:

I disagree. The old DTS was replaced by the SQL Server Import and Export Wizard. Take a look:

I see you're point, but still, DTS was replaced by SSIS. Each has their own Import/Export wizard.

And if you're going against Excel files, then this is a driver issue and it has nothing to do with SSIS.|||

I had the data in tab delimited text files. Plain text in a TXT file, into an exisiting table in SS2005. It doesn't get any more basic than that.

So when when something as simple as that doesn't work, you can understand why switching back to the old DTS (from SS200) makes sense - because it still works, without an error.