In this video we're going to continue with our SSΙS package and we're going to deploy it to a
SQL server. Ιf you haven't seen the previous video where we create this SSIS package you
should pause this video and watch that one first. So in deploying the project the first
thing you need to make sure is that you've installed SSIS on your SQL server. There are
a few different ways to do this and if you'd like instructions on them should contact Microsoft or
find their documentation. For example this Microsoft document describes different ways
to install integration services. The way that we chose for these VMs is listed here.
In the features selection page under shared features select integration services. This is
done during the installation or the addition of features on the SQL server installation wizard.
Once that's done you should see under services SQL server integration services. Make sure this is
installed and that it is running. In the previous video we confirmed that this data flow is working.
So we'll go ahead and deploy it now. We'll go to project, deploy and click through the wizard.
We'll put in our SQL server and we'll browse for a path but we see that an integration services
catalog SSISDB was not found on the server. To deploy a project to this server you must create
this first. So from the integration services catalogs node we should create a catalog. Under
integration services catalog we'll right-click create catalog . Type in a password and click OK.
Now we see SSIS DB has been created. We'll go back to visual studio and retry on the
deployment. You see this one so create a new folder underneath NuGreen equipment export and
click OK. I'll click Next and deploy. I'll click close and now we're done with Visual
Studio. Everything up to this point was the development phase and now that we've created
our SSIS package and deployed it to a SQL server everything else can be done from the SQL server
itself. So close this out and we'll refresh this catalog. We'll expand through to the
projects in the packages and we'll right-click on this package and execute and then click OK.
The operation is started so we can look at the overview page if we want. Here we see the
current status is running, we've given a little bit of time so let's go ahead and refresh. Here
we see the status has changed to succeeded. We can confirm this by looking at our tag that we created
to track the progress. So open up the SQL file we created a few videos ago, we want the name of the
linked server. Here we see the success message from a few minutes ago when we just ran this
package. From this point forward in order to run this package all we need to do is open up SSMS,
connect to the SQL server, drill down and execute the package. However if you want this package to
run every day you can do this using the SQL server agent. Let's start by running the SQL
server agent service. In SSMS we should see the SQL server agent is now showing
a green arrow. Now let's right-click new job. We'll name this NuGreen data export daily job.
Under steps will click new, run SSIS package, SQL server integration services package run as SQL
server agent service account and we'll navigate to find the package itself. One thing of note here is
that we're running as the SQL server agent service account. This means that we need permission for
that account to access all the data that we need on this AF server, as well as writing to the tag
and the PI server. To find out what account this is open up services and navigate to the SQL server
agent service. Under logon as you can see what the account is. Make sure this account has the
appropriate permissions. Click OK and we'll click on schedules. Write a new schedule and name it
daily schedule. We'll set it to run in a few minutes starting today. Everything else should
be good so click OK. And I'll skip ahead to when this has already reached the trigger. Click on
job Activity Monitor and confirm that this is starting when it's supposed to. It's past
the time so let's go ahead and refresh and see that it worked. We hit refresh we see last run
outcome succeeded, last run 6:50 p.m. and for final confirmation we can look at the tracker
tag that we created earlier. Let's refresh this query and see the new success message.
Now that this SSIS package is deployed, confirmed to be working outside of Visual Studio and
scheduled to work as a scheduled task every day this concludes the video. Thanks for watching.