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. SSISstops the whole package onabout 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: Columncannot 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 sameproblem 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 getany more basic than that. So when when something as simple as that doesn't work, you can understand why switching back to the oldDTS (from SS200) makes sense - because it still works, without an error.

MrGTI wrote: 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. Yeah, but that isn't an option in SQL Server 2005. So no, it's not a workaround or a valid suggestion. If you're on SQL Server 2000, then you don't have SSIS, so it isn't an issue.Admittedly, there are still issues with SSIS, but the fact that it is more strict in its rules does not mean it is inferior to DTS -- quite the contrary.

During the testing of SS2005 (before upgrading production machines), i discovered that the new DTS wizard had a horrible time processing files that the old DTS did without a problem - so i saved all the EXE and DLL files that the old DTS used. So after i upgraded to SS2005, i still have a folder containing the old DTS files, thus allowing me to continue working until a new and improved version of DTS is available. If anyone asks, i'll be happy to zip up the folder and supply it to them. They can also get those files from an existing SS2000 machine. "there are still issues with SSIS" - Agreed. "the fact that it is more strict in its rules does not mean it is inferior to DTS" - Hmmmm. I dunno. It can't translate the text in the TXT file into nvarchar text for a table. I'd say that was a big issue. Especially when previous tools could handle this task.

MrGTI wrote: "the fact that it is more strict in its rules does not mean it is inferior to DTS" - Hmmmm. I dunno. It can't translate the text in the TXT file into nvarchar text for a table. I'd say that was a big issue. Especially when previous tools could handle this task.IT CAN! Open up the .dtsx file the Import/Export wizard creates and add a derived column transformation to cast the non-unicode field to unicode or vice/versa.SSIS requires that the user be involved more now than DTS ever did. That is, SSIS does not make any decisions on behalf of the user -- the user must explicitly choose the way the package is designed. Sorry SSIS isn't working out for you.

MrGTI wrote: "the fact that it is more strict in its rules does not mean it is inferior to DTS" - Hmmmm. I dunno. It can't translate the text in the TXT file into nvarchar text for a table. I'd say that was a big issue. Especially when previous tools could handle this task. What if your text file could come from multiple locales? Implicit conversion of text from non-unicode to unicode or vice versa only works if you assume that every file you ever deal with is done in the same locale. That may be fine for your purposes, but for those who have to deal with different locales, international users, etc. it leads to enormous headaches. DTS didn't truly handle this, it guessed - and made mistakes.

Hi Everyone, This item has stumped me in the past as well. You "do" need to put an explicite conversion object between the source and destination. When you add the data conversion object AND select the data type conversions for each columna new column will appear in your destination object... edit your destination object to use this new column. If I have time later today, I will write specific steps and post... Here is an example: OLE DB Source Object --> Data Conversion Object --> OLE DB Destination Object Convert the columns in the Data Conversion Object - For Example Column 1 states DT_WSTR map this to DT_STR. Once you've done this you will find, in your OLE DB destination object, "Copy of Column 1"under the source column/drop downsas well as the original "Column 1". Use the newly created "Copy of Column 1" to map from your source to your destination and that will get rid of the error... Hope this helps - if not, please respond and share your knowledge. Best Regards, Steve Collins

Phil Brammer wrote: IT CAN! Open up the .dtsx file the Import/Export wizard creates and add a derived column transformation to cast the non-unicode field to unicode or vice/versa.SSIS requires that the user be involved more now than DTS ever did. That is, SSIS does not make any decisions on behalf of the user -- the user must explicitly choose the way the package is designed. Sorry SSIS isn't working out for you. The new SS2005 DTS wizard doesn't create a .dtsx unless you ask it to. It's normal operation is to just import/export data inas few as 4 steps. I can make it save the steps into a .dtsx file, and i have tried this on occasion. However the .dtsx file it creates is basically read only (maybe you're opening it in something special, but i'm opening it by double clicking it).

jwelch wrote: What if your text file could come from multiple locales? Implicit conversion of text from non-unicode to unicode or vice versa only works if you assume that every file you ever deal with is done in the same locale. That may be fine for your purposes, but for those who have to deal with different locales, international users, etc. it leads to enormous headaches. DTS didn't truly handle this, it guessed - and made mistakes. I'm sure that handling different locales is a concern. But my TXT filesare not from a different local. It's default "guess" as to what local it was should have been to the current one, which it obviously didn't do. Does it seem odd that it didn't guess the current one?

The point is that they took out guessing altogether,because it leads to unpredictable results. Having unpredictable results is a bad thing for an ETL tool. Given the fact that DTS works for your needs, you should probably stick with it. But not everyone has the same needs as you, and for a lot of scenarios, SSIS is a vastly improved tool over DTS.

Perform the Reverse. Import the sql server 2005 tables and data from Access database. There were no issues using this method. Access - Get External Database and connect to the sql server 2005 using the ODBC connection.

I encountered the same issue with my Import process , i.e importing from access to SQL Server 2005, and here is the quick solution that I followed. I checked my access database tables in the design view and found that the data types of those columns that throw error in the import process are shown as "memo". I changed the type of those columns in access table to "Text" from "Memo" and saved.Then imported the access tables and it worked fine.. So I recommend trying this quick solution before attempting to go through other tools and tutorials.Cheers!John

Hi Guys, I m also suferring with same stupid Error !. could nt trace out. IExported an excel sheet from SQL 2005 table using Export Import wizard Sucessfully. Later I tried to import same excel sheet to SQL 2005 of same server but diferrent data base. Even then I m getting this kind of error. Plz dont suggest to export the data fro table to table.. My actual requirementis toupload excel sheet in SQL 2005 table. What i wonder is SQL could notimport theexcel sheet which was exported by SQL itself.. The error which i got is : Error 0xc02020f6: Data Flow Task: Column "loc_id" cannot convert between unicode and non-unicode string data types. Now I m not aware of using DTS. Only process I use to do is Export / Import in SQL 2000. Now because of this bad feature at this point I could not change my data base into SQL 2000 from 2005. could any one help me ASAP in this regard. Thanks in advance to any one who gives me a solution. Ganesh,

It's not just Access, I had the same problem (error message) when I tried to export to Excel.

I had some luck opening the excel file in excel and saving then importing it again.noobie

may be changing the sqldatatype makes the difference, try using nvarchar data type rather than varchar. It may sound strange but i did that and it worked

may the sqldatatype is different, try using nvarchar data type rather than varchar. It may sound strange but i did that and it workschitra