CODEWITHSUNDEEP
sharepoint-2013ssis
Mittal Patel
Mittal Patel

Reputation: 848

SSIS Package : While Extracting Sharepoint Lookup column, getting error 'Cannnot convert between unicode and non-unicode string data types'

I am working on one project and there is need to extract Sharepoint list data and import them to SQL Server table. I have few lookup columns in the list.

Steps in my Data Flow :

enter image description here

  1. Sharepoint List Source
  2. Derived Column

its formula : SUBSTRING([BusinessUnit],FINDSTRING([BusinessUnit],"#",1)+1,LEN([BusinessUnit])-FINDSTRING([BusinessUnit],"#",1))

  1. Data Conversion
  2. OLE DB Destination

But I am still getting the error of not converting between unicode and non-unicode string data types.

I am not sure what I am missing here.

In Data Conversion, what should be the Data Type for the Look up column?

Please suggest here.

Thank you, Mittal.

Upvotes: 0

Views: 1136

Answers (1)

Doug_Ivison
Doug_Ivison

Reputation: 650

Re' your question: "...what should be the Data Type...?" You just want to always ensure that the data types agree, on each "arrow", and that the data types agree with their objects.

For example, if your Sharepoint input source is a text file, you can check whether its an ASCII file or Unicode file using notepad: open that file in Notepad, do a "save as", and look next to the "Save" button, at the Encoding setting. Is the Encoding ANSI (which is ASCII, and would be DT_STR in SSIS), or is the Encoding Unicode, Unicode big endian, or UTF-8 (all of which are Unicode, or DT_WSTR in SSIS)?

And for example, if your OLE DB output target is a SQL Server table, then you can check whether all string columns are VARCHAR (ASCII, or DT_STR) or NVARCHAR (Unicode, or DT_WSTR).

If you have design control over the input source's encoding, or design control over the output target's columns, then the simplest option might be to make them all match: for example, if your input is a text file that will always be Unicode, then find any columns in the SQL Server table with type VARCHAR, and change them to NVARCHAR. Then, you would recreate your SSIS Data Flow task, with all string column types as DT_WSTR.

However, if you don't have design control, and any column's starting type (in Sharepoint), is different from that same column's final type (in SQL Server), then I'd suggest doing that type change in the Data Conversion transformation. For example, a column's type can be DT_STR (ASCII) going into the Data Conversion transformation, but DT_WSTR (Unicode) coming out of the Data Conversion transformation.

Do any type changes in the Data Flow within a "box", not within an "arrow".

((As a side-note, remember that, as you make changes to columns and their types, you have to double-click each object after the changed box, INCLUDING EACH ARROW, for any metadata changes to be recognized. Consider my answer to this question: Unable to change an SSIS Excel Destination Column Data Type.))

For a list of all data types in SSIS, see this Books Online (BOL) reference:
Integration Services Data Types
The top of that BOL page may show SQL Server 2014, and you probably want to click "Other Versions", to change it to your release of SQL Server.

Upvotes: 1

Related Questions