In this video we're going to create the SSIS package in visual studio that will take data
from the AF server, shape it and store it in an external SQL database. Before we get started
let's create the database in the SQL server that we'll be using to store this data. We'll
open up SSMS and connect to our SQL server. On your databases let's right-click and create new
database. And let's name it NuGreen equipment. Click OK to create a database. We'll refresh to
confirm that it shows up. Great, now to open visual studio and create a new project. File,
new, project and under business intelligence templates select integration services. If business
intelligence is not showing up in your templates list then you'll need to install SQL server data
tools. I order to get the SSDT added for visual studio you'll need to open a browser and search
for SQL server data tools for your version of Visual Studio. For your convenience on these
course virtual machines have included the SSDT setup.exe. If you run it shows that it can install
both SQL server reporting services and SQL server integration services. Note this is just to get the
templates to appear in Visual Studio so that you can build the packages. To actually deploy which
we'll do in later videos you'll need to install the same thing during the SQL server installation.
So now we return to visual studio. Name our project NuGreen data export. We'll select
integration services project and click OK. In the package window we can see control flow and data
flow as well as SSIS toolbox. For this package these are going to be the important ones. We'll
click on SSIS toolbox and we'll grab data flow task and drag it on to the control flow. As it
set up now this data flow task is going to execute and that'll be the end of the package. Within the
data flow let's configure it to grab data from the PI System and move it into the SQL server.
Double-click on it and now we see we're in the data flow task. From our SSIS toolbox let's grab
OLEDB source and double click on it to create the connection. We don't already have an OLEDB
connection manager so let's create a new one. We'll select a PI SQL client in the drop down
put in NuGreen as our server name and for the location PISRV01. We'll use Windows integrated
security and have an initial catalog of guided project. Test the connection and click OK. For
data access mode let's use SQL command and jump over to PI SQL commander so we can see what the
view is named that we created in the first video. We'll right clicked on boiler list and execute,
and we see that these are the 15-minute time step values for all the boilers and
all the plants in our NuGreen database. If we remove the top 100 filter we can see all
the rows that are returned. So let's copy this query, let's paste it into the command text.
There's no editing that needs to be done because we're executing this against the
RTQP query engine. Therefore whatever was typed into the SQL commander can be used in this box.
We'll click preview to confirm that everything looks good. Great we can see the first 200 rows
and everything looks good. Just click OK to return to the data flow. Now this is
configured let's go back to the SSIS toolbox and let's grab a destination assistant. We'll select
SQL server and new and click OK. We're going to write this to the SQL server in that database
that we just created so we're going to keep the provider as SQL server Native Client and we're
going to enter the name of the SQL server into the box. Write in the name of the database and
click test connection. We'll click OK. Now we have an OLEDB destination that we can connect
from our OLEDB source. Now when this executes this will pass directly to this. Let's double
click to open up the destination configuration. Here it's asking us for the name of the table
where we're going to store the data. We don't have that table already so let's click new to
have it create the table for us. The create table syntax was built based off of the columns that
were already mapped from the source because it knows that this is what's going to be passed
to the destination. In order for it to write to the values to the table it knows already
what the names of the columns should be as well as our data type. So we'll give this a
name of boiler because this is going to be all of our boiler information and we'll click OK.
This command was just executed and therefore if we open up SQL server
management studio we should be able to see the new table having been created,
dbo.boiler with columns as described in the configuration. Now let's return to visual
studio. Let's confirm that the mappings were done correctly and all the destination columns match
what you would expect from the input columns. In this case everything is fairly trivial but
in a more complicated example these may need to be adjusted and click OK. I will return
to the control flow. This data flow task will copy the boiler data from the PI System into
the external SQL database. We want to do this three more times for the other sets of data. So
we'll copy and paste it three times. We'll rename these so that we can follow which ones they are.
Now we'll go into each one and we'll create the different tables on the SQL server as
well as changing up the views that we're using on the OLEDB source side. Click okay,
we'll go into the destination we'll create new and type in here and click okay we'll do this
two more times. Let's refresh our tables in SSMS and confirm that everything is there.
Great now we see the four tables that are going to eventually have data and we can confirm if we
want that there's nothing in there now. Zero rows confirms the tables are currently empty. Now let's
connect all of these so that they pass one to the other and finally let's add an execute SQL task so
that when everything is done we'll get a success message written to our SSIS package tracker tag.
We'll double click on execute SQL task and we'll configure it to access our SQL server
so that we can utilize the linked server that we created earlier. The connection is just to the SQL
server and the command itself will have listed the linked server. So we'll select PSRV01. For
the SQL statement let's go back to SSMS and grab the query that writes the value to the PI tag that
we created earlier. That was an insert query with an open query wrapped around it. If you
haven't already watched the video on the linked servers where we created this query I recommend
you pause this video and watch that one first. Let's execute this query to confirm that it still
works and now let's execute this one to show that the value we just wrote does in fact show up.
Here we see the value that we just wrote, so let's grab this query and put it into our SQL statement
in the configuration. We'll change the word test to success and click OK. And we'll connect pump
data export to write back to PI server. Finally let's copy this one and paste it over here and
rename this to failure. Let's go into the query and let's change this from success to failure,
click OK. And now we'll build connections from each of these data exports to this writeback
to PI server failure such that if any of these tasks actually fail instead of writing success
we write failure. So draw lines like you did before where all of them are connecting to
this task. I'll right click on each line and select failure. Next we'll go into properties
to make sure that any of these failures are treated as an or condition instead of an and.
Great, what this means now is that first this task will execute if it succeeds this task will
be executed if it fails this one will. This task will write a failure message to the PI
tag. This process is repeated for the boiler dead export to the heater data export to the
compressor data export to the pump data export all the way down to the writeback to the PI
server which is success. Great, now let's execute the package to make sure that everything works.
We see the sequence of green checkmarks as it went from one data export to the next as well
as the fact that this writeback to PI was used and this writeback to PI server failure was
never used. Let's confirm in SSMS that the data was put into the correct tables. Here
we see the values that we were interested in and we can repeat this for the other tables if
we would like. And we can also see the value of our tag showing success. Now in order to induce
a failure and confirm that the writeback to PI server failure route is kicked off
when it should I'm going to start the package and then stop the RTQP query engine service.
So open up services, and I'll navigate to PI SQL data access server RTQP engine
and I'll have it available where all I have to do is click stop and it will break the package.
So run the package and then stop the service. Here we see the boiler data export has an X
over it because it could not complete and the writeback to PI server failure box has
a green check showing that not only did it execute but it executed successfully. Now
let's go back and view the data for our tag. Here we see the status of failure. Great our
SSIS package is working the way we want and we're getting the data from the PI System shaped into
15-minute intervals and stored in an external SQL database. We have the tracker tag that is
capable of writing success or failure depending on the status of the SSIS package execution.
This concludes the video for building the SSIS package and in the next video
we'll learn how to deploy it to a SQL server.