Execute SQL Task issue with OLE DB and parameters.

Question

Execute SQL Task issue with OLE DB and parameters.

richard.noordam

SSCrazy

Points: 2691
More actions
April 3, 2009 at 3:24 pm

#202518

I am calling a stored procedure with 3 inputs. TableName, NewRecCount, CurrRecCount.
I have the connection type set to ole db, sqlsource type as Direct input, sql statement as exec usp_InserDataPullRecordCounts ?,?,? and I have the BypassPrepare set to True.
parameters
0 - TableName -varchar
1 - NewRecCount -numeric
2 - CurrRecCount -numeric
(and these values match in the db)
I know that I can run the execute statement with the values (gathered from a breakpoint) that I have and the statement compiles and writes the record. But within the Execute SQL Task it fails.
Ideas? This is just passing data to a USP to insert a record in a table.
I'm getting a vague error, says it could be the result set or a parameter. (no result set on an insert, and my parameters seem ok.... ) can anyone see my problem here?
R

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

Honestly, I don't like using the parameters option of Execute SQL (because of it parsing & execution issues). Instead, I store the SQL text in a variable and pass this variable as Input to the Execute SQL task.

--Ramesh

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Can you post the exact error?

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

richard.noordam SSCrazy Points: 2691 More actions · Answer 3

I did and pushed it to User::InsertVariable (string) and it came up as follows:

Exec usp_InsertDataPullRecordCounts 'Test_Score_D', 0, 174227

which looks correct to me, (and inserts just fine in a sql window), I still get the same error

"Unspecified error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

if I understand correctly the resultset should be set to none (for an insert) and the variables? there aren't any.

ResultSet : None

ConnectionType: Ole DB

Connection (already used connection string, so I know it works)

SQLSourceType: Variable

SourceVariable: User::InsertVariable

IsQueryStoredProcedure: False (greyed out)

BypassPrepare: False

ideas?:crazy:

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 4

When did you get that error? Is it on executing on the package or parsing the query? (I think later is the case here)

Try setting the BypassPrepare=True and check if it works.

--Ramesh

richard.noordam SSCrazy Points: 2691 More actions · Answer 5

Ramesh (4/6/2009)
When did you get that error? Is it on executing on the package or parsing the query? (I think later is the case here)
Try setting the BypassPrepare=True and check if it works.

(already tried) no dice 🙁 any other ideas?

R

richard.noordam SSCrazy Points: 2691 More actions · Answer 6

richard.noordam

SSCrazy

Points: 2691

April 6, 2009 at 11:02 am

#972743

error is on the Execute SQL task.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 7

Can give post the print screen of the error as an attachment?

--Ramesh

richard.noordam SSCrazy Points: 2691 More actions · Answer 8

I think I see something that may be of issue but I'm not sure why....

SSIS package "DataPullTest_Score_D.dtsx" starting.

Information: 0x4004300A at Data Flow Task - Pull Data from District, DTS.Pipeline: Validation phase is beginning.

Error: 0x0 at Insert Table Record Counts: The precision is invalid. <--- this??

Error: 0xC002F210 at Insert Table Record Counts, Execute SQL Task: Executing the query "Exec usp_InsertDataPullRecordCounts 'Test_Score_D', 0, 174227" failed with the following error: "Unspecified error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Insert Table Record Counts

Warning: 0x80019002 at DataPullTest_Score_D: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "DataPullTest_Score_D.dtsx" finished: Failure.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 9

Does these links help?

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/90eed185-751e-4f36-89b1-35b2224e5819/[/url]

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/c131a0a8-ed27-4dbb-9ecc-fbf624b057fb/[/url]

--Ramesh

richard.noordam SSCrazy Points: 2691 More actions · Answer 10

Thanks for the Links! Here's what I found. per a conversation in one of those links, someone changed their int32 type to long to make it work.... It worked for me to... now if I completely understood why..... has it to do with some casting issue? I mean the db field was numeric and the variables were int32.

clarification?

btw: THANK YOU SO MUCH for the link. I can now move forward 😎

R

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 11

You are always welcome. And I'm glad that I could help you.

Probably, SSIS' Execute SQL Task enforces the explicit conversion of data types for performance issues. Its fine if it does but why does it cannot clearly state the errors is the annoying thing.

--Ramesh