SQL Server & SSDT : Error modeling database during Publish after adding CREATE AGGREGATE / EXTERNAL NAME

I am using SSDT to create a database containing CLR user defined aggregates.

The aggregates are intended to sit in different schema. That is not possible with CLR objects inside SSDT, without using a post-deployment script to move the objects after deployment.

What I am attempting to do is:

  • Create the CLR aggregates in a single schema (the “CLR” schema) using the “Default schema” option
  • Use a CREATE AGGREGATE wrapper in the desired schema to call the CLR aggregate

For example, I have a CLR aggregate like:

(Serializable)
(Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = -1))
public struct StatsEntropy : IBinarySerialize
{
 ... etc
}

This get compiled and becomes CLR.StatsEntropy in the database. The database is called AnalysisFunctions, so it has an assembly also called AnalysisFunctions. This is no problem.

I then create a wrapper in the Stats schema like so:

CREATE AGGREGATE (Stats).(Entropy)
(
    @Values FLOAT
)
RETURNS FLOAT
EXTERNAL NAME AnalysisFunctions.StatsEntropy

This works without error when I build via debug. I have my debug connection set to a named instance of SQL Server. If I press F5, it compiles and deploys without any problem. The code is now usable on the server, including the wrapper.

But, if I attempt to publish to a server using Publish, it does not work. Instead it fails with:

Errors occurred while modeling the target database. Deployment cannot continue.

There are no further error messages either in the Data Tools Operations panel or error list. ssdttrace shows no useful information:

ssdttrace event log

This only happens when the database already exists on the server. If I drop the database, it can deploy successfully. I can deploy successfully via debug even if the database already exists, it is only via Publish that the problem occurs. I did not start getting this problem until I added the aggregate wrappers.

Previously I was also getting errors indicating that the assembly AnalysisFunctions had an unresolved reference due to the use of EXTERNAL NAME – again working via debug, not via publish. But now that error has mysteriously vanished.

I am using Visual Studio 2017 15.9.21 and SSDT 15.1.62002.01090, deploying to SQL Server 2019.

Does anyone have any idea what this error might be, or how I can debug it?

SQL Server – How to create a web service with C # and use SSDT (SSIS)?

What I mean is that I want to create a website in C #, where the user can download an Excel file and use SSDT (ETL) to check the file data and upload it to the database.
I do not know if you can, and if not, do you know of another way to download an Excel file through a website and download it to the database?

SQL Server 2014 – How to Remove Alter, Drop, Create SQL from a Deployment Script Created in VS 2015 Using SSDT 14.06

I need to delete the automatically generated deployment script below for my SQL project. I'm using VS 2015 with SSDT 14.06. Could this be something that can be set in Advanced Deployment Settings under the File tab? This concerns an existing database deployment.

IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) 
BEGIN
    ALTER DATABASE ($(DatabaseName))
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE ($(DatabaseName));
END

GO
PRINT N'Creating $(DatabaseName)...'
GO
CREATE DATABASE ($(DatabaseName))
    ON 
    PRIMARY(NAME = ($(DatabaseName)), FILENAME = 
N'$(DefaultDataPath)$(DefaultFilePrefix)_Primary.mdf')
    LOG ON (NAME = ($(DatabaseName)_log), FILENAME = 
N'$(DefaultLogPath)$(DefaultFilePrefix)_Primary.ldf') COLLATE 
SQL_Latin1_General_CP1_CI_AS
GO
USE ($(DatabaseName));

I have all the following articles unchecked for my deployment options in properties:

– Deploy the properties of the database
– Always recreate the database
– Block incremental deployment in case of data loss
– DROP objects in the target but not in the project
– Do not use ALTER ASSEMBLY statements to update CLR types

Can not Create or Use an SSIS Package Template in SSDT 2017

