YT YouTube2Text

YouTube2Text → Transcripts → SQL Server Integration Services (SSIS) Part 13 - Script Tasks using C#

SQL Server Integration Services (SSIS) Part 13 - Script Tasks using C# — Transcript

by WiseOwlTutorials · 2,942 words · 437 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00welcome to this wisal tutorial on
0:02creating script tasks using the C
0:04programming language within SQL Server
0:06integration Services here's what you'll
0:08learn in the
0:09tutorial we'll Begin by looking at the
0:11two types of script within ssis script
0:14tasks and script components and then
0:17we'll introduce an overview of what
0:18we're trying to
0:19achieve we'll then look at actually
0:21writing script looking at basic script
0:24tasks looking at using the variable
0:26dispenser to get at variables within our
0:28script and looking at simpler method to
0:30access variables which is to pass them
0:32into script tasks as readon or read
0:34write
0:35variables finally we're look at how to
0:37finish the package by joining all the
0:39script tasks up together and testing it
0:41to check it works so let's get
0:44[Music]
0:45started before we begin looking at
0:48script tasks in the control flow let's
0:50just have a look at the possible ways of
0:52writing script within integration
0:54Services there's basically two as I said
0:57in control flow you can create a script
0:59task
1:00and what this will do is firstly manage
1:02the the order of execution of tasks
1:04within your control flow and it's also
1:06handy for reading in and setting the
1:08value of variables and that's what we'll
1:10be covering in this
1:12tutorial you can also separately create
1:15script components in your data flow and
1:19a script component as this dialog box
1:21suggests allows you to say where your
1:23data is coming from the source where
1:25it's going to the destination or by far
1:28the most commonly it will do trans
1:29informations on the data so it'll
1:31massage it as it flows through your data
1:33flow and that'll be covered in a
1:35separate tutorial but for now let's go
1:38on and look at what we're trying to
1:39achieve in this
1:41[Music]
1:42package so what's our package going to
1:45do well I've got an Excel workbook of
1:48those familiar xfactor contestants UK
1:50version again and I've got two
1:52worksheets I've got a mentors worksheet
1:54which contains 10 mentors and I've got a
1:57finist one which contains
2:00109
2:02finalists add those two numbers together
2:04and you get 119 uh Records in total
2:08remember that number if you could
2:10please in SQL Server I've got the same
2:13thing I've got a table of finalists
2:15there's 108 rows in that and I've got a
2:18table of mentors and there's nine rows
2:21in that if you add those two numbers
2:23together you get 117 not 119 remember
2:27that number too if you could
2:28please what's all this got to do with
2:30integration
2:32Services well here's the package I want
2:34to create it looks a great deal more
2:36complicated than it actually is what
2:38it's going to do is ask an initial
2:40question saying do I want to get my
2:42information from Excel or not if I do
2:45then what it will do is reading the two
2:46XL worksheets using use a row number
2:49data flow task to store those
2:52information in
2:53variables add the two variables together
2:56and show the results so I'll get a
2:58message saying there's 100 19 rows in
3:01total if on the first question I say I
3:04don't want to get my information from
3:06Excel I'll go down the failure route and
3:08in that case I'll decide to read the
3:10information in from SQL Server tables
3:13I'll get 117 as a result and that will
3:15be displayed in the final
3:17message so let's see this actually
3:19working in practice before I continue I
3:22must close down my Excel workbook
3:24otherwise it won't
3:25work so if I started off first time
3:28around I'm going to choose get my
3:30information from Excel so I'll choose
3:32yes I go down the left hand path and I
3:34get a message saying there's 119
3:36finalists and
3:38mentors I'll now run that again but this
3:41time I'll go down the right hand path
3:43and I'll choose no I don't want Excel in
3:46this case I get the message saying
3:47there's 117 finalists and
3:49mentors so I can choose okay don't be
3:52put off by the failure symbol there
3:55that's intentional that makes me go down
3:57the SQL Server path so first what my
4:00package is going to do I'm not in any
4:02way claiming it's the most efficient way
4:04to achieve this but it does illustrate
4:06how script scripting Works in script
4:09tasks in the control
4:11flow so what we'll now do is go on to
4:14the next stage which is to show how to
4:16write basic script
4:19tasks so let's have a look at how to
4:21create our first script task in
4:23integration Services which will be to
4:25ask a question saying do you want to use
4:27Excel or not I've created a new package
4:29P Ag and I'm going to ask I'm going to
4:32create a script task and I'll say use
4:35Excel as a name of it to attach script
4:39to it as always I can double click on it
4:42I'm actually going to use the language C
4:44by default throughout this tututorial
4:45there's a separate tutorial if you're a
4:47VB
4:48person and I'm going to change the name
4:51of my default procedure to run to make
4:54choice just to show it's possible but I
4:56won't at this stage pass any variables
4:58in so if I edit that script what will
5:02happen is it will create a separate
5:03project in Visual Studio tools for
5:05applications and what I can now do
5:07within that firstly is to get rid of the
5:10horrible comments a personal view of
5:13that and then I'm going to have to
5:15rename my entry level procedure to make
5:17choice because that's what I chose to
5:19call it so I've now got a stub of a
5:23procedure which will run when that
5:25script task
5:26runs now I've got some code in my
5:30clipboard which I'm going to paste in at
5:31this point saying what it's going to
5:33do so it's going to display a message
5:36box the message box will have the prompt
5:38saying choose Excel it will have a
5:41caption or a title saying Choice it will
5:43have a yes and a no button it will have
5:46a question mark symbol and by default if
5:49I press return it's the second button
5:51button two which will be selected so
5:53I'll choose SQL
5:54Server what I'm then going to do is look
5:57at the value of the answer so to see
5:59whether I chose yes or no and if I chose
6:02yes what I'm going to do is return
6:04success from this procedure and if I
6:07chose no I'm going to return
6:10failure to make it easier Microsoft
6:12thoughtfully add in every single script
6:14task this enumeration it's called Script
6:17results and it means that instead of
6:19having to type microsoft. SQL server.
6:21DTS runtime. DTS exact result. suuccess
6:24I can just type script results. suuccess
6:27as a shorthand so that's what I'm going
6:29to to do so what I'm going to do in the
6:32yes part of my Clause is say that the
6:37task result
6:40equals script results.
6:44success and if I leave it like that it
6:47will give an error and the reason for
6:49that is because I need to convert the
6:51type of it if you look at task result
6:53can you see that the data type of that
6:55is int so what I need to do is perform a
6:58data type version to make sure that
7:01apples and apples are being
7:03compared so that will return success if
7:05I choose Excel IF on the other hand I
7:08choose SQL
7:10Server I want to choose a failure or
7:12return
7:13failure and that completes my script I
7:16think what I can now do is try it out if
7:19I close this down it will automatically
7:20save it and when I choose okay it will
7:22build it as well to make sure it makes
7:24sense and the fact that it's not coming
7:26up with any errors at that point is a
7:28very good sign
7:30so if I want now want to run this
7:31package I'll just make sure it's my
7:33default before I
7:35begin I can choose either yes or no if I
7:38choose yes it goes down this it will go
7:41down the Excel path if I choose no it
7:44will go down the SQL Server path and
7:46I'll see a failure so that's all looking
7:48very good so far what I'll now do is go
7:51on to look at using variables in two
7:55different
7:58ways I've got a a second version of our
8:01tutorial as you can see I've added two
8:03sequence containers one for two tasks to
8:06import data from Excel and one for two
8:08tars to import data from SQL
8:10Server to see how these work I've set up
8:13two connection managers one to my SQL
8:15Server database and one to my Excel
8:17workbook what I've then done is within
8:20my sequence container for Excel I've
8:22added two data flow tasks the first one
8:25we'll read the mentors in from the Excel
8:27worksheet of called mentors and then it
8:29will use a row count task to assign that
8:33to the variable called number
8:35mentors the other data flow task does a
8:38similar thing but this time it stores a
8:40number of finalists in a variable called
8:42number
8:43finalists and the SQL Server task do
8:45exactly the same thing but in this case
8:47they take their table their data from
8:49the table of mentors in SQL Server
8:51rather than an Excel
8:53worksheet and the fin list takes its
8:55data from the table of fin list and SQL
8:57Server
8:59so I've got three variables created I've
9:01got number finalists which will hold the
9:03number of rows in the finalist table or
9:05worksheet number mentors which will do
9:07the equivalent for the number me for the
9:09mentor worksheet or table and what I
9:11need to do is calculate the number total
9:13which is the sum of the first
9:15two as it happens by far the easiest way
9:18to do this is with an expression task
9:20but to make life more complicated and to
9:22show how script tasks work what I'm
9:25going to do is add one of those down
9:26here and use it to calculate the result
9:30[Music]
9:32so I'll calculate the total
9:35there I'll connect all my tasks up later
9:37on in this tutorial for the moment let's
9:39just edit
9:41this I could and probably normally would
9:44at this point pass in the two readon
9:47variables I want to work with number
9:48finalists and number mentors and pass in
9:51the read right variable called uh total
9:54numbers uh which I want to
9:57set but I'm actually going to make
9:58things that bit harder and use something
10:00called a variable dispenser just to show
10:03how this works I have to say I'm not a
10:05big fan of this but if somebody may find
10:07it
10:08useful so in my main main procedure
10:11which I've left called main on this
10:13occasion I've again got some code in my
10:17clipboard what this does is create a
10:20collection of variables which is
10:22initially
10:24empty it then uses something called the
10:26variable dispenser and looks for reading
10:29two variables number mentors and number
10:31finalists so nothing else can change
10:34them it then locks for writing the
10:37number total
10:38[Music]
10:39variable what it then does is call makes
10:42a call to the get variables
10:44method and what that does is add the
10:47variables which I've locked into my
10:49variables collection I need to use the
10:51word ref because I'm passing the
10:52argument by ref so that I can get some
10:54information out of the method as well as
10:56passing me information into it
10:59what I then do is use the two variables
11:02I've added to my variables collection
11:06get their values and read them into in
11:08integer variables called number mentors
11:09and number
11:10finalists and finally I set the value of
11:14my number total
11:16variable on this occasion rather
11:18dangerously I've done this by index
11:20number it's the third item in the
11:22collection which is why it's got number
11:24two as the index number it might have
11:26been safer to use number total instead
11:30and you access it by
11:32name what I then have to do is unlock my
11:35variables collection and finally say
11:37that the whole thing has been a
11:39success so that's how you can use a
11:41variable dispenser what we're now going
11:43to do is show an easier way of creating
11:45script using by passing in variable
11:48values instead which I think is what I'd
11:51actually
11:53recommend so the final bit of our jigsaw
11:56before we link all the pieces together
11:58is to write write a script to display
12:00the total the value in fact in the
12:03variable called number total now I could
12:06have done that in the previous script
12:08but I wanted to show it in a separate
12:09one to give me an opportunity to show
12:11passing variable values which is
12:12actually something we've done in quite a
12:14few previous tutorials in this
12:16series so I'll add a script task in
12:18there and what it will do is display the
12:21total so that's what I'll call
12:24it and then we can double click on it to
12:27edit it what I'm going to do in order to
12:29be able to access a variable called
12:31number total in my script is I'm going
12:33to pass it as a readon variable so
12:36there's all my system variables being
12:37listed and there in the middle is number
12:41total it's a readon variable because I
12:43only want to find out what the value is
12:45I don't want in any way to change it so
12:48now I can edit my
12:51script
12:52and in the main task I've got some code
12:55in my clipboard I'm just going to paste
12:56that in by right clicking and pasting
13:00and what it will do is create a variable
13:01called number total and set it equal to
13:04the value of the variable called number
13:07total so it's much easier I think to
13:10pass in the variable and then access it
13:13as part of the collection of variables
13:15um within uh DTS within
13:18ssis now that I've got that variable I
13:21can just display the value in a message
13:22I need to convert it to a string first
13:24before I can do that and then I'll spit
13:26out the fact that the whole thing has
13:27been a success
13:29so that completes my script what I can
13:31now do is to exit that just before I do
13:35that here's what would happen if I had
13:36an error me error in my script so I've
13:39missed out the semicolon which I fairly
13:41often do in
13:42C if I now don't notice that red
13:46underlining and try exiting this and
13:48then choose okay it will tell me that
13:51I've got compilation errors in my
13:52package do I want to save my changes and
13:55at this point the thing I'll be most
13:56likely to do is choose no go back into
13:59my script and correct the change and I
14:02can use the error list window to find
14:05the
14:06error so if I reinstate my error and go
14:09to my error list I can double click on
14:12that and it will highlight where the
14:13error
14:13[Music]
14:16is but I'm going to hopefully write a
14:18perfect script and all I now need to do
14:21is link all these pars together by
14:22setting some
14:23[Music]
14:25constraints so we'll be covering
14:28constraints in a later tutorial in the
14:30series but for now let's have a quick
14:32look at what we need to do to get this
14:33package to work the first thing to do is
14:36to say connect up the first script task
14:39with this import from Excel bit but I
14:42also need to connect it up to the SQL
14:44Server
14:45bit that's not going to work if I choose
14:49yes to use Excel it will return a
14:50success from that script and everything
14:52will be fine but it will execute both
14:54the two packages so what I'm going to do
14:56is right click on this second constraint
14:58and change it into a failure
15:00one and what will happen now is if I
15:03choose no from the first error message
15:05I'll go down the failure route and I'll
15:07import my data from SQL
15:08Server now I need to address what
15:10happens after importing the data and to
15:13do that I'm again going to connect up
15:14the Excel sequence container up to the
15:17calculate total
15:19script and I'm also going to connect the
15:21SQL
15:22one as things stand this script will
15:25never run because it can only run if
15:28both of the these two sequence
15:29containers both complete successfully
15:31and that can never happen so what I'm
15:34going to do is right click on each or
15:36one of these and choose edit and change
15:40it so it's a logical all so in this case
15:43any one constraint leading into the task
15:45must evaluate to True which is exactly
15:47what I want if I then choose okay both
15:51the two green lines show in dotted as
15:54dotted lines to show that any one of
15:55them can be true and I can now just link
15:58up the final two
15:59tasks to finish my
16:02package I'll just make sure that that's
16:04my um startup object and if I now run
16:08this to test it out it will work whether
16:10I choose
16:11[Music]
16:13Excel
16:15or if I
16:17[Music]
16:19don't and that completes this tutorial
16:22you can find loads more training
16:23resources on SQL Server integration
16:25Services reporting Services analysis
16:28services is.net and Microsoft Office at
16:32www.wis our.co.th
16:34[Music]

About this transcript

This page contains the full transcript of SQL Server Integration Services (SSIS) Part 13 - Script Tasks using C# by WiseOwlTutorials, generated from the public captions YouTube serves with the video. The transcript has 2,942 words across 437 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.