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.
