2

I am transferring a table from SQL Server to PDA Server. This is my data flow:

ADO NET Source ---> OLE DB Destination

All of the columns in my source table are varchars and so when I created my destination table, I made sure that they had the same data types. So both tables have varchar data types for all columns.

However, I am still getting the error: Column "col1" cannot convert between unicode and non-unicode string data types. The weird thing is that if I replace my OLE DB Destination with a ADO Net Destination, it seems to work however, I want to use OLE DB so that I can specifying things such as rows per batch.

Can anyone help?

Improve this question
2
  • I recently discovered a translation error that was caused either the metadata between the Input column and the destination column. In my instance, both destination and source had the right datatype but only after removing the column in advanced editor and reinserting it did I resolve the metadata issue. Pro-Tip: So you should start small and filter out the possible problems.
    – clifton_h
    Dec 1, 2018 at 14:47
  • If you got to the Destination Source Flow properties (double-click), you can see how you can select which table to use. Instead of using the list, change it to use a sql query. You can always try to translate the column (Cast as datatype) to see if they fixed it or if the error is meta related. See TutorialGateway - OLEDB Source in SSIS
    – clifton_h
    Dec 1, 2018 at 14:51

1 Answer 1

Reset to default
3

Double click the line between the source and destination and select the Metadata tab. That will list all of your columns and the data types SSIS has inferred.

I would compare that to your source and 100% ensure they align.

What can happen is that the first time you built your data flow, SSIS will set the metadata based on what the types are at that point. col1 is interpreted to be nvarchar so it'll set it as DT_WSTR of some length.

Time passes and you update the query and the source is now varchar, but still called col1. A varchar will always fit into an nvarchar so the shape of that column hasn't "changed" so SSIS doesn't need to redo it's internals.

If it is the case that SSIS is clinging to old metadata, my click path is to

  1. Copy the existing query
  2. Change my query to SELECT 1 as TotallyNewAndUniqueColumn
  3. SSIS will then report that all my metadata is out of whack and would I like to autofix it, and I click Yes
  4. Repaste the original query and lo and behold, the current metadata is captured.
Improve this answer
3
  • thanks for the response. I can see that all of the data types are set to DT_WSTR. However, when you tell me to change my query, I'm not sure which query you are talking about since I never explicitly wrote out a query for the task I'm trying to perform....Also, the source table was never changed. It would work if I replaced the Destination with ADO Net Destination instead of OLE DB Destination
    – tee
    Nov 28, 2018 at 16:40
  • The target component (OLE vs ADO) won't matter. The same logic for holds true if you are using a table source versus query source - pick a different source table and you'll force the metadata to be reset
    – billinkc
    Nov 28, 2018 at 16:58
  • I tried changing the source table but it didn't force the metadata to be reset. I also changed my source to OLE instead of ADO...so I'd have OLE DB source ---> OLE DB Destination and it seems like it works like that...I just don't know why it doesn't work with my original design.
    – tee
    Nov 28, 2018 at 17:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.