if you've created an ssis package odds are you're going to want to execute it at some
point so in this video we'll take a look at the various different ways that you can do that
so option number one is pretty straightforward it's executing your package directly in microsoft
visual studio this is where you've built your package and set a lot of parameters
and what you can do is actually just execute it right from here so if you go over to the side
under ssis packages you can right click on the one that you want and simply say execute package
and from here it's going to go through and run it and you'll see you're in this debugger mode
you can see by these dashed lines here that means you're in debugger mode it's running
and if you get any errors it'll let you know you can click this progress button here and
it'll show you all the individual steps that occurred so step by step through each
flow that you have here it'll show you what happened and so if you had any issues you
could see that here pretty clearly now the second way is actually going to take place directly in
sql server management studio so here we are i'm logged into my database server and if we go under
information services catalogs if you expand this you'll see the ssis db directory and this is what
was set up once you go back when you originally set up your integration services so if you haven't
done that yet you may not see this and we've set up our directory again this is based on what i
had built previously but if you have deployed any packages at some point you'll have this projects
demo etl packages we're still moving down and here is demo package so this is where i deployed
my package originally and again if this is something you've already done
it should be here if not check out some of the other videos on how to do that
and here what we can do is right click and execute if we had any parameters we could add these here
in this package we don't but this is where you could handle that here and add specific values
maybe you want to hard code something in here and override what maybe was deployed connection
manager this is going to again be taken from visual studio and take this in here so we have
our connection strings to a file to a database and anything you would have here would be
placed here and again you could override this if you wanted so i want to show you that and now
let's go ahead right click and execute and then we select ok and right here it says the operation has
started would you like to open overview report and we'll say yes here we can see this is the one that
we just ran this is the current time for me and here are the steps we can see everything succeeded
and no issues and if we want we can click into each of these individual steps to see a little
bit more about what happened so step by step every individual action that was taken behind the scenes
is logged here the time so there's a lot of good information go back let's check out this
one every individual step that the processor is actually taking we can view performance
so here i've actually run this in the past so there are some previous ones here but you could
see over time potentially how your performance is doing maybe it's for some reason getting much much
longer or having a huge spike somewhere you could see that here very quickly and then you know it's
on you if you want to go in and investigate why that might be or maybe it's a non-issue so that's
an example of how you could do this here through the information services catalogs in your ssis db
now option number three this will be more for automating and scheduling your packages to run
directly through a sql server agent job so if we open this up here sql server agent let's expand
jobs in a previous video we made one on processing a cube and now in this one we're going to create
a new job for process ssis package and it's the same format the same wizard here we're going to
add a step new and here we'll give it a step name so within a job if you're not familiar
you can add multiple steps all doing different things in this case
it's going to be one job with just one step and we'll just say run demo package
type this is not a sql script it is actually a package so in sql server integration services
package select that run as it's going to default to the sql agent service account and that's
something that could cause a little bit of an issue depending on what you're doing and i'll
point that out towards the end of the video but for now i'm all good so we'll keep this here and
which package so package source there's a couple options here depending on where you're actually
storing this package if you happen to be doing it somewhere else in our case we've deployed it to
the ssis db catalog here as we pointed out before so we can just select this and server this might
take a moment to load up so here's my server i'm going to select this one log on to the server as
the current user and this is where we're going to want to pick the package we want to run in this
case demo package so let's click these three dots we're going to follow the same path there it is
okay and now we've added this to this job click ok and one last thing to point out you could run this
on a schedule if you wanted we're not going to cover schedules in this video so here we have our
process ssis package maybe we should have renamed that to execute ssis package but you get the idea
and what we'll do here now is right click start job at step and it's going to run this job for
us it should be fairly quick hopefully no errors and all good success success awesome and just like
before where we saw those reports let's go back real quick and take a look if you right click on
on this here on the package go to reports standard reports all executions we should now see a new one
number four and this is the one that was triggered through our sql agent job and we can see this here
caller sql agent demo that's the name of the sql server agent in my case and we can see
again everything that we want here and have a nice little reporting breakdown of the execution
so i mentioned previously that if you're going to run something using a sql agent job there is
something you need to keep an eye on and really what i'm referring to here is this user right
here so for example in this case the package is trying to capture data from the sales target csv
file but initially when i ran this i actually had an error and we can see this here we can see this
failed and if we were to look at this failure on this step right here upload data i'm going to see
cannot open the data file access is denied and the reason that failed initially was because
that user that caller sql agent server user did not have access to this so it was trying to run
it through here and it couldn't get to it so to fix that i went to properties security and added
this guy right here and that was based off of this name right here i was able to add that in here
right here so just to show you actually why this is important i'm going to go ahead and remove this
now let's try to run this again through the job we should see this fail see failure and again let's
go back refresh here's the failure we should see the same error access is denied that caller there
is is the issue so to get the specific user of that you can go to your security logins and
here you can see nt service sql agent demo double click that and here you can easily copy and paste
it it's not so easy to copy from the other part so you can copy this here go back to your file
right click properties security edit add now i'm going to paste this name in here and if i do check
names it's going to identify that within the server as a user click ok make sure it has read
execute apply ok so now we've given this login user which is the user that the
job is going to use we've given that permissions and now let's try this again
and there it succeeded so that'll do it for this video just a quick summary of how you can run
your packages in multiple ways and if you found this helpful please feel free to like the video
leave a comment if you have any questions and i'll see you at the next one thanks for watching