YT YouTube2Text

YouTube2Text → Transcripts → SQL Server Integration Services (SSIS) Part 17 - Event-handling and logging

SQL Server Integration Services (SSIS) Part 17 - Event-handling and logging — Transcript

by WiseOwlTutorials · 3,085 words · 450 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00[Music]
0:00Welcome to this Weisowl tutorial on
0:02event handling and logging in SQL Server
0:05Integration Services. Here's what we'll
0:07learn on the tutorial.
0:09We'll begin by having a look at event
0:11handling within integration services.
0:13So, we'll look at how to create event
0:14handlers, how to review the ones you've
0:16already got set up, and how to get
0:18variables to raise events when their
0:20values change.
0:22We'll then go on to look at logging. So,
0:24we'll look at the main types of logging
0:25available to you, how to log information
0:27on a package's execution progress to
0:30text files, and how to do the same thing
0:32to a SQL Server table. And finally,
0:35we'll have a quick look at audit
0:36transforms, which provide another way to
0:39log information about what's going on
0:40within your package. So, let's get
0:43started.
0:45To illustrate the concept of event
0:48handlers and how to make them work
0:49within integration services, I've
0:51created a really simple package which
0:53imports from an Excel worksheet the
0:55number of series, imports a number of
0:57mentors and stores those two bits of
0:59information in variables called number
1:00mentors and number series. What the
1:03script then does is display the result
1:05of those two variables. If I run this
1:08package, you'll see that it comes up
1:10with a dialog box saying I've imported
1:11nine series and 10 mentors. The precise
1:15details of how these two data flow tasks
1:17work isn't terribly important, but just
1:20for the sake of your curiosity, I'll
1:21show you one of them. It takes in an
1:23Excel worksheet and uses a row count
1:25transform to store the number of rows in
1:28a variable.
1:31It works, but you could argue that this
1:34uh script task isn't actually part of
1:36the control flow at all. It's just
1:38almost like a debug message which is
1:40popping up on screen to say that
1:41everything's worked successfully.
1:43This would be even truer if I had used
1:45something like a send mail task, in
1:47which case I really would just be
1:49reporting on the progress of what's
1:50happening without it necessarily being
1:52part of my control flow. So what I'm
1:55going to do is show you an alternative
1:56way to achieve the same thing using an
1:58event handler and then we can decide
2:00whether or not it's better.
2:02To do that, I'll just cut that to my
2:04clipboard clipboard. I'm going to need
2:06it soon. And I'll connect these two
2:09tasks up together so they run in
2:10sequence.
2:12What I want to do is say that when this
2:13get number of mentors task has finished
2:15executing then I want my message to
2:18appear. So what I'm going to do is go to
2:20the event handlers tab and click on the
2:23drop on the left hand side and choose to
2:26which task or which package I want to
2:28attach an event.
2:30Now because I have the get number of
2:32mentors selected, it's chosen that
2:34automatically. But I could attach an
2:36event to any one of these different
2:37parts of my package. So I'll choose
2:40that. I can then choose the event and
2:43there are three main ones you'll choose
2:44typically which are pre-execute which
2:47runs immediately before the task runs
2:49postexecute runs immediately after it
2:52has finished executing and on error is
2:54fairly self-explanatory.
2:57So I'm going to choose postexecute and I
3:00now need to click to create my
3:01postexecute event handler.
3:04If you're expecting something dramatic
3:05to happen at this point you'll be
3:06disappointed. All it does is clear the
3:08screen. I've got an event handler. You
3:11can see that because it's gone bold in
3:12my list, but it's not actually doing
3:14anything. So, what I'll do is paste in
3:17my script task there. And if I now go
3:19back to my control flow, you'll see that
3:22when I run this um package, when I
3:24finished executing this second task, my
3:27event handler will kick in and display
3:29exactly the same dialog box as we saw
3:31earlier. So, let's see if this works. I
3:34run my package.
3:36It's finished executing the second task.
3:38And lo and behold, my event handler has
3:40displayed the dialogue box.
3:44As to whether it's better or not, I
3:46leave that up to you. Personally, I
3:48think it's much clearer if I have
3:50everything in my control flow and I
3:52don't like the fact that something's
3:53hidden away in an event handler, but you
3:55may disagree.
3:59In the previous part of this tutorial, I
4:02created an event handler for the get
4:04number of menus tasks postexecute event.
4:08What I want to do now is see how you can
4:09see to which which event handlers you've
4:11created for which part of a package.
4:14One way to do that of course is to click
4:16on the event handlers tab and then click
4:18on the part of the package you're
4:20interested in. So if you're clairvoyant
4:22or have a bit of insight, you could
4:24guess that you've attached something to
4:26the get number of menus task and when
4:29you choose okay you'll see the
4:30suspicions were correct because it
4:32appears in bold in the the on
4:34postexecute event appears in bold in the
4:36list. You can then click on that and see
4:38what that event is doing.
4:41What would be nice though is to have
4:43some holistic approach where you can see
4:44everything in one place and it's called
4:46package explorer
4:48and you can see there that you can
4:50expand um the executables which are
4:53essentially the tasks within a package
4:55to see the events attached to each of
4:57them. If we look at the get number of
4:59series task it's got no event handlers
5:02attached to it. But if we look at the
5:04get number of mentors task you can see
5:06it has got event handlers attached.
5:09In fact, the only events are attached to
5:11the onpost execute event. And you can
5:14expand that to see what the event
5:16handlers are. It's a bit disturbing to
5:19see that the event handlers have
5:21themselves potential to have event
5:22handlers. I don't even want to go there.
5:26So, that's how you can use package
5:27explorer to review the events you've
5:29attached to different parts of a
5:31package.
5:34So far, we've got normal event handlers
5:36working. What I want to do now is show
5:38that you can get SIS to react to when a
5:40variable values has changed. But I
5:43should warn you, it's quite complicated
5:44and there's so many things which can go
5:46wrong that you might want not to bother
5:49doing it. Let's have a look at our
5:51package we've got. I've got a variable
5:53called answer which holds the value zero
5:55initially and I've got an expression
5:57task which changes the value of the
5:59variable to 42 which is the meaning of
6:01life as we all know. I've then got a
6:03script which displays the value of that
6:05variable. So if I run this package, what
6:07I'll get is a message saying the meaning
6:09of life is 42. So far so good. What I
6:13want to do now is get the script to run
6:16when the value of this variable called
6:18answer changes. And that should be
6:20fairly straightforward. I should be able
6:22to cut this script, go to my event
6:24handlers and attach it to the on
6:27variable value changed of the meaning of
6:30life task.
6:33If I click on that, it will create the
6:35event and I can paste in my script.
6:38And surely that's job done. Let's find
6:41out. If I run this package now, you'll
6:43see that absolutely nothing happens. No
6:45script runs. And the reason for that is
6:49because my variable isn't telling the
6:51world its value has been changed because
6:53by default variables don't. So what I
6:56need to do is find a column in here
6:57saying whether the value of the variable
6:59being changed will raise an event. And I
7:01can do that by going to the grid options
7:03and choosing to display the raise event
7:05when variable value changes column.
7:07Obviously
7:09the default for that is false. So I'm
7:11going to change it to true.
7:13And the variables value changing should
7:16now raise an event which should be
7:17handled and I should see my message,
7:20shouldn't I? But if I run it, I think
7:22we'll see still nothing happens.
7:25The problem now is that the scope of the
7:27variable is for the entire package, but
7:30it's trying to raise an event within a
7:32specific task. So, what I need to do is
7:35to click on the move variable but icon
7:38and move my variable within the task.
7:43The variable disconcertingly vanishes,
7:45but that's cuz I haven't got my task
7:46selected. Once I do that, you can see
7:48that it reselects it or redis it. The
7:52really nasty bug is it resets this raise
7:56change uh property back to false which I
7:59must admit took me a long time to
8:00notice. So I need to change it back to
8:02true otherwise the variables value being
8:04changed won't raise an event. And surely
8:08surely now it's all going to work. Well
8:11sort of. It's clearly running an event
8:14but what it's not doing is displaying
8:15the script. And at this point I'm going
8:18to go to guesswork.
8:20I think what's happening is this. I
8:23think for some reason a script within an
8:26event handler raised from this
8:28particular type of event can't pass in
8:31the variable into it. So what I'm going
8:34to do is delete the variable, edit the
8:36script, and cheat. So instead of
8:39displaying a message giving the value of
8:41the variable, what I'm actually going to
8:44do is comment that out and instead just
8:47display that the meaning of life is 42.
8:53And I think if I now run this,
8:57the variable value changed will raise an
9:00event and display that message.
9:04And that's how you can get a value of a
9:06variable being changed to raise an
9:08event.
9:11I've created an extremely complicated
9:14package. It sets a variable to one and
9:17then displays the value of the variable.
9:19And if I run this package, you can see I
9:21get an error, sorry, a dialog box saying
9:23the value of the variable is one. That
9:25was English sarcasm, by the way, if you
9:27were wondering.
9:29What I now want to do is to log what's
9:31going on. So I can look at a a trace
9:33from the executing the package and track
9:36down any errors. There's a couple of
9:39ways I could do this. A couple of
9:40obvious ways. One is to create a text
9:42file. That's the sort of thing I might
9:44get as a result. So give me lots and
9:46lots of information about what's going
9:47on. And an alternative idea might be to
9:50use SQL Server. So I've got here
9:56the output from logging. And you can see
9:58I've already accumulated 852 rows of all
10:01sorts of useful information. The
10:03advantage of SQL Server is of course
10:05that I can then write queries against
10:06it.
10:08Here are the types of logging available.
10:10You can invoke logging as I'll show
10:12shortly by choosing SSIS from the menu
10:14and choosing logging. The logging
10:16providers you can use are you can send
10:19information to a text file. You can send
10:22it to a predetermined SQL server table
10:24and I'll show you how to do that
10:25shortly. You can use SQL Server Profiler
10:29and what that does is produce a SQL
10:31profiler trace file about which I know
10:33virtually nothing I'll be honest. You
10:36can send information to the Windows
10:37event log and what it will do is log it
10:40under the source name SQLIS package 1110
10:45or you can send it to an XML file. I'm
10:48going to show the first two.
10:51So what we'll do now is set up logging
10:53to send information to a text file.
10:59To log to a text file, the first thing
11:02to do is go to SSIS and choose logging.
11:06What I can then do is choose which sort
11:09of provider I want. In this case, it's a
11:11text file one and add that into the list
11:13of providers.
11:15Before I can use it, I need to configure
11:17it. So, I need to choose a file
11:18connection. But because I don't have one
11:20set up for this project package, I'm
11:22going to have to create a new one. I'm
11:24going to choose an existing file. I'm
11:26not going to create one, although I
11:27could. And I'm going to browse to my
11:29file called log.txt and use that. So,
11:33I'm sending information to that file.
11:35But at the moment, I'm not actually
11:37logging anything. So, what I need to do
11:39is choose which bits of the package I'm
11:42going to log information for. Whether
11:44it's the entire package, whether it's
11:46the individual tasks within it, or
11:48whether it's a combination of both. as
11:49here.
11:51It still won't work because I now need
11:53to choose to tick the login provider
11:56which I've created so that it actually
11:57uses it. And then I could do with going
12:00into the details tab to fine-tune
12:03exactly what's going on. At the moment
12:05it's not capturing information for any
12:07of these events. What I want to do is
12:09after every task or and the package
12:11itself completes I want to capture the
12:14information from the onostexecute event.
12:16So I'll tick that. And I could, if I
12:19like, go into much more detail on the
12:20advanced tab and choose which bits of
12:23information I want to capture. So for
12:24example, if I don't want the operator, I
12:26could just untick that. You can never
12:28accuse SSIS of not giving you enough
12:30logging information.
12:32What I could do if I wanted is save that
12:34as an XML file so that I could then
12:37retrieve it later on for a different
12:38package. But I'm happy with what I've
12:40done. And what I'm going to do is choose
12:42okay and then run it. And what it will
12:44do is run marginally more slowly because
12:46it's actually writing out text
12:48information to that file called log.txt
12:51which I'm actually going to ask you to
12:52believe. So I can stop running and what
12:55I'll do now is show you how to log to
12:57SQL Server.
13:00The way you log to SQL Server is almost
13:02identical to the way you log to a text
13:04file. The first thing to do is choose
13:06SSIS from the menu and choose logging
13:08and then choose the type of provider you
13:10want to use. In this case is SQL Server.
13:13I need to add that into my list of
13:14providers and probably at this point
13:16untick the text one and choose to use
13:18SQL Server instead. Although there's
13:20nothing to stop me using both. I can
13:23choose here the scope of what I want to
13:24log. I'm just going to log information
13:26for the package rather than also for the
13:28individual tasks within it. And I need
13:31to configure my SQL server log provider
13:32to say which database is going to. So
13:35I've got a connection to the XFactor
13:36database and I'll use that. I can then
13:39go into details and specify which events
13:42I'm going to log information for. I'll
13:44choose both on postexecute and on
13:46pre-execute.
13:47And I'll click on the advance button and
13:49choose the fact I actually don't again
13:52want to log the operator. I'm not quite
13:53sure why, but I don't like it. I can
13:56then choose okay to confirm that and run
13:59my package. And again, it will run
14:01marginally more slowly because not only
14:03is it doing what the package does, it's
14:05also writing information out to a SQL
14:06server table. as to where that table is.
14:10Let's have a look.
14:12In object explorer, you've got your
14:14database called Xactor
14:16and within that you've got tables and
14:19within those you have system tables. And
14:21what SSIS will automatically do is
14:23create a new table called SIS SSIS log
14:26and put your logging information in
14:27that.
14:29And if you have a look at that table,
14:31you can see that it's got all sorts of
14:33wonderful information in there um to be
14:36investigated and researched.
14:39And that's how you log information to
14:41SQL Server.
14:45In addition to all the other ways of
14:46logging in information integration
14:48services such as using logging itself
14:50and using events, you can also use a
14:53nifty little transform called the audit
14:54transform. And this final part of the
14:56tutorial explains how this works. I've
14:59got a package which imports some Excel
15:01uh contestants and uses a union all
15:05transform to display the information.
15:06And I've got a data viewer which is
15:08going to show what's going down that
15:10pipeline. And you can see there's 109
15:12rows and you can see it's put in the
15:14columns coming from the Excel workbook.
15:17There's about six of them. What I'm now
15:19going to do is firstly to stop debugging
15:22and I'll get rid of this pipe. And what
15:26I'm going to do is put in an audit
15:28transform between the two. I'll connect
15:31all that up.
15:34So the information flows from the Excel
15:35workbook into the order transform and
15:37from there into the union all transform.
15:40But what the order transform will do
15:41when I configure it is add in additional
15:44columns.
15:45I'm going to choose to display the name
15:47of the package and I'm going to call
15:49that package name the default. And I'm
15:52also going to choose to display the name
15:54of the task and I'm going to call that
15:57name of task just to prove that I don't
16:00have to stick with the preset names. And
16:02there's all sorts of other information I
16:04could choose to display at the same
16:06time.
16:08If I now put a data viewer on this final
16:10stage, when I run this, I'll have two
16:12additional columns of information
16:14available to me. I'll have, yes, the
16:16columns from the original Excel
16:17workbook, but scrolling across to the
16:19right, I've also got the package name
16:21and the name of the task. At this point,
16:23I probably could have chosen more useful
16:25information because they're always the
16:27same.
16:29And that's what an ordered transform
16:31lets you do. And that completes this
16:33tutorial on logging and event handling
16:36in integration services. We hope you've
16:39enjoyed this tutorial on event handling
16:42and logging in SQL Server integration
16:44services. There's loads more training
16:46resources on SQL Server.net, Microsoft
16:49Office, and all sorts of other software
16:50applications at www.wisowl.co.uk.
16:55[Music]

About this transcript

This page contains the full transcript of SQL Server Integration Services (SSIS) Part 17 - Event-handling and logging by WiseOwlTutorials, generated from the public captions YouTube serves with the video. The transcript has 3,085 words across 450 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.