YT YouTube2Text

YouTube2Text → Transcripts → SQL Server Integration Services (SSIS) Part 6 - Expressions

SQL Server Integration Services (SSIS) Part 6 - Expressions — Transcript

by WiseOwlTutorials · 3,631 words · 509 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00welcome to this wise tutorial on using
0:02Expressions within a SQL Server
0:04integration
0:05Services here's what you'll learn in the
0:08tutorial we'll begin with a look at the
0:10example used for this tutorial then go
0:13straight into creating basic
0:15Expressions we'll then look at some more
0:17advanced Expressions before going on to
0:20look in more detail at the Turner
0:22operator or the question mark
0:24symbol we'll then look at how to create
0:26expression tasks in integration services
0:29and finally look at how to complete your
0:31project so that you can see it working
0:34so let's get
0:36started before we look at expressions in
0:39detail I thought it would be useful to
0:40see where I'm trying to get to we're
0:43aiming to create a package with two
0:45tasks one will be something called an
0:48expression task to work out a message I
0:50want to display on screen and then I'll
0:52actually use a script task to show that
0:55message so if I run this package you
0:58should see that it displays my required
1:00message which is whether it's a weekend
1:02or not and how many days there are till
1:05Christmas a rather strange example but
1:07it will be useful for showing the
1:09various ways in which you can use
1:10expressions in integration
1:15Services before creating some
1:17expressions in integration Services I
1:19thought I'd have a quick look at where
1:20the language fits into the Microsoft
1:22Lang programming language family you've
1:25got the existing languages in Microsoft
1:27you've got C SQL vbm VBA and there's
1:31also um an element of new programming
1:33within
1:35ssis so that's where ssis Expressions
1:38fit in I would say they're mostly based
1:41on C sharp or C there's an elements of
1:44SQL in there and there's new things
1:46which Microsoft programmers probably
1:48weren't have seen before what there
1:49isn't is any VB or
1:52VBA so with that as background let's
1:54have a look at creating our first
1:58variables and set expressions for their
2:01values you can create the variables
2:04window or make it appear by clicking on
2:06the little blue eraser symbol at the top
2:07right there and what I'm going to do is
2:09create two variables one of them is
2:12going to hold the current date and one
2:14of them is going to hold the current
2:16year and we'll use these later to
2:18calculate the Expressions saying whether
2:19it's a weekend or not and how many
2:21shopping days there are till Christmas
2:24so to create the first variable I can
2:25click on the blue eraser I'm going to
2:27call this one current date
2:30and because it's holding a a date I can
2:34just type in a d or I could have clicked
2:35on the drop arrow and chosen the data
2:37type from there now instead of typing a
2:40value in for this I want it to be an
2:41expression which always gives me today's
2:43date so I can click on the three dots to
2:46the right of which is called an
2:47Expression
2:48Builder and build up my expression from
2:52the list there's two parts there's a
2:55list of variables on the left hand side
2:57at the moment i' only I've only got the
2:58one and there's a list of functions on
3:01the right now I want to show today's
3:03date so it's a fair bet it's going to
3:05belong to datetime functions so if I
3:08expand that you can see that there's a
3:10fairly small number of date functions
3:13and the one I want is get date if I let
3:15my mouse linger over it it says it
3:17Returns the current data of the system
3:19and it also comes up with a
3:21description what does take a bit of
3:23getting used to is the fact that when I
3:24double click on that it doesn't put it
3:26into the expression at the bottom what I
3:29have to do instead is click on it and
3:30drag it into the expression for that to
3:33work so I've now got an expression and
3:36if I click on this button saying
3:37evaluate expression it will tell me
3:40whether it's valid and you can see I'm
3:41creating this on the 11th of November
3:442013 at 11:07 in the morning so there's
3:48a bit of um familiarity for
3:50you if you then choose okay I've created
3:53my first variable what I'm now going to
3:55do is create a second variable which is
3:57going to hold the current year
4:00so to do this I can choose to add a
4:02variable I'm going to call this one
4:05current year and it's going to hold an
4:07integer because it's a number of the
4:10year what I can then do as before is
4:13click on the Expression
4:14Builder again I'm looking for a date
4:16function fairly obviously and in this
4:19case I want one which will give me the
4:21year corresponding to a date so I can
4:23click on that drag it down into the
4:26expression now the bit in the middle
4:29there says that it expects a date to be
4:32filled in as the one argument to it but
4:35what I can do is click on get date uh
4:39sorry is go to the variables click on
4:42current date which holds today's date
4:45and I can drag it down over that
4:46argument now watch what happens when I
4:48get over it the argument is selected in
4:51its entirety and when I release the
4:53mouse button what it will do is replace
4:55that argument with the name of the
4:57variable you can see the name of the the
4:59variable is a bit long-winded it's got
5:01an at symbol then it's got user to
5:04signify it's a user variable and not a
5:06system one and then it's actually got
5:07the name of the
5:08variable so I can click and evaluate
5:11expression to see the value of this and
5:13it says I'm in
5:142013 and if I choose okay it will also
5:17show the value in Gray there so that's
5:20how you can create simple Expressions
5:23what we'll now do is go on to look at
5:24some more complicated
5:27ones what I now want to do is three new
5:30Expressions to give how many days there
5:32are from now until Christmas I'm going
5:34to do this in three stages to make it
5:36clearer what's going on and to show how
5:38to use expressions in
5:40ssis the first variable I'll create will
5:43give the date of Christmas Day for this
5:46year but as a string of text so will
5:49evaluate to the 25th of December
5:512013 but that will be a string of text
5:54and I'll use that format because I know
5:56that ssis can convert that to a date
5:58format
6:00what we'll then do is calculate what
6:02that is as a date so we'll convert it to
6:04a datetime format and finally I'll take
6:07the difference between today's date and
6:09that date to work out that at the time
6:11of writing there's 49 days till
6:14Christmas but I've hardcoded all of
6:16those three variables in what I need to
6:18do is create Expressions to calculate
6:20them according to the current date so
6:22let's start with the date of Christmas
6:25as a in text format so I'll create a new
6:28variable and I'm going to call it exus
6:31date text and it's going to be a string
6:35variable because it's going to hold a
6:37series of characters and if I click on
6:39the Expression Builder what I need to do
6:41then is build it
6:43up what I would like to do is this I'd
6:46like to take the current here the
6:48variable I've already calculated which
6:50will give me the
6:5120113 and what I'd like to do is
6:54concatenate that with the month and the
6:57day the concatenation symbol is a plus
7:00sign which will be familiar to anyone
7:02who writes in SQL or C but may give VB
7:05programmers cause for
7:08concern what I can then do is add
7:11on the month and the year so starting
7:14with the most General thing the year and
7:16going to the most specific thing the day
7:19the problem is if I evaluate that
7:21expression it will give me an error and
7:23what is saying is that the data types
7:25DTI 4 and DT wst are incompatible
7:30and that's because I'm trying to
7:31concatenate or join together two things
7:33one of which is text but the other of
7:35which is an integer an ssis is extremely
7:39fussy about its data types so what I
7:42need to do is convert this first thing
7:44to text and I can do that by looking at
7:48the typ cast functions casting is just a
7:52posh word for converting data types so
7:55if I expand that you can see for every
7:57single data type in ssis there's a
8:00corresponding conversion function the
8:02one I want is something which will take
8:04this and turn it into text and I've got
8:06two choices dtor St or dtore
8:12wstr I'm going to go for the second
8:14choice because it's simpler with the
8:17first one I'd have to specify the code
8:19page whereas with the second one I can
8:21just specify how long the text is or
8:24rather should be so I can drag that
8:27before the expression and change the
8:29length
8:31to be four characters because that's how
8:33many characters are in a string of text
8:35and when I evaluate that it will give me
8:37the data's
8:39text for VB programmers and indeed for
8:42SQL ones it may seem strange that you
8:44put the conversion function before the
8:46variable you're
8:47converting but as my daughter would say
8:50you'll have to cope with that um it's
8:52just how it works in ssis and also how
8:55it works in C by the
8:57way so we choose okay to confirm that
9:00and what I'm now going to do is create a
9:02second expression to give the date of
9:05Christmas so to do that I can add
9:07another expression I'll call it xmus
9:10date it's going to be a datetime
9:13expression and this time all I want to
9:15do is take the text of the Christmas
9:17date and convert it into a datetime
9:20format so again I'm looking for a type
9:22Cass function and the one I'm going to
9:24choose is dtore DB time stamp which will
9:29convert a string of text into a datetime
9:31format so I can drag that into the
9:34Expression
9:35Builder and then I can put in the thing
9:37I'm actually trying to convert which is
9:40the xmus data's
9:43text and if I click on evaluate
9:45expression what it will do is give me
9:47the date in a date format which is what
9:50I wanted and because I haven't specified
9:52a time it assumes it's midnight just
9:55when children are starting to open their
9:56presentence perhaps so if I choose okay
9:59to confirm that I've got one more
10:01variable to create in this session and
10:03that will give me how many days there
10:04are till Christmas so I can add another
10:07variable I'm going to call this one xmus
10:10days and it's going to be an
10:13integer and to calculate this I need to
10:16work out the difference between two
10:18dates one of them is today's date which
10:21is held in a variable called current
10:24date and one of them is the date of
10:26Christmas
10:27day I could do this by subtracting one
10:30from the other but there's a posher
10:32better function in ssis which is the
10:35date diff function the difference
10:37between data ad and date diff is a data
10:40ad takes a date and adds on a given
10:41number of days weeks months quarters
10:43years whatever it may be whereas date
10:46diff takes the difference between two
10:48dates and you can see it says that in
10:50the
10:51description so I can drag that in I'll
10:55come back to the first argument at the
10:56end for the moment let's choose the
10:58start date and the end date the start
11:01date is today's date so I can drag that
11:04over the start date and the end date is
11:07a date of Christmas Day
11:09itself now the question is taking the
11:12difference between those two dates do I
11:14want to say how many months there are
11:15between them how many quarters how many
11:17years how many weekdays or in this case
11:19how many days and the only practical way
11:22I can think of finding out what you
11:23should type in in this um argument is to
11:27go on Google or use help but I've done
11:31that and I know I want to take how many
11:32days there are so I can evaluate that
11:35expression and it will give me 49 the
11:38number of days between the two dates so
11:41I can then choose okay to confirm that
11:43and that completes my three
11:47Expressions the last variable we're
11:49going to create before we get into
11:51creating expression tasks is want to say
11:53whether it's a weekend or not so I've
11:56created an example here which will say
11:58it's a week weekend I'm actually
12:00recording this on a Wednesday so in fact
12:03when I create my expression it will say
12:04the opposite it will say that it's a
12:07weekday the to do this we're going to
12:09use something called the Turner operator
12:12which involves writing a condition then
12:15putting a question mark then putting
12:17what will happen if the condition turns
12:18out to be true then putting a colon and
12:22then putting what uh you what will
12:23happen if the condition turns out to be
12:26false VB and probably access other
12:29programmers will recognize this as the
12:32if or the iif function but there's no
12:35function like that in ssis so instead
12:38you have to use a thing called a turn
12:39Turner
12:41operator so let's have a go of creating
12:43this expression so I'll choose to create
12:45a new
12:46variable excuse me and I'll call it
12:49weekend
12:52status and it's going to be a string
12:55variable and is going to have an
12:58expression so the first thing I'm going
13:00to do is to put in my condition which is
13:03in this case whether the weekday is a
13:07Saturday or a
13:08Sunday now the way I'm going to do that
13:10is having search for a weekday function
13:13here and not found it I'm going to do
13:15the next best thing and use the date
13:17part function which gives you an integer
13:20representing a date part of a date now
13:22that date part could be the day number
13:24the month number the week number the
13:26year number or as in this case the week
13:29day number so if I drag that
13:31in the date I want to use is the current
13:35date so that's nice and easy for the
13:37date part it's back to Google or back to
13:40help again in this case what I actually
13:42want to type in is DW in quotation marks
13:46which will give me the weekday number
13:48and by default weekdays start from uh
13:52Monday so in fact because it's a
13:54Wednesday that will give me the number
13:56three so what that's my stter my
13:59condition what I now want to say is
14:01whether that equals 6 or seven six being
14:06Saturday 7 being Sunday and to do that I
14:10can use an
14:12operator and my operator in this case is
14:15equals and I can drag that in now I
14:18could of course have typed that in much
14:20more easily but I was doing it to
14:21emphasize the fact that in ssis unlike
14:24in some other languages you have to put
14:26the equal sign twice in a condition
14:29so if that's
14:31six I would now want to say or and again
14:34if you're having problems remembering
14:35the syntax the easiest thing to do is go
14:37to the list of operators and you can see
14:39that and is written as Ampersand
14:41Ampersand and or is written as two
14:43vertical bars one after the other so I
14:46could either type that in or drag it
14:48in so either that equals six or that
14:53equals seven and what I'm going to do is
14:55indulge in a bit of copying and pasting
14:57to save myself typing
14:59and there's my
15:02condition what I'm now going to do is
15:04say if that's true then what I want to
15:07do is put in a message saying it's a
15:09weekend and I've put in some return
15:12codes to make my expression easier to
15:13read they'll be ignored by
15:16ssis so if that's true I want to say
15:19it's the
15:21weekend and I think that deserves an
15:23exclamation
15:24mark otherwise I'm going to put a
15:27message saying
15:29it's a
15:31weekday and if I evaluate that
15:33expression it will tell me it's a
15:35weekday because as I've mentioned I'm
15:37recording this on a
15:38Wednesday so I can choose okay to
15:40confirm that and that is how to create a
15:43Turner operator so just as a refresher
15:46there you put in the condition excuse me
15:49you put the condition you put a question
15:51mark you put what happens if that's true
15:53then you put a colon and then you put
15:55what happens if that isn't true
16:00for the final part of this tutorial
16:02we're going to create and display the
16:04message saying whether it's a weekend or
16:06not and how many days or are till
16:08Christmas as a first step I'll create a
16:11variable which I'll call message to hold
16:13a string of text now what I could do is
16:16use expression Builders before to build
16:17up this message but by way of variation
16:21I'm going to instead use an expression
16:23task now if you can't see this it's
16:25because you're not using the latest
16:26version of SQL Server it's quite a new
16:28new addition so I can add that task into
16:31my control flow window and perhaps
16:34change the name of it so it does says
16:35something more sensible what it's going
16:37to do is calculate the message to be
16:39displayed so I'll call it calculate the
16:43message as with any other task I can
16:45then double click on the icon to the
16:46left of it to configure how it's going
16:48to work and what it's going to do is set
16:50the value of my message equal to
16:54something now the first thing it's going
16:56to do is put in the weekend status so I
16:58can just drag that into the window and
17:00that will give me the bit which says
17:02whether it's a weekend or
17:03weekday what I can then do is
17:05concatenate something with that and what
17:08I now want to do is put in two Carriage
17:11returns so that there's a blank line
17:13between my first message and my second
17:15one in uh VBA you would do that by
17:18typing vbcrlf in other languages you
17:21would use YY characters 13 and 10 in
17:24ssis and indeed in C you put in a back
17:28slash n for each carriage return you
17:31want to
17:32include I can then have the words there
17:36are and what I now want to do is put in
17:39how many days there are till Christmas
17:42but it's no good just using this
17:43variable because that's an integer and I
17:45want it to be incorporated within a
17:46string of text so what I'm going to do
17:49first is to convert it to
17:52text I would have thought a maximum
17:54length of three would be sufficient
17:57because there can be most three 65 days
17:59till Christmas and then I can drag my
18:02variable
18:03in so I've now got there R followed by a
18:06number what I want to do to complete my
18:08message is just to type
18:10in days till
18:14Christmas and if I evaluate that
18:16expression it would tell me it's a
18:18weekday but if I scroll down you can see
18:21underneath that it's also got the
18:23message saying how many days are till
18:25Christmas so I can choose okay to
18:27confirm that and that completes my
18:30expression
18:32task what I'm now going to do very
18:34briefly is to explain how to display
18:37that message on screen um because this
18:39is all a refresher of things we've done
18:41in previous tutorials so the first thing
18:44I want to do is TR add a script task and
18:46then I need to make sure that it follows
18:48on directly from the calculation of the
18:50message I can double click on the script
18:52task to configure it and specify that I
18:54can pass the value of my message
18:56variable as a readon varable able into
18:59the task and then I can go straight into
19:02editing the script using the default
19:03language which is
19:06C the first and only thing it's going to
19:08do is display a message so I can type in
19:11messagebox doow and the message it will
19:14display is just a value of the variable
19:16which is part of the variables
19:18collection it's called message I need to
19:20be careful to type that in in the
19:22correct case and it's actually the value
19:24of the variable I want to display that's
19:27returned as an object so what I need to
19:29do is convert that to a string of text
19:31to make sure that it displays correctly
19:34and that's got rid of all the red
19:35underlinings so I'm pretty confident
19:37that's going to work so I can then close
19:40that down that project to return me back
19:41to my original one and choose
19:43okay and if I've done everything
19:45correctly when I play this I should get
19:47the message saying it's a weekday and
19:50there are 49 days till Christmas just
19:52time to do my shopping I hope you've
19:54enjoyed this tutorial on integration
19:56Services Expressions you can find loads
19:59more training resources on integration
20:00Services other aspects of SQL server and
20:03net and Microsoft Office at
20:06www.wis al.uk

About this transcript

This page contains the full transcript of SQL Server Integration Services (SSIS) Part 6 - Expressions by WiseOwlTutorials, generated from the public captions YouTube serves with the video. The transcript has 3,631 words across 509 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.