September 28, 2009 at 2:33 pm
Here's what I have:
I have an SSIS project that reads an XML file and uses two of the three fields in each row of the file to fill the parameters of a stored procedure that contains an SQL UPDATE.
However after about 45 seconds of each execution of the package the fails with the following error:
SSIS Error Code DTS_E_OLEDBERROR. An OLEDBERROR has occurred. Error code 0x80004005 Description: "Syntax error, permission violation, or other non-specific error. Source OLE DB Command DTS.Pipeline Description: "component" OLE DB Command" (228)" failed validation and returned validation status "VS_ISBROKEN". ...
From what I've gathered I have read that to fix the problem I have to "Use a no-op select statement to "declare" metadata to the pipeline."
Does anyone know what that means?
Does it have to do with using an UPDATE that doesn't return a record set?
September 29, 2009 at 9:33 am
What component are you using to call the stored procedure?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 30, 2009 at 8:06 am
An OLE DB Command component in the Package's Data Flow Panel.
It is inside that component in the SqlCommand property that I say "EXEC storedProcedure @rowCount = 0, @NewTitle = ?, @CurrentTitle = NULL, @ID = ?
where the ?'s are being filled by the XML Source component mappings.
October 1, 2009 at 4:10 pm
head_contusion (9/30/2009)
An OLE DB Command component in the Package's Data Flow Panel.It is inside that component in the SqlCommand property that I say "EXEC storedProcedure @rowCount = 0, @NewTitle = ?, @CurrentTitle = NULL, @ID = ?
where the ?'s are being filled by the XML Source component mappings.
I may be wrong... but shouldn't the call to the Stored Procedure be:
EXEC storedProcedure ?, ?, ?, ?
and then you fill the parameters with the variables (read from the XML source) in the appropriate tab
October 5, 2009 at 8:21 am
afantozzi,
thank you for the response. the reason for filling in two of the parameters ahead of time is that the XML file is only providing 2 parameters for the stored procedure. meanwhile the two that are being filled in are being given their default information any way.
we think something else is going on because we finally got the error message to tell us the user doesn't have permissions to access some part of the server (we don't know the message is somewhat generic).
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply