Tag: SSMS

Wrong version of ISDeploymentWizard in SSMS 17.8.1

The problem

Years ago, with SQL Server 2016 release, Microsoft came up with a separated brand new version of SQL Server Management Studio. It’s been a happy day for the SQL Server community and database developers.

Shortly afterwards, our company started to migrate every instances from older version of SQL Server to the 2016, using SSMS 17.*. Developers have already jumped into Visual Studio 2017 and everything seemed to work like a charm, until we started deploying integration services via the new SSMS, after we converted them to 2016 TargetServerVersion (which is NOT the Project Version).

The TargetServerVersion is the SSIS version, also for the deploy operations, while the Project Version setting tells to Visual Studio how to open projects based on .dtproj specifications on the XML projects definitions.

Some days ago I’ve realized that after updating to the latest build of SSMS (17.8.1), the .ispac deployment is actually executing the latest build of the Integration Services Deployment wizard (aka ISDeploymentWizard.exe). As a result every deploy of .ispac files, regardless trying double click or deploying directly from the Integration Services Catalog, the SSISDB, failed with one of the most scaring error message ever:

“[…] The Script Task <unique_name> uses version 15.0 script that is not supported in this release of Integration Services […]“

What? Why? I’ve a workstation with SQL Server 2016 and the related Integration Services 13.0:

blog_ssis_error_001

The computer I’m speaking of has the same build of mine, nobody has installed any other Integration Services versions anywhere. Andy Leonard explained this behavior in this blog post. Unfortunately, in my scenario, I cannot solve the problem.

Let’s try to explain better.

Scenario

The workstation I’m working on has SQL Server 2016 (build 13.0.5149.0) and Integration Services 13.0 on Windows 10 Pro. I’ve got a simple package with a single script task which does literally anything:
blog_ssis_error_002bAs Andy suggested us, I’ve changed the TargetServerVersion to SQL Server 2016, so I’ve got C# 2015 compiler for scripts:

First execution and deploy

Executing it locally, nothing happens, but it happens in a green way (success):

blog_ssis_error_002a.PNG

Ok, now we’re going to deploy to the other machine, generating the .ispac file and double clickin on it. It’s important to make sure that you’re double-cliking the .ispac file. Do not right click on the Integration Service Catalog project folders because SSMS will execute the latest build of ISDeploymentWizard.exe by design.

blog_ssis_error_008b
deploy from SSMS 17.8.1

 

When double clicking, the app selector should use the version related to the TargetServerVersion setting of the .ispac. This works for many of my other computers. But for one of them, here is the screen:

blog_ssis_error_005
double click on .ispac file

Hey! This is 2017 also when clicking on .ispac file. Let’s try to deploy using the new tool. The deploy succeeded.

blog_ssis_error_006.PNG

And now, let’s try to validate the package execution via SSISDB, right clicking on the project itself and selecting “Validate…”. This is the result of the empty script package validation:

blog_ssis_error_007.PNG

I’ve tried on six different machines, five ran successfully and ONLY ONE returned the above error message. Still stuck in the middle.

What have we changed?

Just the setup of SSMS (17.8.1) updating the 17.7 one. Once again, the same setup on all six machines. Five by six worked, this one is trying to kill me.

For some strange reason, something (I assure you all, not someone) has changed the registry in the .ispac application association, maybe when double clicking for the first time the file in a pending reboot (?). We’re still investigating, since we used to avoid any change in production without permissions and processes. That said, it’s weird. And it was so difficult to get.

Solution

Easy to say, now that we’ve figured out the root consequence. Not so good, but changing the registry on the key HKEY_CLASSES_ROOT\.ispac with the 130 executable (IntegrationServices.ProjectDeploymentFile.130) fixed the unwanted behavior. The key has been set to IntegrationServices.ProjectDeploymentFile.140 right after the update from 17.7 and 17.8.1.

Instead of changing it via regedit, you can try an “open with…” with “use default” checked in order to force the association between .ispac file and the right ISDeploymentWizard.exe version. But this time, in this machine, it didn’t work. This is the reason why I tried the regedit action.

Conclusions

I have to say a big thanks to Andrea Amantini, one of my peer, which is well known for his ability to find out “a needle in a haystack.”. Strange things happened here. A combination of Murphy’s law, a sort of “black” friday and a pending reboot. Hopefully this helps someone, at least.

Automatically link databases to Red Gate SQL Source Control

For the ones that have many databases to keep under source control, it can be really useful to speed up the link-to-source-control process. The only way we have now is to use the GUI provided by Red Gate SQL Source Control. Actually, there’s a github project called SOCAutoLinkDatabases by Matthew Flat, a Red Gate engineer, but, unfortunately it works only on a shared database model (centralised) in TFS. Let’s see how to manage the link using Working Folder (which is also good for many SCM) and dedicated database model (distributed).

Continue reading “Automatically link databases to Red Gate SQL Source Control” →

How to manage SQL Server security with SQL Source Control

One of the most common issue you can find when source controlling the database is about the security. How to manage the users and the related permissions?

If you use to apply permission to users and to assign users to the database, this can be a problem, especially when you are in the deployment phase (or else when getting latest versions from the source control). Let’s see these two scenarios:

Continue reading “How to manage SQL Server security with SQL Source Control” →

A 2015 full of DLM

After SQL Saturday Pordenone, I’ll keep speaking about DLM (aka ALM on databases) during the following events:

PASS Italian Virtual Chapter, April 14. I’ll demonstrate the SQL source control usage on SQL Server database
PASS SQL Saturday Torino, May 23, I’ve proposed two sessions (source control and unit testing on database)
.Net Campus a Roma, May 30, I’ll speak about continuous integration with SQL Server (source control, unit testing, deploy).
There’s a lot of work to do. But I’m thinking now about two or three new sessions. I hope to finish them in the last months of the year, and I hope to meet you in one of  these events, at least online.
Stay tuned!

 

SQL Saturday Parma – English Slide decks available to download

My SQL Saturday Parma slide decks are available to download on SlideShare.

The main topic of those presentations is database lifecycle management (DLM) on SQL Server.
Concepts: ALM/DLM, team work, differences between code and databases.
We’ve demonstrated the usage of the following tools: Visual Studio with SQL Server Data Tools, Red-Gate ed ApexSQL.
Second session: “Unit testing su database“.
Concepts: unit testing, database testing vs code testing.
We’ve demonstrated testing tools like Red-Gate SQL Test and Visual Studio Unit test projects, and the TSQLUnit framework.
Additionally, I’ve created a set of sample scripts with T-SQL and TSQLUnit on MSDN Code Samples.
Stay Tuned! 🙂

LepideAuditor for SQL Server review

Every day a DBA (or a developer), has to reply to questions like “who changed that table?” or “who tried to login on our production database?”. These answers are not so simple to find out, actually. LepideAuditor for SQL Server is a tool that can helps us to audit operations on a SQL Server instance and to monitor both the server and the database level.

Continue reading “LepideAuditor for SQL Server review” →

Create C# enum template with SSMS Boost

I’ve spoken about SSMS Boost in this post and now I’m going to apply one of its feature in order to improve the productivity of my team. Speaking about the database side, a table that contains static data can be used as a foreign key referenced table. In my experience those kind of tables are often mapped to enums in our application layer. Creating that enum can be a tedious operation and sometimes it can be very uncomfortable. With SSMS Boost we can enhance our development experience. Continue reading “Create C# enum template with SSMS Boost” →

Suggestion for SQL Prompt and SQL Test (Red-Gate)

Recently I moved my job on ALM topics, focusing on Source Control Management and Testing, in order to reach Continuous Integration on database development. I evaluated some tools and Red-Gate was my first choice. In this post I’d like to share the suggestions I made on two of the most important tool I’m using atm:

  • SQL Prompt (productivity tool for code formatting, intellisense, code inspection, etc.)

suggestions: TODO highlights. I’d like to improve readability of TODO and HACK comments, like in the following picture:

todo
 For ReSharper user (and Visual Studio ones), this is very familiar.
 
  • SQL Test (addon for SQL Server Management Studio, unit testing utility)

suggestions:

“Expand/Collapse all” for avoiding clicks when you have lots of test procedures

Logical folders for grouping tests logically and not with the strict database schema names
If you think that those suggestions could help us, feel free to vote!
Stay Tuned!