ENV:
Using VS 2017 Professional / SSDT 2017 on Win10 pro.
Our company does NOT allow developers to have administrator rights on their laptops. The ONLY way to create and use a template is to save it in the directory designated by Tools-> Options-> Projects and Solutions-Locations-> Location of User Item Templates:

C: Users yourname Documents Visual Studio 2017 Templates ItemTemplates

(Note: we'd like to point this option to a shared drive so we can share models)

PROBLEMS:

1) In SSDT when editing a project / package, the option Project-> Export Template is greyed out.

2) If I create a project / package in SSDT and backup (MyPkg.dtsx), then copy it into the User Item Templates directory, VS / SSDT can not find it when you perform a "Add New element "…

HOWEVER, if it is registered in the PROGRAM element template directory, it will be found, BUT we do not have administrator rights / privileges to copy files to this directory.

(I have spent DAYS trying to find this answer and I am convinced that thousands of other DBA / ETL developers have tried to do the same thing …)

SQL – Error Processing OLAP Cube in SSDT 2010

When I try to process the cube with the data source that I defined earlier, the following error is generated:

Processing information can not be provided.


FURTHER INFORMATION:

Method not found: Microsoft.DataWarehouse.Design.IEnvironmentService.get_IsAppMetroOrGreater () Boolean. (Microsoft.AnalysisServices.ManagementDialogs)

I've already changed the server name of the project properties and it still does not work.

SQL Server Data Tools – Visual Studio 2017 SSDT Add Reference

I am new to SSIS and want to create a script to connect to a Mongo database. To do this, I try to add a reference to a MongoDB driver as explained here

Unfortunately, I do not see the way to do it, in the solutions explorer, there is no way Reference node
enter the description of the image here

Could you please indicate how to add a reference to my existing project?

SQL Server – Publishing database changes with SSDT including adding columns to the existing table in the target database via Project

The publication of database changes with SSDT including the addition of columns to the existing table of the target database (created previously via SSDT). Instead of creating a new SSDT database. Trying to add a column via table creates a script or alter table script.i was encountered by the same error. Even tried with Schema Always compares the table in the target database with the same error. How to modify the changes made to objects in the target database through the SSDT publication?

RAISERROR (Rows has been detected, the schema update is complete because data loss might occur.), 16, 127) WITH NOWAIT;

I've tried checking the

  1. Block incremental deployment in case of data loss.

  2. Do not modify the replicated objects.

  3. Do not modify DATA data capture objects.

Another possibility to merge instructions for managing static data in SSDT projects

I have a database in which some of the tables are relatively static. To keep the data synchronized between database versions, I use the merge statements generated by sp_generate_merge in the post-deployment scripts. That works well. This, however, disturbs my DBA because the merge instructions are problematic.

The merge statements are relatively safe in my use case, but if there is a way to generate a safer T-SQL for the same end goal as simple as sp_generate_merge, I would much rather do it. .

SQL Server Data Tools – Codelens Visual Studio 2017 SSDT

I installed VS 2017 Pro and then SQL Server Data Tools.

Now, I have two instances (not one) ..:

two instances, photo

If I open Visual Studio 2017, I can not open the SSIS packages. So I start Visual Studio 2017 (SSDT), which supports c # projects and SSIS packages:
two programs

But in this case, I do not have any code, it does not work, where are the options oh no
enter the description of the image here

ssdt – Connection test failed due to an error while initializing the oracle provider ora-01019

I have connection issues since my 2010 SSDT to an instance of Oracle, in fact 11g. L & # 39; error failure of the test connection due to an error while initializing the oracle provider ora-01019. Earlier in the day, I was able to connect to the database with my server name and IP address. I had mapped the Oracle database with my SQL table, but running the data flow process was restored to unmasked data errors that I corrected. However, suddenly, I noticed the connection error caanot and I tried to test the connection again. Then the mistake in the title. In fact, I have several Oracle clients installed, the more sql connects to the remote server without errors. I need help urgently. I've also cofigured the path TNSnames.ora in the environment variables … help you please