SQL Server On Linux – Integration Services – Installation

Background

Let us install SQL Server Integration Services on our Linux system.

os

Our os is centOS and so we will be using yum as our Installer.

Installation

Installer

Find Installer Installer

yum search

syntax

yum search [search-tag]

sample

yum search mssql

output

yum.search.20181202.0326AM.PNG

Explanation
  1. Our match is
    • mssql-server-is.x86_x64

 

Confirm Installer

yum info

syntax

yum info [tag]

sample

yum info mssql-server-is.x86_64

output

yum.info.20181202.1131AM.PNG

Explanation
  1. Package Info
    • Name :- mssql-server-is
    • Architecture :- x86_64
    • Version :- 14.0.3015.40
    • Size :- 815 MB
    • Server :- Microsoft SQL Server Integration Services

 

Install

yum install

syntax


yum install [search-tag]

sample


yum install mssql-server-is

output

output -01

yum.install.20181202.0328AM.PNG

output -02

yum.install.20181202.0334AM.PNG

Explanation

Package downloaded & installed.

 

Confirm Installation

ssis-conf

Configure ssis by invoking /opt/ssis/bin/ssis-conf.

Please pass along the setup argument.

syntax


sudo /opt/ssis/bin/ssis-conf setup 

sample


sudo /opt/ssis/bin/ssis-conf setup 

output

  1. We are prompted for the following :-
    • SQL Server Edition
    • Agreement to License Terms

output

output -01

configure.20181202.0337AM.PNG

 

Review Installation

Services

systemctl

systemctl – list

syntax

systemctl --all 

sample

systemctl --all | grep "Microsoft"

output

systemctl.20181202.1152AM.PNG

Explanation

The listed services are :-

  1. mssql-server.service
    • Database Engine
  2. ssis-telemetry.service
    • SQL Server Integration Services
      • Telemetry

We noticed that unlike the Windows Install, we do not have an actual Integration Services Engine; just the telemetry app.

 

References

  1. Microsoft
    • Docs / SQL / SQL Server on Linux
      • Install SQL Server Integration Services (SSIS) on Linux
        Link
      • Configuration SQL Server Integration Services on Linux with ssis-configure-ssis
        Link
      • Limitations and known issues for SSIS on Linux
        Link
      • Schedule SQL Server Integration Services package execution on Linux with cron
        Link

 

Microsoft – SQL Server – v2008/R2 – Installation – Integration Services

Microsoft – SQL Server – v2008/R2 – Installation – Integration Services

Attempting a very tight\slimmed install of Integration Services (SSIS) :-

Once the install completed, went to Control Panel, “Features and Programs” and reviewed what was actually installed:

  1. Microsoft SQL Server 2008 R2 (64-bit)
  2. Microsoft SQL Server 2008 R2 Native Client
  3. Microsoft SQL Server 2008 R2 Setup (English)
  4. Microsoft SQL Server 2008 Setup Support Files
  5. Microsoft SQL Server Compact 3.5 SP2 ENU
  6. Microsoft SQL Server Compact 3.5 SP2 Query Tools ENU

A bit surprised to see that “Microsoft SQL Server Compact 3.5” and its corresponding query tools are installed.

What gives….

After spending a bit of time wondering ….. I decided to uninstall the compact components…
Not so sure what are the implications of doing so…..

Microsoft – SQL Server – SSIS Error (The application-specific permission settings do not grant Local Launch permission for the COM Server application with CLSID {46063B1E-BE4A-4014-8755-5B377CD462FC}

Error Message:

The application-specific permission settings do not grant Local Launch permission for the COM Server application with CLSID
{46063B1E-BE4A-4014-8755-5B377CD462FC}
to the user LAB\SQLAgt SID (S-1-5-XXXXXXXXXXXX-XXXXXXXXXX-XXXXXXXXXX-XXXX).

 

Diagnostic:

The initial step is to find the problematic component:

  • Launch Regedit
  • Access the branch labelled HKEY_CLASSES_ROOT
  • Right-click and perform a find on the CLS_ID – 46063B1E-BE4A-4014-8755-5B377CD462FC (in this case)
  • Once found, glance to the right panel and there you have it – the problematic component (Microsoft.SqlServer.Dts.Server.DtsServer)

This security permission can be modified using the Component Services administrative tool.

Remediation Steps:

  • Launch Component Services (Using Control Panel\System and Security\Administrative Tools\Component Services)
  • Navigate to Component Services \ Computers \ My Computer \ DCOM Config \ MsDtsServer100 
  • Right click on your selection and from the drop-down menu select “Properties”
  • On the “MSDtsServer100 Properties” window, access the “Security” tab
  • From the “Launch and Activation Permissions” group box select “Customize” .  In the “Launch Permission” window, confirm \ add the Service Accounts (MS SQL Server Agent in this case).  Ensure that the Service Accounts have at minimum “Local Launch” and “Local Activation”
  • From the “Access Permissions” group box select “Customize” .  In the “Access Permission” window, confirm \ add the Service Accounts (MS SQL Server Agent in this case).  Ensure that the Service Accounts have at minimum “Local Access”

References:

Microsoft – SQL Server – Integration Service – BIDS – Inserting into Identity Column

Working on a simple SSIS Package that basically uses the “Conversion” Data Flow Transformation toolset to copy unicode data to a non-unicode destination.

To speed lab work, decided to use the AdventureWorks’s Production.Product table.

As anyone familiar with that table knows, the  primary key (ProductID) is an identity column and so it system populated and does not lend itself to straightforward population.

Error Message

The exact error message we were getting is:

Error 1 -Validation error. Data Flow Task: Data Flow Task: Failure inserting into the read-only column “ProductID”.   Package.dtsx 0 0

 

Fix

Crediting Rafael Salas; he blogs here.

Marking ‘Keep identity’ option (when using fast load as data access mode) in the OLE DB destination component should work.
Link