Exexute SQL Task, OLEDB , stored procedures parameters

Question

Exexute SQL Task, OLEDB , stored procedures parameters

Adam Sandler

SSCrazy

Points: 2697
More actions
April 20, 2017 at 8:16 am

#324495

I have mapped 2 parameters - named 0 , 1 within the task
Then I have tried EXEC spName ? , ?
I have tried it with EXEC spName @paramname = ? , @paramname2 = ?
I have tried it with teh mapped parameters named matching the TSQL param names.
Still getting errors.
I have read all the MS stuff - still cant find what I should be doing. Any help mist apprecitated?

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

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

JustMarie SSCertifiable Points: 7771 More actions · Answer 1

First - What errors are you getting? We can't help without knowing those.
Second - Are you connecting to the correct database?
Third - Does the stored procedure run outside of SSIS?
Fourth - Are you running this under an account with the correct permissions?

Almost everything starts with answers to that first question - what errors are you getting?

Adam Sandler SSCrazy Points: 2697 More actions · Answer 2

Thanks
1)[Execute SQL Task] Error: Executing the query "EXEC ProcTPSUpdate @intJob = ? , @varCharUserName ..." failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
2)Yes
3)yes
4)Its runs ok if ihard code teh parameter values in SSIS rather than using teh mapped parameters.

So I think its something to do with my names/mappings.

Adam Sandler SSCrazy Points: 2697 More actions · Answer 3

Or
[Execute SQL Task] Error: Executing the query "EXEC ProcTPSUpdate ? , ?" failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

Please post a screen shot of the screen where you have configured the parameter mappings.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Adam Sandler SSCrazy Points: 2697 More actions · Answer 5

Thanks Phil - it could be to do with datatype or size even?

Adam Sandler SSCrazy Points: 2697 More actions · Answer 6

I can get this to work with an ODBC connection. Is it possible to use an ODBC connection DSN less. I cant get my data to load to my table using ODBC howver! Swings and roundabouts!

Then theres OLE depreciation to think of? Any advice greatly received.
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/92390f70-e17d-4f9f-9965-97746f3f7dc8/ssis-ole-db-deprecation-what-to-do-about-it?forum=sqlintegrationservices

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Have you tried specifying a parameter size for Parameter 1? Also, what system variable are you passing to that parameter?

John

Adam Sandler SSCrazy Points: 2697 More actions · Answer 8

John Mitchell-245523 - Friday, April 21, 2017 4:01 AM
Have you tried specifying a parameter size for Parameter 1? Also, what system variable are you passing to that parameter?
John

Thanks John - I seem to have got round it by building teh SQL Source as an expression so

"EXEC ProcTPSUPDATE " + (DT_WSTR, 10) @[User::JobID] + ",'" + RIGHT( @[System::CreatorName] , LEN( @[System::CreatorName] ) - FINDSTRING( @[System::CreatorName] , "\\", 1 ) ) + "'"

hopefully it now works. I'd still like to know how to map the parameters. the fisrt param is bigint in SQL and int64 as variable in SSIS and I was trying long as datatype in the parameter mapping? Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

I'm pleased that works for you - but you're right, it's much better to know how to use it properly. It's less typing, for a start! I think the issue might be the sizing of the second parameter (which is what I meant by Parameter 1). When you have a string data type, you need to specify a length.

John

Adam Sandler SSCrazy Points: 2697 More actions · Answer 10

John Mitchell-245523 - Friday, April 21, 2017 4:37 AM
I'm pleased that works for you - but you're right, it's much better to know how to use it properly. It's less typing, for a start! I think the issue might be the sizing of the second parameter (which is what I meant by Parameter 1). When you have a string data type, you need to specify a length.
John

Right - I seem to have solved what caused the error.

In the proc second paramater was varChar(30) - but was insering data to a field varChar(25) - worked fine when called in via sql. Works find when called like EXEC procTPSUpdate 12 , "Adam" from SSIS execute SQL Task.

Errors if called as EXEC procTPSUpdate ? , "Adam"

So even if the length of the string actually attempted to be passed was OK it errors.

Works OK if I change the TSQL in the proc to have varChar(25) - even when I use parmeter mapping with both parameters length -1?

Thats what seems to be happnening anyway?

If you can shed any light onto what may be happening - all teh better. Otherwise thanks for your help.

Thanks everyone..

JustMarie SSCertifiable Points: 7771 More actions · Answer 11

I noticed that you were getting Result Set errors. That leads me to believe that you've set the task to have a result set but haven't directed it to a variable. Can you post a screen print of the task to show that option? Or confirm what you have that set to and/or that you have a Result Set variable as needed?

Adam Sandler SSCrazy Points: 2697 More actions · Answer 12

JustMarie - Monday, April 24, 2017 8:02 AM
I noticed that you were getting Result Set errors. That leads me to believe that you've set the task to have a result set but haven't directed it to a variable. Can you post a screen print of the task to show that option? Or confirm what you have that set to and/or that you have a Result Set variable as needed?

Thanks Marie - i had results set to none. Solution seemed to be above - sp variable was length 30 but inserting data to length 25. All worked fine in SQL Itself, or indeed does not when called with data from SSIS exceeding 25 (its automatically is truncating it) . And with hard coded arguments from SSIS even with the size mismatch.

Just one of those things to look out for I suppose. Thanks for your help.