Input, Output and Return Values in SSIS Execute Sql Task

193
52074
return values

Friends,

In this post we are gonna discuss about all the THREE Parameter directions INPUT,OUTPUT and RETURN VALUE in Execute Sql Task. I am pretty sure that most of the SSIS developers might have used this task many times but at least few of them still not clear about how to use the three directions of Parameter and this post is for that set of people.

I can explain the INPUT direction by using any Sql Statement but to explain all the three Stored Procedure is the best option to choose. So, in this post I am gonna use SP to demonstrate the same.

I have created a damn simple SP with one parameter to demonstrate INPUT parameter and you can see the same in the below given SC.

Here I created a Stored Procedure “test” with a  Input Parameter @PrdKey

Now let’s see how to call the same in the Execute Sql Task.

1)     Drag and drop an Execute Sql Task to control flow and double click or right click and select Edit to set the properties.

2)     Once the properties wizard opens then set the connection to the database and use the following query for Sql Statement.

Exec test ?

3)     The ? in the above query means, it is a parameter for which the value will be assigned at runtime using variable.

 

4)     Now go to the Parameter Mapping page to map the parameter (variable) which will provide value to the “?” provided in the query.

5)     Under variable name select the variable which provides value to the query parameter.

6)     Under direction we will have THREE options and select INPUT as we are inputting the value to the parameter.

7)     Under datatype select the appropriate datatype and in this case I have selected LONG as the parameter for the SP.

8)    Under parameter Name property set 0 (Zero) as we have only one parameter in our query.

The parameter name values will be as given below –

? –> 0

?,? –> 0,1

?,?,? –> 0,1,2

The parameter name will be interger values starting from ZERO and it will be mapped in such a way that first ? will be replaced with first parameter (parameter name 0) and second one with second parameter ( parameter name 1) ….

9)     Now select OK and Execute.

10)  Now I created a SP with all the three values i.e Input Parameter, Output Parameter and also RETURN Value as shown below.

11)   Here @ProductKey is input , @ProductName is Output and “16” is Return Value parameter created stored procedure. Remember that SP can return ONLY Integer values.

12)  Now the query for this SP will be like given below in Execute Sql Task –

Exec ? = Test_sp ? , ? OUTPUT  

13) Here first ? is Return Value parameter, second ‘?’ Is Input parameter and third ‘?’ with OUTPUT keyword is output parameter

14)   Now Open Parameter Mapping and create Variable and Map it to the ‘?’ as explained above Section. Remember that the parameters assigning should follow sequence and in our example first one should be RETURN VALUE Parameter then INPUT and finally OUTPUT.

INPUT – Used for Input variables. The value will be passed from calling environment.

OUTPUT – Used for Output variable and value will be returned to calling environment.

RETURN VALUE – The value which is returned by SP.

15)  Here I created three variable Count for returnValue ,MinValue for Input and ProductName for the output and order them as 0,1,2 as per there occurrence.

16)  Click Ok and now we can pass values to the variables in the Variable window and run the package.

Note – We can configure OUTPUR (Result set) to return Table/XML/Single Row which will be discussed in next post.

Thanks a lot to Swati Srivastava for providing most of the content and SC to draft this post.

Regards,
Roopesh Babu V

193 COMMENTS

  1. Lasix (Furosemide) helps people with edematous syndrome to reduce the amount of liquid in the body. It relieves general state of hypertensive patients because it has a hypotensive action. This is an irreplaceable diuretic which is available to everybody due to its safety, efficiency, and low cost.
    Special offer – buy lasix online Now and Get Discount for all Purchased! – No Prescription Required.
    Safe & Secure Payments. Fast & Free Delivery.
    Two Free Pills (Viagra or Cialis or Levitra) available With Every Order.

  2. I am not sure where you’re getting your info, but good topic. I needs to spend some time learning much more or understanding more.
    Thanks for fantastic information I was looking for this information for my mission.

  3. writing dissertation abstract
    [url=”https://dissertationwritingcenter.com”]dissertation writing service online[/url]
    dissertation template

  4. buy dissertation online
    [url=”https://writing-a-dissertation.net”]professional dissertation help[/url]
    doctoral dissertation help your

  5. dissertation proofreading services
    [url=”https://writingadissertationproposal.com”]dissertation chapters[/url]
    acknowledgements dissertation

  6. live online casino usa
    [url=”https://casino-online-jackpot.com”]online casinos for usa players[/url]
    online casinos real money no deposit

  7. gay dating a macho
    [url=”https://gay-singles-dating.com”]gay dating long term relationship oriented guys into spanish[/url]
    women veteran dating sites gay

  8. In a second pharmacokinetic study, single doses and multiple doses of dapoxetine 30, 60 mg were evaluated in a randomized, open-label, 2-treatment, 2-period, crossover study of 42 healthy male volunteers over 9 days buy cialis uk His tone says check his expression

  9. Искусственные гипоаллергенные сверхлёгкие ресницы хороший мастер подберёт с учётом особенностей и состояния ваших родных ресничек. Если ресницы тонкие и слабые, то вам подойдут самые лёгкие искусственные ресницы. Мастер порекомендует вам не делать их слишком длинными и не использовать объём больше, чем 2D. Это нужно, чтобы нагрузка на свои ресницы была посильная. Сыворотки для ресниц Изгибы ресниц для наращивания марки Lovely Сегодня наращивание ресниц остается самой востребованной процедурой в салонах, а броу-бары неминуемо трансформируются в лаш-бары. Наращивание — идеальный вариант для тех, кому лень краситься по утрам и смывать косметику по вечерам. К тому же это ухоженный вид 24 часа в сутки. Все еще сомневаетесь, выбирая подходящий вид ресничек и техники наращивания? Напоследок мы поможем вам окончательно определиться с оптимальным вариантом, который сделает ваш взгляд выразительнее, а сами реснички намного пышнее и привлекательнее. Все, что для этого нужно, – это понимать, какие виды наращивания подходят разным формам и разрезам глаз. https://abbatrust.org/community/profile/jeffreychristen/ Новая услуга бесплатной гравировки кистей при заказе кистей на сумму от 10 000 руб и условии предоплаты на сайте БрендJEANMISHEL Издательство Эксмо Хочу и буду. 6 правил счастливой жизни, или Метод Лабковского в действии, Михаил Лабковский Мы получаем и обрабатываем персональные данные посетителей нашего сайта в соответствии с официальной политикой. Если вы не даете согласия на обработку своих персональных данных, вам необходимо покинуть наш сайт. Продукцию Mary Kay® можно приобрести через Независимого Консультанта по красоте. В корзине Корзина пуста Скажите «привет» вашим новым помощникам — революционным спиралевидным резинкам-браслетам для волос Круглосуточная горячая линия Издательство Эксмо Хочу и буду. 6 правил счастливой жизни, или Метод Лабковского в действии, Михаил Лабковский Самовывоз Старейшая интернет-аптека в России. Работаем для вас eжедневно с 2000 года.

  10. Amoxicillin is used to treat many different types of infections caused by bacteria, such as ear infections, bladder infections, pneumonia, gonorrhea, and E. coli or salmonella infection.
    Order amoxicillin in Essex online – Save UP to 201.99$ and get two free pills Viagra or Cialis or Levitra (available with every order).
    Highest Quality Generic Drugs. Safe & Secure Payments. Fast & Free Delivery.

  11. com is for informational purposes only, and is not a substitute for medical advice, diagnosis, or treatment provided by a qualified healthcare provider. clomid canada pharmacy Clinical outcome variables, such as embryo quality, implantation rate, and clinical pregnancy rate were also noted.

  12. Ampicillin is used to treat many different types of infections caused by bacteria, such as ear infections, bladder infections, pneumonia, gonorrhea, and E. coli or salmonella infection.This is an irreplaceable diuretic which is available to everybody due to its safety, efficiency, and low cost – only $0.25 per pill.
    Special offer – Buy Ampicillin On Line and Get Discount for all Purchased! – No Prescription Required.
    Safe & Secure Payments. Fast & Free Delivery.
    Two Free Pills (Viagra or Cialis or Levitra) available With Every Order.

  13. Appreciate it. Quite a lot of write ups.
    online pharmacies [url=https://canadadrugspower.com]order prescriptions online without doctor[/url] best online canadian pharmacy

  14. Many thanks. Very good information.
    college application essay editing services [url=https://quality-essays.com]essays on service[/url] high quality article writing service

  15. Terrific information. Thanks a lot!
    how to write an introduction for a narrative essay [url=https://orangepornhub.com]https://orangepornhub.com/[/url] essay writing website

  16. You have made your position quite effectively.!
    writing an autobiography essay [url=https://quality-essays.com]how to write a college entry essay[/url] urgent essay writing service

  17. Seriously all kinds of useful facts.
    canadian pharmacy online [url=https://canadapharmacyspace.com]best online international pharmacies[/url] best online pharmacy stores

  18. Valuable write ups. Cheers.
    how to write a conclusion paragraph for an essay [url=https://orangepornhub.com]essay writing service[/url] academic writers online review

  19. You actually expressed it adequately!
    essay homework help online [url=https://essayssolution.com]https://essayssolution.com/[/url] i need help writing a personal statement

  20. You actually revealed that very well!
    reputable canadian mail order pharmacies [url=https://canadianonlinepharmacybase.com]medicine online shopping[/url] canadian pharmacy online

  21. Thanks a lot, I enjoy it.
    mexican online pharmacies [url=https://canadapharmacyspace.com]canadian online pharmacies prescription drugs[/url] canadian pharmacies-24h

  22. สล็อต เว็บ ตรง แตกง่ายแตกหนักแจกจริง ไม่ผ่านเอเยนต์ต้องที่นี่กับ PGSLOT-TH.COM เว็บเกมคุณภาพจากค่ายชื่อดังอย่าง PG SLOT ที่มีลูกค้าเล่นอย่างมากมายทั่วโลก มาแรงปี2022

  23. Fantastic stuff. Appreciate it.
    how to write a concept essay [url=https://homeworkcourseworkhelps.com]https://homeworkcourseworkhelps.com/[/url] professional letter writing services

  24. Amazing quite a lot of good information.
    canada pharmaceutical online ordering [url=https://onlinepharmacycanadahelp.com]trust pharmacy canada[/url] online canadian pharmacies

  25. You actually suggested it exceptionally well.
    best online pharmacy stores [url=https://canadianpharmacyonlinedb.com]order prescriptions online without doctor[/url] ed meds online

  26. The Free Extra Chips team makes sure that every casino listed meets our very strict set of standards and requirements. We listen to players and casinos alike to ensure that every player can access the best games, the best free spins, no deposit bonuses, and the safest, most secure online casinos around. Love a freebie? Sign-up then verify your account today and you will receive 77 FREE slots spins with no deposit required. The $25 no-deposit bonus is exactly what it sounds like, free money with no risk. Feel free to use it on slots, table games, live dealer games, and more. Free slots no deposit can be found on some sites, and we have investigated the offers provided to the UK players. These promotions are usually available upon registration. In such a way, casinos attract new customers. For players, this means they get a free possibility to win some cash and easily get it to their accounts. Below, you will see some of the best sites with free online slots no deposit in the UK. https://ohaakademy.com/community/profile/clarenceshears/ US players can use different bonus codes to claim different No Deposit Bonuses. Usually, online casinos make sure to provide several bonus offers that would fit any type of casino player. Some no deposit bonuses simply require you to input a special code or use a coupon to unlock them. As with all other casino bonuses, no deposit bonus codes are not concealed or difficult to find. You’ll find them promoted on the homepage or the bonus page. You might even get codes sent by email in the casino’s newsletter. Sometimes, an online casino sites will ask for no deposit bonus codes to redeem your no deposit casino bonus. Below is a list of all legit US online casino bonus codes that help you take advantage of the best no deposit casino offers. Visit our directory and claim any of our featured USA no deposit bonuses with codes to find out. All our codes are the best and we stand behind every one.

  27. Nicely put. Regards.
    online medicine tablets shopping [url=https://canadianonlinepharmacybase.com]list of approved canadian pharmacies[/url] mexican pharmacies online

LEAVE A REPLY

Please enter your comment!
Please enter your name here

+ 46 = 51