Forum Discussion
Data Type Inconsistencies
I am working with a client who was receiving an error message in SSIS saying that a field was not Unicode and needed to be for the destination.
We went to SSMS and Object Explorer, and low and behold, all of the fields that were causing the error messages said varchar and not nvarchar, like she thought they were all configured from table creation. I wanted to show them how to find all fields that were varchar, so we wrote a query against the Information_Schema.Columns view. But, all of the columns in the table we were looking at came back as nvarchar. The next step was to have them right-click the table and click Design. This interface also showed every column as nvarchar.
I have never seen anything like this before. Obviously, SSMS Object Explorer and SSIS are both seeing the limited fields as varchar, but the view and Design window are not. Other than dropping the table and recreating it with a script, rather than the Design window, any other ideas on what could be happening and why?
3 Replies
- You need to open the SSIS package for edit, and refresh data source/destination.
- The problem is in SSMS. If I use the designer I see 1 data type for ColA and if I open object explorer, I see a different data type for ColB. When I query the system views, I see the same data type as in designer, but SSIS sees the same data types as object explorer. The data sources and destinations have been refreshed and have the same results. I'm wondering if there is an OLE DB vs ADO difference or something.
The table is created within an SSIS Execute SQL Task with a SELECT INTO. I'm going to replace it with a CREATE TABLE to hopefully get rid of the problem, but I'd still like to know what I am seeing and why.- I'll suggest to use Visual Studio and open the package from there. then make sure it runs within VS and adjust data types if needed
Regards
Javier
