January 12, 2015 at 7:02 pm
Hi,
I am running a SSIS package for some combinations through SP by using the Execute SQL task , and I am storing the results in the system.object type variable . It is a FULL result set .
The package execute fine for lower level of combinations , but when I run for higher combinations i.e SP will output more records is what I mean then package fails with the below error .
Executing the query "EXEC dbo.professionalSP 'ramu..." failed with the following error: "Object was open.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
There is not much information around this error , Could you give your suggestion for this error and the way to fix the same . Not sure is it because of data issue or memory constraints .
FYI : If I execute the stored procedure combination in the sql server management studio it execute fine without issue only when I am executing through package it is failing .
Thanks in advance .
Regards,
RaghavSK
January 13, 2015 at 9:14 am
Does the stored procedure use a cursor? If so, are you deallocating it when you are through with it?
Does the package run an Execute SQL task and then a Foreach Sequence container? How are you running one instance of the Execute SQL task and then the next?
January 13, 2015 at 9:23 am
No , I am not using a cusror inside a stored procedure .
It is a quite big package , This execute sql task will execute the SP and write the resultset into the file . After this execute sql task I have a script task which will write the output into the file which is of CSV format .
Raghav
January 13, 2015 at 2:33 pm
Did you try with a dataflow executing the SP and output to csv file directly?
Based on the information given, it might be easier than writing a script task to create a csv file.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply