January 14, 2016 at 5:13 am
I have an SSIS job that has been failing with the following error:
Message
Executed as user: xxxxxxxx. Microsoft (R) SQL Server Execute Package Utility Version 11.0.5058.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 12:05:01 AM Error: 2016-01-12 00:05:04.65 Code: 0xC02020F6 Source: Data Flow Task Source - Query [1] Description: Column "Legacy Incident #" cannot convert between unicode and non-unicode string data types. End Error Error: 2016-01-12 00:05:04.67 Code: 0xC004706B Source: Data Flow Task SSIS.Pipeline Description: "Source - Query" failed validation and returned validation status "VS_ISBROKEN". End Error Error: 2016-01-12 00:05:04.67 Code: 0xC004700C Source: Data Flow Task SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2016-01-12 00:05:04.67 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:05:01 AM Finished: 12:05:04 AM Elapsed: 3.172 seconds. The package execution failed. The step failed.
From looking at the above I am presuming the error is because of the data types between source and destination.
The package executes a stored procedure which currently returns one row of data - the field LegacyIncidentNumber within the sql database has the datatype of Nvarchar(50). (the stored proc returns this field as the column Legacy Incident # as mentioned in the error message above). Currently this field returns no data.
The SSIS package seems to be using the datatype [DT_STR] - Should this be [DTWSTR] which I believe is the unicode version - and would match the datatype from the sql server table of Nvarchar(50)
January 14, 2016 at 5:33 am
I would say yes. But as this is a job, presumably it was working before? Or has the proc definition changed recently?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 14, 2016 at 5:43 am
Its a good question. I have just asked the same question regarding the data type changing at any point and I am waiting for an answer. This is a new environment I have come into, so I don't know how long the job has been failing for. (the job history retention for msdb is another thing I need to look at, as it'c currently only holding about 5 days worth !). They tell me this extra field was added back in May - so I presume somebody would have flagged this if the job was failing before now (ie the customer that this report is produced for, although there has never been a dba here so who knows !!)
So you think my suggestion over the data type being DT_STR is correct and it should be DT_WSTR ?
Might it be easier to change the datatype in sql server from Nvarchar to varchar without changing the SSIS package and then see if it runs ?
January 14, 2016 at 5:50 am
PearlJammer1 (1/14/2016)
Its a good question. I have just asked the same question regarding the data type changing at any point and I am waiting for an answer. This is a new environment I have come into, so I don't know how long the job has been failing for. (the job history retention for msdb is another thing I need to look at, as it'c currently only holding about 5 days worth !). They tell me this extra field was added back in May - so I presume somebody would have flagged this if the job was failing before now (ie the customer that this report is produced for, although there has never been a dba here so who knows !!)So you think my suggestion over the data type being DT_STR is correct and it should be DT_WSTR ?
Might it be easier to change the datatype in sql server from Nvarchar to varchar without changing the SSIS package and then see if it runs ?
Agreed – with everything you say. In general, I'd advise against using NVarchar() unless you need it – a waste of space otherwise.
Someone probably decided that MSDB was growing too much and reduced the history retention period – I've seen that before.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 14, 2016 at 9:28 am
SSIS is .. picky about data types.
The solution to this one is either to cast it before it ever hits the data source or do add a Data Conversion transformation and cast it to DT-STR.
January 14, 2016 at 10:21 am
ok thanks everyone - They tell me the data type hasn't changed, so im going to try and get the data type from the sql server base table changed from Nvarchar to varchar - see if that fixes it..
January 21, 2016 at 7:41 am
Phil
We made a change in the stored procedure which the package call and it now does a cast to the non unicode datatype (varchar) and this package is using the DT_STR data type for that column.
However, I know get another error when the package runs:
Data Flow Task SSIS.Pipeline Description: "Source - Query" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
I believe this is because I have done the 'cast' in the sp and now the package needs something doing to it ?
The dtsx package is deployed to the file system on the server. There is no visual studio installed on the server. Do I need to somehow open the package up and re-validate things and if so can you tell me how i do this ? (it is installed on sql2012 server)
January 21, 2016 at 8:51 am
PearlJammer1 (1/21/2016)
PhilWe made a change in the stored procedure which the package call and it now does a cast to the non unicode datatype (varchar) and this package is using the DT_STR data type for that column.
However, I know get another error when the package runs:
Data Flow Task SSIS.Pipeline Description: "Source - Query" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
I believe this is because I have done the 'cast' in the sp and now the package needs something doing to it ?
The dtsx package is deployed to the file system on the server. There is no visual studio installed on the server. Do I need to somehow open the package up and re-validate things and if so can you tell me how i do this ? (it is installed on sql2012 server)
Easiest thing is to fix up the package in VS (locally/wherever you do your dev) and redeploy.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 21, 2016 at 9:41 am
I'm not really sure what I need to do to do that - shouldn't the package work now that I have converted the data type within the stored proc ? Or does it just need re-deploying (is that a case of just copying and pasting the dtsx files over to the production server ? - apologies for my lack of knowledge, SSIS is not my thing !!!!!!)
January 21, 2016 at 10:07 am
As I said - SSIS is very picky when it comes to data types. You've changed one and now the metadata is out of sync.
Your best bet is to open it in VS, update the metadata, then redeploy.
January 21, 2016 at 10:35 am
So I have altered the metadata to match the SSIS package datatype which is DT_STR. (ive altered the table column in sql server to type of varchar which should match this SSIS DATA type of DT_STR.
The error I get is:
Data Flow Task SSIS.Pipeline Description: "Source - Query" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
Do I still need to re-deploy the package just because I have made a change in sql server to match what the package is actually already configured to look for ( I don't understand the logic\thinking behind that ?).
If that is the case how do I deploy from my local machine where I have opened the package (with all sorts of validation errors ) to the server. I have never done this before so please excuse my ignorance on these matters.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply