Returning Single Row using Execute SQL Task in SSIS 2008 R2 with Example

By
roopesh.valluru
-
298
25157
single row

Friends,

The Execute SQL task is one of the handier components in SQL Server Integration Services (SSIS) because it lets you run Transact-SQL statements from within your control flow. The task is especially useful for returning result sets that can then be used by other components in SSIS package.

The Execute SQL task runs SQL statements or stored procedures from a package. The task can contain either a single SQL statement or multiple SQL statements that run sequentially. We can use the Execute SQL task for the following purposes:

  • Truncate a table or view in preparation for inserting data.
  • Create, alter, and drop database objects such as tables and views.
  • Re-create fact and dimension tables before loading data into them.
  • Run stored procedures. If the SQL statement invokes a stored procedure that returns results from a temporary table, use the WITH RESULT SETS option to define metadata for the result set.
  • Save the rowset returned from a query into a variable.

The Execute SQL task can be used in combination with the Foreach Loop and For Loop container to run multiple SQL statements. These containers implement repeating control flows in a package and they can run the Excute SQL task repeatedly. For example, using the Foreach Loop container a package can enumerate files in a folder and run an Execute SQL task repeatedly to execute the SQL statement stored in each file.

When using the Execute SQL task to return a result set, we must also implement the necessary variables and parameters to pass data into and out of the T-SQL statement called by the task.

In this scenario, I’ll use  Execute SQL task. The  task retrieves a single value from a table in theAdventureWorks2008 database. That value is returned by the task as a single-row result set.

Adding  Variable to the SSIS Package

The first variable I’ll create is the EmpNum variable. If the Variables window is not open, right-click the Control Flow workspace, and then click Variables. In the Variables window, add a new variable by clicking on the Add Variable icon.

Name the new variable EmpNum, and ensure that the scope is set at the package level, as indicated by the package name. (Here my package name is Execute sql task with single row). Next, set the data type to Int32 and the value to 0. The Execute SQL task will use the variable to store the value it retrieves from the database.

Now drag and drop Execute Sql Task to Control Flow pane.

Adding a Connection Manager to the SSIS Package

The next step is to create a connection manager that points to the AdventureWorks2008 database. Right-click theConnection Manager s window, and then click New OLE DB Connection

When the Configure OLE DB Connection Manager dialog box appears, click the New button to launch theConnection Manager dialog box.From the Server name drop-down list, select the name of your SQL Server instance, and then select an authentication type. From the Select or enter a database namedrop-down list, select  database.  you can see, I selected SQL Server instance, Windows Authentication as my authentication type, and the AdventureWorks2008 as my database.

Be sure to test the connection by clicking the Test Connection button. If the connection is good, click OK to close the Connection Manager dialog box.

When you’re returned to the Configure OLE DB Connection Manager dialog box, you’ll see that your new connection has been added to the Data connections section. Click OK to close the dialog box. Your connection should now be listed in Connection Managers window.

If you want, you can rename your connection manager to something more appropriate. To do so, right-click the connection, click Rename, and type in the new name.

Returning a Single-Row Result Set

Execute SQL task will return a single-row result set, which in this case, will contain only one value.

In this example, I’ll retrieve the highest BusinessEntityID value from the HumanResources.Employee table. I’ll start by using the first Execute SQLtask to retrieve the value and pass it to the EmpNum variable.

To get started, drag the Execute SQL task onto the Control Flow design surface. Then double-click the task to open the Execute SQL Task Editor. The editor opens to the General page

.

Notice that the General section contains the Name property and the Description property. The Name property refers to the task name. You should name the task something suitable.

The next section on the General page is Result Set. Notice that this section includes only the ResultSetproperty.

In this exercise, our query will return only a single value. Consequently, we will choose the Single row option.

Our next step is to associate our result set value with a variable that will store the value we retrieve from the database. To do this, go to the Result Set page of the Execute SQL Task Editor.

The main grid of the Result Set page contains two columns: Result Name and Variable Name. Click the Addbutton to add a row to the grid. In the Result Name column, enter the column name returned by your query (MaxEmpID). In the Variable Name column, select the User:: EmpNum variable.

 

If our single-row result set contains multiple columns, we would have had to map a variable to each column. However, because we returned only one value, we needed only one mapping.

Once you’ve associated your result set value with a variable, click OK to close the Execute SQL Task Editor. The task should now be set up to return a single-row result set.

Thanks a lot Archana for the document and it is well designed.

Regards,
Roopesh Babu V

RELATED ARTICLESMORE FROM AUTHOR

298 COMMENTS

  2. All of the comp-earning apps are tied to some sort of casino-friendly games. Some are providing slot games that can be found at actual casinos; others have their own games that are either completely new creations, or have original games that look and play a lot like casino games that they’re inspired by. Some offer other games, like Blackjack or Video Poker, aside from slots. Ignition: Our top pick, Ignition Casino has done an amazing job with its poker app to ensure the most thrilling mobile experience for all of its poker fans. If you join the casino, you can take part in regular poker tournaments but before doing so, consider benefiting from its lucrative offer for newcomers that grants up to $3,000 in bonus funds to play slots and poker.
    http://ohanataxi.com/us/bbs/board.php?bo_table=free&wr_id=6086
    Most online slot machines are optimized for mobile phones and other personal portable devices so that punters can squeeze in a little bit of gambling into their busy and moving lives. Most established software providers build their games with the HTML5 technology to equip free slots no download for Android and iOS sets. Over a stable internet connection, punters can play free slots online no download without any restrictions. Some casino sites do have an application rendition to download on mobile phones, while the rest can be accessed directly from any Flash-enabled browser. Free slots without downloading, as well as real money slots come in different types: video slots, classic slots, fruit machines, 3-reel slots, 3D slots, mobile slots, and offline slots. OnlineSlots isn’t an online casino, we’re an independent online slots review site that rates and reviews online casinos and slot games. That means you can play free slots on our website with no registration or downloads required.

  3. Cierpliwość jest niezbędna w grze w pokera, ponieważ pozwala podejmować przemyślane decyzje i unikać emocji, które przejmą kontrolę nad grą. Cierpliwość pomoże ci również zachować koncentrację, dzięki czemu będziesz w stanie dostrzec potencjalne możliwości, które inni mogą przegapić, i odpowiednio je wykorzystać. Nie jestem święty. W miarę regularnie biorę udział w różnego rodzaju nielegalnych grach pokerowych. Dlaczego? Bo legalnej gry w zasadzie w Polsce nie ma, a ja po prostu lubię grać w pokera i lubię sam decydować, na co wydaję swoje ciężko zarobione pieniądze. Tymczasem ustawodawca pokerzystom dość skutecznie to uniemożliwia.
    http://fuelregulations.com/forum/profile/midddtabinri197/
    Kasyna internetowe nie stanowią pewnego źródła dochodu, a nawet często powodują jedynie uszczuplenie portfela. Niestety hazard ma to do siebie, że jest bardzo ryzykowny i nie warto nikogo do niego namawiać, ale jeśli kogoś interesuje tego typu forma rozrywki to zdecydowanie lepiej wykorzystać do tego jedynie te legalne i sprawdzone kasyna. W ten sposób oszczędzimy sobie niepotrzebnych nerwów związanych z ewentualną wypłatą wygranej lub stresów tuż po. Licencja dla niektórych graczy może okazać się niewystarczająca w przypadku określania, czy dane kasyno online wypłacalne jest zupełnie bezpieczne. W takim wypadku dobrze jest sprawdzić, którzy niezależni audytorzy sprawdzili kasyno i co to oznacza. Oto najważniejsi z nich: 

  4. The traditional casual game requires users to spend a lot of time to accumulate their wealth ability skills in the game, which is both the feature and fun of the game, but at the same time, the accumulation process will inevitably make people feel tired, but now, the emergence of mods has rewritten this situation. Here, you don’t need to spend most of your energy and repeat the slightly boring “accumulation”. Mods can easily help you omit this process, thereby helping you focus on enjoying the joy of the game itself Play Vegas slot machines & casino games. Hit HUGE JACKPOTS on 100+ slot games! Play online Vegas SLOTS game and win Huge Jackpots! Unlimited Money LEGENDARY GAME JACKPOTSJACKPOTS a’ feitheamh riut anns na prìomh gheamannan casino slot! Stàlaich Lotsa Slots – Slots Casino Vegas an-asgaidh an-diugh agus cruinnich Bonus Fàilte Sònraichte Lotsa Limited, Bònasan Làitheil agus barrachd. Tlachd a ghabhail air jackpots anns a ‘gheama casino air-loidhne seo.
    https://www.curioos.com/slotscasinonode
    For obvious reasons, we’ll be looking at the slots selection first and foremost in this section. However we may also take into account what else is available for bonus points on the score. After all, you can’t always play online slots, can you? Or maybe you can. Is it a novelty? Yes. Does it feature some awfully silly graphics? Of course. But these are the reasons to love MONOPOLY Slots. Why? Because those are the qualities of some of the very best slot games. That means this casino slot app for iPhone fans gets a big tick from us.   You may not access this content You can even use these to test the waters before you sign up at a New Jersey online casino where you can play real money slots, as most of the free slots are the same or similar in nature. Whether you’re looking for a full database of free slots in NJ or the best free spins bonuses, you’re in the right place.

  5. Το 10 Bet Casino θα μπορούσε να χαρακτηρισθεί ο παράδεισος του παίκτη καθότι προσφέρει πολλά διαφορετικά είδη παιχνιδιών. Επίσης διαθέτει ένα ελκυστικό πακέτο μπόνους πολλές μεθόδους συναλλαγών και μία αρκετά καλή εξυπηρέτηση πελατών! Read Full Review Η Paysafecard είναι σαν μετρητά που μπορείτε να χρησιμοποιήσετε για να πληρώσετε online: ανώνυμα, χωρίς τα προσωπικά σας στοιχεία, με ασφάλεια και προσβάσιμη σχεδόν από οπουδήποτε. Προσφορες Δημοφιλείς
    https://emilioayvs740730.answerblogs.com/20807852/φρουτάκια-on-line
    @ Copyright Foxcasino | About us | Επικοινωνία | Όροι χρήσης 21+ | ΑΡΜΟΔΙΟΣ ΡΥΘΜΙΣΤΗΣ ΕΕΕΠ | ΚΙΝΔΥΝΟΣ ΕΘΙΣΜΟΥ & ΑΠΩΛΕΙΑΣ ΠΕΡΙΟΥΣΙΑΣ | ΓΡΑΜΜΗ ΒΟΗΘΕΙΑΣ ΚΕΘΕΑ: 210 9237777 | ΠΑΙΞΕ ΥΠΕΥΘΥΝΑ ΝΕΑ ΚΑΖΙΝΟ LIVE Μπορείτε επίσης να μάθετε τα πάντα για όλα τα παιχνίδια, τις live καζίνο προσφορές*, τα δυνατά σημεία και αυτά στα οποία χρειάζεται βελτίωση κάθε ένα από τα live casino, αλλά και πως λειτουργεί το Mobile live casino σε κάθε πλατφόρμα. Τα φρουτάκια που κυκλοφορούν είναι αμέτρητα κι ο αριθμός αυξάνεται συνεχώς. Ο διαχωρισμός τους σε κατηγορίες είναι μεγάλης σημασίας, αφού έτσι μπορούμε να αντιλαμβανόμαστε καλύτερα τι μπορεί να μας προσφέρει το κάθε slot κι αν απευθύνεται πραγματικά στις δικές μας προτιμήσεις. Πάμε να δούμε τις κατηγορίες των κουλοχέρηδων από το foxcasino.

  88. farmacia envГ­os internacionales [url=http://farmaciabarata.pro/#]farmacia barata[/url] farmacias online seguras

  114. zithromax for sale cheap [url=https://azithromycinotc.store/#]buy azithromycin over the counter[/url] zithromax without prescription

  117. Welcome to Sloto Cash Online Casino! We are a well-respected, fun-loving, real money online casino with stupendous casino games, bountiful casino best bonuses, including a really generous welcome bonus for new players and edge of your seat winning real money casino thrills. So, come on in and enjoy all the BEST online max cash gaming excitement and online slot casino real money action that Sloto’Cash has to offer. Gambling can be harmful if not controlled and may lead to addiction! Compared to their land-based counterparts, there are no limitations with online slots. The result: there’s an incredible choice of slot titles available – particularly here at 32Red Casino. This includes innovative 5-reel favourites such as Immortal Romance, millionaire-making jackpot slots like Major Millions and Mega Moolah, and games with memorable motion-picture themes like Game of Thrones and Terminator II. Plus, if you want to take a step back in time, we also have traditional 3-reel slot machines for simple yet exhilarating experiences.
    https://paxtonigdv808528.blogdigy.com/red-seven-slots-34362972
    Recent changes: Fixed issue with game not starting correctly on Android 12 devices.Fixed issue with hold button graphics staying as “nudge” buttons occasionally. Copyright © AquaSlot – 2021. All Rights Reserved Copyright © AquaSlot – 2021. All Rights Reserved – Free Download- Download For Free Phone- reserata Free- Nulla amissio pecuniae- Nullamque causam – Free Download- Download For Free Phone- reserata Free- Nulla amissio pecuniae- Nullamque causam Base APK: com.sanga.hauntedhouseslot.apk A real explosion happened when Survivor.io was announced to be available to players via the mobile battle version. If you are a player who loves first-person shooting experiences, this will be a great choice.

  118. zithromax cost australia [url=http://azithromycinotc.store/#]zithromax 500 mg lowest price pharmacy online[/url] zithromax 250 mg tablet price

  122. medicine in mexico pharmacies [url=https://mexicanpharmacy.site/#]buy medications in Mexico without a prescription[/url] pharmacies in mexico that ship to usa

  126. canadian pharmacy cialis reviews [url=http://drugsotc.pro/#]us online pharmacy[/url] ordering drugs from canada

  132. I’m grateful for their around-the-clock service. https://mexicanpharmonline.com/# buying prescription drugs in mexico
    mexican border pharmacies shipping to usa [url=https://mexicanpharmonline.shop/#]mexico online pharmacy[/url] buying prescription drugs in mexico

  160. Generic Cialis without a doctor prescription [url=https://cialis.foundation/#]Generic Cialis without a doctor prescription[/url] buy cialis pill

  175. п»їlegitimate online pharmacies india: indian pharmacy – best online pharmacy india indiapharmacy.pro
    reputable mexican pharmacies online [url=http://mexicanpharmacy.company/#]buying from online mexican pharmacy[/url] medication from mexico pharmacy mexicanpharmacy.company

  179. buy prescription drugs from india: indian pharmacy paypal – п»їlegitimate online pharmacies india indiapharmacy.pro
    best online pharmacies in mexico [url=https://mexicanpharmacy.company/#]medication from mexico pharmacy[/url] mexico drug stores pharmacies mexicanpharmacy.company

  184. buying from online mexican pharmacy: reputable mexican pharmacies online – best online pharmacies in mexico mexicanpharmacy.company
    mexican drugstore online [url=http://mexicanpharmacy.company/#]mexican rx online[/url] pharmacies in mexico that ship to usa mexicanpharmacy.company

  187. india online pharmacy: indian pharmacy online – top 10 pharmacies in india indiapharmacy.pro
    safe canadian pharmacy [url=http://canadapharmacy.guru/#]best mail order pharmacy canada[/url] canadian pharmacy meds reviews canadapharmacy.guru

  190. buying from online mexican pharmacy: mexico drug stores pharmacies – mexican online pharmacies prescription drugs mexicanpharmacy.company
    best online pharmacy india [url=https://indiapharmacy.pro/#]cheapest online pharmacy india[/url] top online pharmacy india indiapharmacy.pro

  196. buying from online mexican pharmacy: mexico pharmacies prescription drugs – mexican mail order pharmacies mexicanpharmacy.company
    buying prescription drugs in mexico online [url=http://mexicanpharmacy.company/#]medicine in mexico pharmacies[/url] purple pharmacy mexico price list mexicanpharmacy.company

  204. mexican pharmaceuticals online: medicine in mexico pharmacies – pharmacies in mexico that ship to usa mexicanpharmacy.company
    pharmacies in mexico that ship to usa [url=http://mexicanpharmacy.company/#]pharmacies in mexico that ship to usa[/url] buying from online mexican pharmacy mexicanpharmacy.company

  253. what are ed drugs [url=https://edpills.monster/#]best ed pills non prescription[/url] ed meds online without doctor prescription

  263. cost of amoxicillin 875 mg [url=https://amoxicillin.best/#]amoxicillin no prescription[/url] amoxicillin 500mg capsules uk

  268. best price for prescription doxycycline [url=https://doxycycline.forum/#]Buy Doxycycline for acne[/url] doxycycline pharmacy uk

  271. Быстро возводимые здания: прибыль для бизнеса в каждой части!
    В современной сфере, где моменты – финансы, сооружения с быстрым монтажем стали реальным спасением для фирм. Эти новаторские строения комбинируют в себе повышенную прочность, эффективное расходование средств и быстрое строительство, что дает им возможность лучшим выбором для различных коммерческих проектов.
    [url=https://bystrovozvodimye-zdanija-moskva.ru/]Строительство быстровозводимых зданий цена[/url]
    1. Срочное строительство: Минуты – важнейший фактор в коммерции, и экспресс-сооружения позволяют существенно сократить время монтажа. Это особенно ценно в постановках, когда актуально оперативно начать предпринимательство и начать зарабатывать.
    2. Финансовая экономия: За счет оптимизации процессов производства элементов и сборки на месте, расходы на скоростройки часто уменьшается, по отношению к обычным строительным проектам. Это способствует сбережению денежных ресурсов и добиться более высокой доходности инвестиций.
    Подробнее на [url=https://bystrovozvodimye-zdanija-moskva.ru/]www.scholding.ru[/url]
    В заключение, экспресс-конструкции – это великолепное решение для проектов любого масштаба. Они обладают эффективное строительство, бюджетность и надежность, что делает их оптимальным решением для профессионалов, желающих быстро начать вести бизнес и выручать прибыль. Не упустите шанс экономии времени и денег, прекрасно себя показавшие быстровозводимые сооружения для вашего предстоящего предприятия!

  272. generic amoxicillin cost [url=https://amoxicillin.best/#]cheap amoxicillin[/url] cost of amoxicillin 875 mg

  279. top online pharmacy india [url=https://indiapharmacy.site/#]п»їlegitimate online pharmacies india[/url] mail order pharmacy india

LEAVE A REPLY

Please enter your comment!
Please enter your name here

25 + = 35