question

iainrobertson avatar image
iainrobertson asked

VS_ISBROKEN thrown by SSIS ODBC DataReader

Hi all, I have a strange problem. I have a SSIS package that uses a combination of system DSNs and config values stored in a database table to connect to an ODBC source using a DataReader source component. The package is stored within the SQL Server package store and is executed via a SQL Agent job. The package runs successfully in my Development and Test environments. However, on transfer to the production environment it throws VS_ISBROKEN on attempting to extract data from the source: "component "[Component Name]" (2363)" failed validation and returned validation status "VS_ISBROKEN". This error is described on MSDN as follows: The VS_ISBROKEN value indicates that the component has an error that can be rectified by editing the component in the designer. The error is typically caused by a custom property or a required connection that is not specified or is set incorrectly. I've tried forcing recompilation of the package then redeploying but this hasn't fixed the problem. I know that the DSN and connection is valid, as this is validated by a script task as part of the package's initial setup phase. I have considered attempting to set ValidateExternalMetadata to false for the component, but I'm loath to do so, as this is just kicking the problem down the road. I'd rather understand what the actual issue is. But, I'm a bit confused as to where to look to be honest. To complicate matters, I have no access to the live environment and some spectacularly unhelpful DBAs to work with. Does anyone have any suggestions for things I might try / look at? Thanks, Iain
ssis error
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dave_Green avatar image Dave_Green ♦ commented ·
Are there any other errors or warnings in the package output? I ask as often SSIS hides the problem some distance from the actual failure message. You may only have a subset of the package output; you may need to ask the DBAs for the full output if you don't have it.
0 Likes 0 ·

1 Answer

·
iainrobertson avatar image
iainrobertson answered
Hi Dave, Apologies for the tardy reply, I got caught up in something else. I figured updating this thread might help someone else later, so here we go... Interestingly, the external metadata turned out to be bit of a red herring. On setting ValidateExternalMetadata = false and re-executing, the component threw a syntax error. The query executed against the ODBC source is dynamically generated by concatenating a query with a where clause generated from package parameters stored in a database table. One of these parameters wasn't set correctly and this resulted in a non-valid SQL statement. Regards, Iain EDIT: Just one more thing... The parameter value was only incorrect on the production server, hence the non-repeatability. Which was fun... :)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dave_Green avatar image Dave_Green ♦ commented ·
Thanks for coming back to us to let us know what solved your problem in the end :-)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.