[synthesizer music]
>>Hi there, I'm Michael Otey,
SolarWinds contributor and president of TECA, INC.
And today we're going to talk about SQL Sever on Linux.
This is part of the Jump Start series,
and now we're going to talk about
SQL Server Integration Services on Linux.
SQL Server Integration Services, it is a powerful ETL,
Extract Transfer Load tool that's been around
SQL Server since SQL Server 2005.
It is a great tool for moving data
between different systems, that includes multiple
SQL Server systems as well as SQL Server and Oracle,
SQL Server and MySQL, SQL Server and DB2.
It is a great cross-platform tool, and it also,
in addition to just moving data,
it can perform sophisticated data transformations,
and have complex logic, as well as perform
checking to make sure that your data types are right,
and even restarts for your packages if they go wrong.
One of the great additions with SQL Server 2017,
though, is the new addition of
SQL Server Integration Services on Linux.
And in this presentation we're going to talk about
some of its requirements, some of the limitations.
It is an initial release so
it doesn't have all the features.
Then we're going to quickly run through
how you can install it on Linux,
and then how you can run SSIS packages on Linux.
So what are some of the requirements?
Well, first, this was released in CTP2.1,
and the current release is RC2,
so it's in both of those and it will be
scheduled in the final release.
At this point, it is supported
on the Ubuntu Linux 16.04 platform,
and Red Hat Enterprise Linux 7.2.
So what are some of the limitations?
And there are quite a few.
First of all, on Linux, or SSIS, is for execution only.
The designer, which is SQL Server Data Tools,
that still only runs on Windows;
there isn't a Linux implementation on that.
So you essentially design your packages in Windows,
and then you can move your packages over to your Linux
SQL Server systems, usually by a file share or Samba,
and then execute them from there.
Right now there is no support for the SSIS catalog database,
you have to run your packages out of the file system.
There's no support for scheduling packages with SQL Agent.
There isn't support for Windows Authentication
or any third-party components.
Change data capture isn't supported,
neither is SSIS Scale Out, or the Azure Pack,
or support for Hadoop, or the HDFS file system.
And there's also no support for
the Microsoft Connector for SAP.
So there are a number of limitations, but still,
for basic data transform, and data transfer,
it will work great.
So now, let's talk a little bit
about the installation process.
Here's the list of commands that you basically
run to do this, and this is all documented
in the article that accompanies this video, as well.
But the first two commands, the curl packages
and the curl packages down here,
basically update the Ubuntu registry
with a Microsoft installation GPG listings.
And then, to actually perform the install,
you would use sudo, which is your super user command,
and say app-get update, which is going to
update your registries, and then say,
super user app-get install mssql-server-is.
And that will make sure that you install
a SQL Server Integration Services into your current system.
When I did this, I did have an error on the first command,
but it didn't seem to interfere in anything,
and the rest of the process worked fine.
Once the installation had completed,
then you need to perform a configuration for it,
and to do that, you basically, again,
through the Bash shell run, super user do,
and this time you're going to run ssis-config setup.
And this is going to display the screen that you see here.
It first asks you, do you want to accept the license terms?
And you have to say yes in order to proceed.
Then it asks you what edition you're going to use,
and you can choose between Evaluation, Developer, Express,
the Web Edition Standard, or Enterprise,
or I bought another license through
the retail channel and I have a product key.
And anyway, in my case, I entered
the two for the Developer edition,
and then it prompted me for
adding the current user to the SSIS group.
You need to do that in order to run the DT exec command,
and the DT exec command is how you execute packages.
So in my case, I did want to add
the current user to the group.
And that was basically it; I was done.
And it did ask me to log out
and log back in to reload the group support.
After that, if you want to run a package,
you basically run it using
the DT EXE command that you see here.
If, by the way, you didn't want to use that current user,
you can later add a current user using
the G password command that you see up here.
It also wanted me to export the path
so I had easy access to the DT EXE programs.
So I did both of those things following the configuration.
Then, when you go to actually run a package,
you use the DT exec command, like you see here,
give it a package name, and then the package password,
if your package has such a thing,
and then go ahead and it will
execute that package on your system.
So here's a quick look at
SQL Server Data Tools, connected to Linux.
Let's quick go over to Server Explorer,
and you can see, there's our Linux system.
And in this connection we're connected over
to a database called the SSIS example.
This database has one table in it called products.
And if we want to show the table data right now,
the table's empty.
Because we're going to go ahead and
use SSIS to go ahead and run our package here.
And if we look at our package,
we're importing from a flat file source.
It is using the Connection Manager.
And this is what our CSV file looks like.
This is in our temp directory.
And we're going to our destination,
and our destination is that Linux SQL Server system.
You can see connecting here;
it's much like using your regular Windows system.
You can see where you set up mappings
for our CSV to our destination table,
and right now we're not really using any in or output.
So that is basically what our table looks like.
So at this point, we can go ahead and run our package,
and go ahead and populate our table.
And so, the package is running,
and the package has completed with success.
So let's switch back.
And now we can go back into Server Manager,
look at our table, show the table data,
and there's our table populated.
And that's it for SQL and Linux part five.
Thank you for watching.