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 7:02 am
Without knowing a few more details, this one is hard to answer.
Can you post pics of all tabs on your EXECUTE SQL task?
Also, how many is "more records"? Have you exerimented to find the break point?
January 13, 2015 at 7:41 am
Hi,
Breakpoint i believe is 450,000 records . It execute fine upto 450000 records . The issue I faced is having a record of 650,000 records .
I am not able to attach the screenshot . The info is
ResultSet : Full result set
Connection type : OLE DB
SQL source type : Variable
SourceVariable : User::ReportStatement
ResultSet Variable is of object type . Storing as below .
0 user::UserResultset
Please let me know incase of any furthur information needed .
Regards,
Raghavendra
January 13, 2015 at 7:54 am
That is just the General tab, and not even all the information on it (timeout, codepage, bypassPrepare). What about the other three tabs, Parameter Mapping, Result Set, and Expressions?
Side Note / Unrelated Question: Why do you have your source set as a variable? If you leave it as Direct input, you can still list EXEC dbo.professionalSP in the SQL Statement line without a problem.
January 13, 2015 at 8:02 am
Brandie Tarvin (1/13/2015)
That is just the General tab, and not even all the information on it (timeout, codepage, bypassPrepare). What about the other three tabs, Parameter Mapping, Result Set, and Expressions?Side Note / Unrelated Question: Why do you have your source set as a variable? If you leave it as Direct input, you can still list EXEC dbo.professionalSP in the SQL Statement line without a problem.
To be clear, I believe you are trying to store too much data in the Object variable, but without knowing how many bytes each row is consuming, I couldn't tell you for sure. Still, I'd like to know the other information so I know whether or not I'm heading in the correct direction on this.
January 13, 2015 at 8:07 am
Stored procedure is passed to the variable . I have given it as Direct input and passed the stored procedure with the input parameters even that did not execute successfully .
timeout = 0
Codepage = 1252
ByPassPrepare = True .
Raghav
January 13, 2015 at 8:15 am
Brandie Tarvin (1/13/2015)
What about the other three tabs, Parameter Mapping, Result Set, and Expressions?
January 13, 2015 at 8:35 am
skrs (1/13/2015)
Stored procedure is passed to the variable . I have given it as Direct input and passed the stored procedure with the input parameters even that did not execute successfully .timeout = 0
Codepage = 1252
ByPassPrepare = True .
Raghav
Give a trying using the DFT and Recordset destination.
rxm119528
January 13, 2015 at 8:47 am
Parameter mapping is having one input variable .
VariableName = User::CurHeader
Direction = Input
Data type = Varchar
Parameter name = 0
ParameterSize = -1
ResultSet:
ResultName = 0
Variable Name = user::CurResultRS
Expression :
There is no data , it is empty .
Raghav
January 15, 2015 at 4:56 am
I am positive that you are trying to squeeze too much data into the variable, but I cannot find anything on the internet that indicates how much data an object type variable can hold.
Why do you need to use an object type variable?
Can you not just pull the data into a staging table or global temp table (hard to do in SSIS unless you remember to keep all your connections) on the DB and then manipulate it there?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply