SSIS 2008 - MAPPING A OBJECT (RESULTSET) TO VARIABLE INT32

Amour22015
Amour22015 used Ask the Experts™
on
Hi experts,

I keep getting this error:
SSIS package "ClaimPurge.dtsx" starting.
Error: 0xC002F210 at Purge Claims, Execute SQL Task: Executing the query "EXEC Purge_By_FileId ,?,?,?,?" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Purge Claims

This is a:
Result Set: Full result set
Connection: OLE DB
TblVal datatype Object

there is a SQLStatement like:
Select
Fileids
tblnms
from table

so i have a
tblVal with a datatype = object
so that I can pass to a "Foreach Loop Container"

The datatype in the table were I am getting the field is:
File_id int,

The datatype to the SP to be finally passed to is:
file_id int,

So I am passing a Object (datatype) to a In32(DT_I4 - int) datatype

Please help and thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
>> "EXEC Purge_By_FileId ,?,?,?,?"
I can see a redundant comma after the name of the proc, is this really in the error message or just a typo?
T correct call to the proc should be:
"EXEC Purge_By_FileId ?,?,?,?"

Author

Commented:
Ok,

I took out the extra "," but still got the same error.

EXEC Purge_By_FileId ?,?,?,?

Thanks
Can you please post the stored proc signature?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I have attached:

Stored Procedure
SSIS Package

Thanks
Purge-by-fileid.docx
SnapshotPassingVariables.docx
I think I've spotted the issue. The datatype of the USer::TBLNMS used in the "Execute SQL Task" inside the Loop Container is Long (i.e. int)  instead of Varchar(20).
Error.png

Author

Commented:
Looks good

Author

Commented:
Great thanks
You're welcome!  : )

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial