Moving SSIS Packages from Windows Server 2012 R2 to Windows Server 2019

Posted on by
Reply

I tried opening the designer for a package on an old SSIS dtproj from Windows Server 2012R2 on Windows Server 2019. I kept getting mysterious failures.  So I solved it by creating a new SSIS Integration Services project then adding each package individually.  Before I did this, I opened the code and changed the connection strings and used windows authentication and (local) instead of the server name.

I was wondering why, so I asked CHATGPT the following question:

In order to use SSIS packages on a Windows 19 server from windows server 2012 R2, why did I have to create a new project then Add Existing Packages. Why did the old solution fail every time I tried to open a package in the designer?

CHATGPT actually answered the question.

There could be several reasons why the old solution failed to open SSIS packages in the designer. One possibility is that the version of SQL Server Data Tools (SSDT) being used on the Windows Server 2012 R2 machine is not compatible with the version of SQL Server Integration Services (SSIS) used to create the SSIS packages.

In this case, creating a new project in SSDT and then adding the existing packages would allow the packages to be opened and edited in the newer version of SSDT.

Another possible reason could be that the old solution was corrupted or had missing dependencies that prevented it from opening the packages properly. Creating a new project would ensure that all the necessary dependencies are present and that the project is not corrupted.

It’s also worth noting that different versions of SQL Server may use different formats for SSIS packages, which could cause compatibility issues when opening older packages in a newer version of SSIS. Creating a new project and adding the existing packages ensures that the packages are converted to the correct format for the new version of SSIS.

Reporting Services Configuration for sending mail

Posted on by
Reply

we have a standard IIS SMTP server installed. One of its sending default accounts is Network Service.  Reporting Services has to be configured to use it.

Open the Reporting Services Configuration Manager and go to Service Account. Change it to Network Service.  You will be prompted to save an encryption key with a password.

Go to Email Settings. Put in the sending email address, Use SMTP server is chosen for you, and the SMTP Server ip is 127.0.0.1

Go to the root of the tree on the left, stop and restart the service.

You’re going to need this for Subscription Services. On the SMTP server in iis choose properties, delivery outbound relay and uncheck limit connections to.   My emails kept getting queued for failing to do so.

 

Migrate Reporting Services

Posted on by
Reply

Use Report Server Configuration Manager on source server, click on Encryption keys and backup. You’ll need these on the destination server

use Report Server Configuration Manager  and stop the service on both servers

detach ReportServer and ReportServerTempDb on both servers

I advise on the Destination Server making a backup of these two database

Copy the ReportServer and ReportServerTempDb  and the encryption key file to the new server and attach them in Sql Server management studio

Start the service

Click on Encryption Keys, restore and restore the key file generated on the source  server.

Subscription users are going to be different, you have to update the users to the new server. They’re in the format of ServerName\username

select * from subscriptions  in ReportServer database.  Run a simple update query.

Done

 

Adding CLR assemblies after Server Migration

Posted on by
Reply

the CLR assemblies are brought across with the database. However they need to be added to the sys.trusted_assemblies to actually run

  1. Go to the database you have the assemblies in. It’s different for each database using CLR assemblies.

List them with sys.assemblies. You need the clr_name field

select * from sys.assemblies

List your CLR assembly files with

select * from sys.assembly_files

you need the hash field

Then run this script for each assembly

–run from create assembly after from copied to @asmbin
USE master;
GO
DECLARE @clrName nvarchar(4000) = ‘clr_name’ –this name is quoted
DECLARE @asmBin varbinary(max) = hash  –no quotes here paste the entire value as is.
DECLARE @hash varbinary(64);

SELECT @hash = HASHBYTES(‘SHA2_512’, @asmBin);

EXEC sys.sp_add_trusted_assembly @hash = @hash,
@description = @clrName;

 

go

Copying Integration Service Packages to new server

Posted on by
Reply

Location of file on Windows Server 2012 c:\program files\Microsoft SQL Server\120\DTS\Binn

I listed the packages with this SQL Query then created a batch file with the commands below

select * from msdb.dbo.sysssispackages

Exporting SSIS Packages to File:

dtutil /SQL ExportPackage /COPY FILE;C:\Packages\ExportPackage.dtsx

Importing SSIS Package to SQL:

dtutil /file C:\Packages\ImportPackage.dtsx /copy sql;ImportPackage

I recommend opening the files in a text editor and making sure the connection strings are not pointing to an old server.

Also before importing the files, create a visual studio project. Import in all the packages. Go to the properties of the project, choose configuration, build and choose the version of sql server you’re using. Rebuild all the DTS packages.

You do have the option of creating a new visual studio project and importing in all the packages and performing an in place upgrade. However some of these were written in an older version and hard to upgrade. I just used the SQL Server versioning in VisualCron, our Job scheduler, and in some cases 32 bit mode.

MsDeploy Skip website directory contents

Posted on by
Reply

First, I created a list of the sites I needed then a simple batch file containing the MSDeploy commands. Be careful of virtual directories. I had virtual directories that went to some large document repositories. The code I’m giving you only handles the root directory of the site and its physical subdirectories.

appcmd list vdir> c:/sites.text

With some editing of the file, I created an import format that I could use in Excel then used the concatenate excel function to build the msdeploy lines below.

MsDeploy is now a separate install. Once installed you will find it at c:\program files\iis\Microsoft Web Deploy V3. Run your commands from this directory.

It’s a great deal easier to directly transfer all the website files first. I personally use FileZilla between servers.  Once done here’s the two commands I use to export a site then import it to a new site. The example site here is 8aSelect and I kept the archivedirectory in c:\resources

Command to export a site and not include the contents of the site itself

msdeploy -verb:sync -source:apphostconfig=”8aselect” -dest:archivedir=c:\resources\8aselect.zip,encryptPassword=MyPassKey -skip:objectName=dirPath,absolutePath=”d:\\websites\\8aselect”

Command to import a site and skip deleting existing files

msdeploy -verb:sync -source:archivedir=”c:\resources\8aselect.zip”,encryptPassword=MyPassKey -dest:appHostConfig=”8aselect” -skip:objectName=filepath,skipAction=delete

Copying CLR assemblies across servers

Posted on by
Reply

I recently had an Sql Server 2014 r2 server I needed to migrate to Sql Server 2019 r2 with a set of CLR assemblies on it. My plan is to detach each database and reattach them on the new box. The assemblies are transferred along with the database, but they won’t run. I went through the following steps to run them.

Step 1. Click on Programmability, Assemblies under the Database in Sql Server Management Studio.

Step 2: Right click and choose Script Assembly as Create to New Query Editor Window

you’ll get something that looks like this

CREATE ASSEMBLY [Microsoft.SqlServer.Types]

from   XXXXXX

with permission_set=unsafe

Step #3, run the query select * from sys.assemblies you’ll get something that looks like this

name principal_id assembly_id clr_name
Microsoft.SqlServer.Types 4 1 microsoft.sqlserver.types, version=15.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil

Step #4 You have to make the assemblies trusted. Run the following code. Substitute the XXXXX where indicated and the clr_name from above

–run from create assembly after from copied to @asmbin
USE master;
GO
DECLARE @clrName nvarchar(4000) = ‘microsoft.sqlserver.types, version=15.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil’
DECLARE @asmBin varbinary(max) = XXXXXXXX
DECLARE @hash varbinary(64);

SELECT @hash = HASHBYTES(‘SHA2_512’, @asmBin);

EXEC sys.sp_add_trusted_assembly @hash = @hash,
@description = @clrName;

go

Step #5. Here’s the query to list the trusted assemblies (I put XXXX for hash,  you can take that out)

SELECT ‘XXXX’ as hash, description, create_date
FROM sys.trusted_assemblies

run as

SELECT hash, description, create_date
FROM sys.trusted_assemblies

with these results

hash description create_date
XXXX microsoft.sqlserver.types, version=15.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil 1/2/2023

Getting error Azure DevOps services are not available from server xxxxx.visualstudio.com

Posted on by
Reply

You could try to clear visual studio cache and TFS cache .

1.Close all instances of Visual Studio

2. Press Windows + R and input the path

%LocalAppData%\Microsoft\Team Foundation\

Then choose the version of TFS, then go to the Cache folder

%LocalAppData%\Microsoft\Team Foundation\{TFS version}\Cache 

I removed this folder and it was sufficient.

Here’s the visual studio cache by the way.

%APPDATA%\Microsoft\VisualStudio

remove the component model cache

%LocalAppData%\Microsoft\VisualStudio\16.xxx\ComponentModelCache

 

for whatever version

 

3. Remove the Cache folder

4. Restart the Visual Studio.

mailwizz failing with VestaCP and MariaDb in Centos 7

Posted on by
Reply

I recently had a frustrating problem where I couldn’t upload files into MailWizz host.  This also causes problems with command line CSV imports. I discovered I had to open up /etc/my.cnf and edit the following line to

max_allowed_packet = 110M

then in /etc/php.ini I changed the following two lines
post_max_size = 200M
upload_max_filesize = 500M

then execute

service mariadb restart

Under settings, import/export on the backend I set CLI Import Enabled to Yes and Url Import Enabled to Yes.