Dear Group:
I have an SSIS package that is failing with the following message: Data Flow Task 1: Error Column "" cannot convert between unicode and non-unicode string data types.
We have a SQL Job setup that has two steps. The first step creates a table and inputs the data. The second step is an SSIS package that takes the data in this table and creates an Excel file to put on a network share.
The error is because some of these columns are of type NVARCHAR() instead of VARCHAR(). So in the first step, I create the table as VARCHAR() and use a CONVERT(VARCHAR(20), nvarchar_column) AS Column1. This fixes the Unicode issue, but now my SSIS is giving me an error about "Failed Validation and returned validation status VS_NEEDSNEWMETATDATA and a couple warnings about "The Column "" Needs to be added to the external columns."
The big problem is that I cannot view the SSIS package. It was created in SSMS and stored in the Integration Services Catalog / SSISDB, so there is no *.dtsx file I can view inside Visual Studio before I got on this team, so I am not sure what the actual SSIS file does, other than creates an Excel file and stores on a network share.
I was tasked with correct this error and getting the job to work.
I am hoping I can get some help, as I have tried most things I could think of (changing the staging table to use VARCHAR fields and converting the NVARCHAR columns in the select statement. If I could fix the original Unicode vs. Non-Unicode, that woudl be best, but if I need to do the CONVERTs, how do I get past this VS_NEEDSNEWMETATDATA issue / external columns issue?
In Visual Studio - create a new Integration Services Project using the template 'Integration Services Import Project Wizard'. This will prompt you for the Integration Services Catalog and allow you to pull down the project into Visual Studio.
Once you have that - you can correct the metadata as needed and redeploy the project to the catalog.
Note: Excel expects unicode data - which will most likely require adding a data conversion transformation to convert all columns coming from SQL Server to unicode. Or - you might want to change all columns in that table to nvarchar.
Also - make sure you set delay validation to true on the Excel connection manager. Most likely the project is using a template file - and copying that file as to destination file, which will cause the connection manage to 'fail' validation.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply