stroed procedure returning 100 rows giving error

Question

stroed procedure returning 100 rows giving error

guru12

Ten Centuries

Points: 1068
More actions
August 19, 2010 at 2:01 am

#144624

HI Everyone.
I have a problem ,i have one stored procedure and i am executing that procedure with tree input parameters in executesql task ,i executed same procedure in SSMS and returns 100 rows with 10 columns.My actual problem is i want to catch that rows and load into destination table using SSIS .Can any one tell me the solution.
Regards..
guru12

Viewing 8 posts - 1 through 7 (of 7 total)

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Use the stored procedure in an OLE DB Source and then connect your input with an OLE DB Destination component.

To use the input parameters, it is best that you store the stored procedure in a string variable and your input parameters in other variables.

Then dynamically create your stored procedure using an expression. Something in the form of:

"EXECUTE sp_myprocedure " + (DT_WSTR,10) @my_parameter1 + ", " + (DT_WSTR,10) @my_parameter2

Finally use the SQL Command From Variable in the Source.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

guru12 Ten Centuries Points: 1068 More actions · Answer 2

HI DA ZERO...

Can you explain briefly explain how to do this,i have taken one execute sqltask and execute the procedure like this

EXECUTE [GetAllChangesByDateTime] " + (DT_WSTR,10) @TableName + ", " + (DT_WSTR,10) @StartDateTime +"," + (DT_WSTR,10) @EndDateTime +"

and given three input variables in parameter mapping, and in result set i selected full result set and create one variable and given resultname =0.

i have use the oledb connection string.but still it is throwing error,can you help why it is not executing correctly.

Regards..
guru12

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

You should store the code I gave you in a variable (which is actually an expression who sets the value of said variable). Do not use it directly in the Execute SQL Task, unless you replace the parameters with question marks.

If you use the variable, select SQL Command from variable in the Execute SQL Task.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

guru12 Ten Centuries Points: 1068 More actions · Answer 4

Hi DA ......

I have created one Package variable with datatype string and given value like this

"EXECUTE [GetAllChangesByDateTime] " + (DT_WSTR,10) @User::TableName + ", " + @User::StartDateTime +"," + @User::EndDateTime +""

my other three three input variables are there in statement and when i use oledb source,sql command variable i set my package variable,when i click on preview it is throwing some errors,can you please check my expression.and help me,its very urgent..

Regards..
guru12

guru12 Ten Centuries Points: 1068 More actions · Answer 5

hi da zero..

i tried like this

EXECUTE [GetAllChangesByDateTime] "+ (DT_WSTR,30) @[User::TableName] + "," + (DT_WSTR,30)(DT_DBDATE)

@[User::StartDateTime] + "," + (DT_WSTR,30)(DT_DBDATE) @[User::EndDateTime] +"

it is giving the following errors..

Error converting data type nvarchar to datetime.

Regards..
guru12

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

Don't forget the " at the beginning of the code.

Is the conversion to DT_DBDATE necessary?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

guru12 Ten Centuries Points: 1068 More actions · Answer 7

HI da..

NO conversion is not necessary ,please modify my expression and repost the correct one.it will be help ful to me,its very urgent.

Regards..
guru12