How To Check Edition of SSIS Installed on Server with SQL Server Installed - SSIS Tutorial

 Issue: How to Check Edition of SQL Server Integration Services (SSIS ) Installed on Server

Sometimes we need to find out which Edition of SSIS we have installed on the server. As we know that SSIS service can be installed on a server stand-alone and does not require SQL Server installed on the same machine. 

Solution: 

The first thing that comes to mind, to go to SQL server configuration manager and take a look. I can see that I have SSIS 2019 installed but do not say if it is standard or enterprise edition.

There is a summary.txt file that is generated when we install SSIS/SQL Server.

C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log

In the summary.txt file, you will see records like below. Under the Edition, you will see Developer Edition or Enterprise Edition.

How to find out edition of SSIS Installed on Windows Server - SSIS Tutorial

Run SSIS Package in 32 bit mode using bat file - SSIS Tutorial

 Often we need to situation where we want to create batch file to execute our SSIS Pacakage. I have seen this in many situation. One of example, sometime people use third party schedulers for which they create batch file for ssis package and then call that batch file in third party schedule. 

In this post, we are going to learn how you can create batch file for your SSIS Package execution in 32 bit mode.

1) First of all you are going to create an empty text file and change the extension to .bat.

If you don't know how to create batch file you can watch "How to create batch file"

2) Right click on batch file and then hit Edit. Paste below line of code to run your SSIS Package in 32 bit from batch file.

"C:\Program Files (x86)\Microsoft SQL Server\150\DTS\Binn\DTExec.exe" /f "Your SSIS Package Path"

Notice that, I have 150 folder because I am using SQL Server 2019. In your case you might 130,140 or 150 depending upon your SQL Server version.

I used below command in batch file to execute my Package.dtsx.

Save the file. Double click on it or call it from some scheduler and it should execute your SSIS Package in 32 bit.

How to Execute SSIS Package from Command Line in 32 Bit runtime and 64 bit runtime - SSIS Tutorial

Executing SSIS Package from Command Line is easy. You will be using DTExec.exe. You have two choices, either to execute your SSIS Package in 32bit runtime or 64bit runtime.

To execute your SSIS Package in 32bit runtime

Go to Run, type cmd to open the command line.to execute SSIS Package in 32 bit runtime, use below.

"C:\Program Files (x86)\Microsoft SQL Server\150\DTS\Binn\DTExec.exe" /f "Your SSIS Package Location"

Notice, I am using 150 folder in above as I have SQL Server 2019. You will change that according to your SQL Server.

In below example I am running my ssis package in 32 bit.

"C:\Program Files (x86)\Microsoft SQL Server\150\DTS\Binn\DTExec.exe" /f "C:\Users\Aamir.Shahzad\Source\Repos\SSIS2\Integration Services Project1\Integration Services Project1\Package.dtsx"

This is how your output in cmd will look like.

To run your SSIS package in 64bit runtime.

 You can use the below command.

"C:\Program Files\Microsoft SQL Server\150\DTS\Binn\DTExec.exe" /f "Your SSIS Package Location"

In the below example, I am executing Package.dtsx SSIS Package from the command line.

"C:\Program Files\Microsoft SQL Server\150\DTS\Binn\DTExec.exe" /f "C:\Users\Aamir.Shahzad\Source\Repos\SSIS2\Integration Services Project1\Integration Services Project1\Package.dtsx"

Unable to install SSIS Devops Tools from Marketplace on-premises- We've encountered an error while downloading the extension. Please try again later

 Issue :

When you try to install SSIS DevOps Tools from MarketPlace to DevOps on-prem DevOps server, you are getting an error "We've encountered an error while downloading the extension. Please try again later."

Solution:

You cannot install SSIS DevOps Tools because the default extension size is equal to 25 MB and SSIS DevOps Tools extension size is greater than 25 MB. Therefore, to install SSIS DevOps Tools extension on-prem, we will be increasing the "The extension package size" to 30 MB.

Here are the steps

1) Take a full backup of your database before applying the below script to increase extension package size.
2) Execute below script to set extension package size to 30 MB.

DECLARE @keyvalues dbo.typ_keyvaluepairstringtablenullable;

INSERT @keyvalues

VALUES ('#\Configuration\Service\Gallery\LargeExtensionUpload\MaxPackageSizeMB\', '30')

exec prc_UpdateRegistry 1, @keyvalues

This will add a new entry in the table to update the default value.

3) Once step 2 is done. You can follow the link to install SSIS DevOps Tools

4) Open your server, update your server name in below url

http://TFSURL:8080/tfs/_gallery/manage

5) You need to find Upload Extensions as shown below.

Locate VSIX extension file that you downloaded from the marketplace. This will install the extension to the server.

As you have uploaded the extension, Now you need to CONFIGURE the SSIS DevOps Tools extension for your specific collection.

Open your server url http://TFSURL:8080/tfs/_gallery , you will change TFSURL to your Server name.

Now you will notice that the SSIS DevOps Tools extension shows up as shown below.

Click on SSIS DevOps Tools, and it will take you to "Get it free". 

Click and configure for collection you want to. You are all done here, you can go to the collection now, and you will see the extension there.