I keep getting the error message below when attempting to import a text file (flat file) to an SQL Server Destination using SSIS. This database has recently been migrated from SQL Server 2000 (where I used a DTS Package that worked fine). I plan on doing further manipulation to the file after importing but have so far tried to keep one step at a time. I have tried using a smaller files and different tables but still get a similar response. I believe that the encoding of the text file is ANSI. If it is relevant the database collation is Latin1_General_CI_AS (the same as it was when 2000). Any help anyone can provide will be greatly appreciated!!!
TITLE: Package Validation Error
Package Validation Error
ADDITIONAL INFORMATION:
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 0" and "AccountNumber" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 1" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 2" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 3" and "Name" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 4" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 5" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 6" and "ExpiryDate" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 7" and "RateType" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 8" can't be inserted because the conversion between types DT_STR and DT_BOOL is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 9" and "FullName" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 10" and "Address" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 11" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 12" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 13" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 14" and "Occupancy" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 15" and "LoanPurpose" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 16" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 17" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 18" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 19" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 20" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 21" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 22" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 23" and "DocumentLocation" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 24" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 25" and "SecurityType" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 26" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 27" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 28" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 29" and "MortgageInsurancePolicyNumber" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 30" and "SecurityAddress" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 31" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 32" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 33" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 34" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 35" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 36" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 37" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 38" and "SecuritySuburb" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 39" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 40" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 41" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 42" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 43" and "MortgageNumber" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: Columns "Column 44" and "TitleParticulars" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 45" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 46" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 47" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 48" can't be inserted because the conversion between types DT_STR and DT_I4 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 49" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 50" can't be inserted because the conversion between types DT_STR and DT_R8 is not supported.
Error at Data Flow Task [SQL Server Destination [174]]: The column "Column 51" can't be inserted because the conversion between types DT_STR and DT_DBTIMESTAMP is not supported.
Error at Data Flow Task [DTS.Pipeline]: "component "SQL Server Destination" (174)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
BUTTONS:
OK
The errors are exactly as are stated. You are trying to do implicit conversions that do not work. e.g. In column1 the value is coming in as a String and you are trying to insert it into an integer column.
You will have to open up the package and use the Data Conversion component or the Derived Column component to do explicit conversions.
-Jamie
|||
Thanks for the reply Jamie, I have tried using both of these and still get a similar conversion error (as below on a similar table). I have tried converting to different datatypes but still get the same conversion error.
The same database had no problem using DTS on 2000.
I must missing something that is probably blatantly obvious, but do you have any other ideas?
TITLE: Package Validation Error
Package Validation Error
ADDITIONAL INFORMATION:
Error at Data Flow Task [SQL Server Destination [4395]]: The column "TransactionKey" can't be inserted because the conversion between types DT_NTEXT and DT_WSTR is not supported.
Error at Data Flow Task [SQL Server Destination [4395]]: The column "AccountNumber" can't be inserted because the conversion between types DT_NTEXT and DT_WSTR is not supported.
Error at Data Flow Task [SQL Server Destination [4395]]: Column "TransactionCode" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SQL Server Destination [4395]]: The column "Acmount" can't be inserted because the conversion between types DT_R4 and DT_CY is not supported.
Error at Data Flow Task [SQL Server Destination [4395]]: The column "TransactionDate" can't be inserted because the conversion between types DT_DATE and DT_DBTIMESTAMP is not supported.
Error at Data Flow Task [SQL Server Destination [4395]]: Column "Narration" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [DTS.Pipeline]: "component "SQL Server Destination" (4395)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
BUTTONS:
OK
In SQL Server 2000, DTS used to do implicit conversions in many circumstances. The decision was made to eliminate that risky guessing in Integration Services, and to compel all data type conversions, truncations, etc. to be made explicitly.
Note that if you use the Import and Export Wizard to create your "base" package, the wizard creates all these tedious conversion transformations for you.
-Doug
|||
I'm getting the '...can't convert unicode...' conversion errors for strings imported from a table in Access 97.
I see that the Import Wizard does it's job and will successfully import the data into a new table.
But if you then change the destination to a table that already exists (created by a db copy from SQL2k) then the wizard generated package fails with the unicode conversion error again.
I've changed the destination column properties to match those created in the new table by the wizard - and it still fails.
I suspect that I could get the package copy the import into my table by doing an INSERT and then drop the table that the wizard creates. But I'm sure there's a more direct way than this.
I suspect I'm overlooking something obvious - but it is proving difficult to manage data between Microsoft family applications.
Any help / links appreciated.
|||I
have the same error trying to import an Access database with a memo
field type. SQL Server 2005 SSIS can't convert the memo to a varchar
and gives the unicode error. Odd.|||
I have found the same problem connecting to a Lotus Notes database via the NotesSQL 3.02g ODBC driver.
Consider the following article on unicode.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_6voh.asp
When I changed the datatypes of my destination table from varchar to nvarchar, I no longer received the "cannot convert between unicode and non-unicode" error.
|||To be clear: String conversion errors can be resolved by changing char columns to nchar and varchar columns to nvarchar in SQL2005.|||To be really clear, conversion errors happen because we use types inconsistently. There are unfortunately some instances when the type is forced upon us, but it is easy enough to change types inside SSIS. Changing existing database structures to simplify your ETL is not a great justification, alebit if possible it may make life easier. So in summary changing SQL Server column types will work, but so will converting the data within the pipeline, see the Data Conversion transform or the Derived Column transform.
|||Well, I guess just another in a long line of folks hitting this trip wire. I'm trying to import an Excel file into an existing SQL table which as varchar columns, not nvchar. And, you're right, I'm not changing the table definition!
OK, so please forgive the basic nature of the question, but you say it is easy enough to change types in SSIS. So, you have to drag one of those transforms (Data Conversion or Derived Column) then onto your package?
I was just thinking that there ought to be a more native way to do this. But I guess this is it, then?
For example, I right-clicked by source data file and went to the Advanced Editor. There's a Input and Output Properties tab that shows Excel output in a tree view. When expanded, in turn, there are External and Output columns nodes.
I tried changing these to my desired data types, but it didn't seem to a) save my changes much less b) make the package work. So what's does one use the Input and Output Properties for?
Thanks for any information (or if you're up to adding an indepth article about it on your SQLIS.com website!). BOL does not seem to offer any meaningful information here.
|||External columns are what the source actually contains. Output columns are what SSIS gives you downstream. You cannot change the types of either because the external types are decided by the underlying Excel provider not you, and you cannot change the output columns because they should match the external columns. External vs output is a bit like which columns have you selected to bring through to SSIS out of those available. So changing types as part of your column choice does not make sense, it would only lead to problems and failures.
The lack of implicit conversion is annoying for most people, but you can workaround. If implicit conversions take place and they are wrong, then you are generally stuck. The strong typing of SSIS is better in the long run I firmly believe, although I do often think it would be nice the other way too. Think of the way strings can be interpreted as dates. How may times have you hot problems with MD vs DM (if you in the UK all the time!). VBScript was my favourite as that would convert dates both ways depending on what the value was, all within the same function! You had no control and now idea which way it would go. Hence my assertion that not having implicit conversions are better. On the other hand some more help could be given in the product, such as selectively allowing similar types to be converted, STR to WSTR and I2 to I4. There will still be issues no doubt for more international organisations with multiple extended character sets or those using different code pages.
Data Conversion or Derived Column is the way to go I'm afraid.
|||Again thanks for the update.So, I'm trying a simple test at this point. Starting with a brand new package, I set up an Excel source, then dragged a Derived Column transformation onto the Data Flow designer. I selected just two columns to test w/ by dragging them from the Columns pane down to the Expressions field, putting a TRIM() around 'em, entered a custom name for each in the Derived Column Name field, selecting <add as new column> and selecting string [DT_STR] in the Data Type field.
Then set up my SQL table as the destination and mapped the two derived columns to the corresponding columns in my table.
I'm still getting task validation errors.
"cannot convert between unicode and non-unicode string data types."
So, this makes no sense to me. In the derived transformation editor, I have the data types set to string; in my SQL table, the columns are varchar's. I'm just not sure what I else I should be doing...
Any suggestions would be welcome.|||
Although you have selected the type of the output column to be DT_STR you haven't told SSIS how to convert the value into a DT_STR so in essence all you have done is define another implicit conversion.
Explicit conversions are done using type casts. The following expression will convert a column of type DT_WSTR into a DT_STR:
(DT_STR, <length>, 1252) (<column_name>)
Hope that helps.
-Jamie
|||
I am experiencing a similar error using the Microsoft OLEDB provider for oracle as a source. All data types are defined as nvarchar2 in Oracle and exist in a sql server 2000 db typed as nvarchar. Since these are both unicode types, why am I getting the same error?
I did find that the .net oracle provider fixed the unicode/non unicode error however the size of the source field is 1.5 times in the destination. The package will run but I get truncation warnings throughout execution.
Any feedback on these is greatly appreciated!
Thanks,
Barbara
|||I am having the same problem with SQL Server as a source and destination. I am using a "DataReader Source" and specifying a SQL query. I then pass it through Merge Join and Conditional Split transforms. My destination is an OLE DB Destination. If I pick an existing destination table that has EXACTLY the same schema as the source table, the data flow fails with the same message everyone is getting above. Somewhere in the process the package is converting the source data from varchar to nvarchar. I can only get it to work if I have it create a new table in the OLE DB Destination, in which case it gives me nvarchar columns (identical schema as source except it changes the varchars to nvarchars).
Where in the package is it converting from varchar to nvarchar? It this a known issue? I can tell you with certainty that the source fields are not unicode.
No comments:
Post a Comment