Skip to main content

Questions tagged [ssdt]

SQL Server Data Tools is a free component of the SQL Server platform and is available for all SQL Server users. It provides a modern database development experience for SQL Server versions 2005 and up as well as SQL Azure.

Filter by
Sorted by
Tagged with
0 votes
1 answer
27 views

Why is SQLPackage suddenly including every scalar function in my SSDT project for ALTER in my deployment?

I have a SQL 2019 SSDT project with a large number of scalar and table-valued functions. Suddenly yesterday, my Windows TFS (on prem) deployment project's SQLPackage scripting step is suddenly ...
John Hurtubise's user avatar
0 votes
1 answer
36 views

SSDT Project Will Not Publish or Generate Preview When Source and Target Partition Function Not Matched

In a SSDT project there is a partition function defined in a file as follows. CREATE PARTITION FUNCTION [pfSourceID](varchar(50)) AS RANGE RIGHT FOR VALUES ( '', 'COKE', 'DRPEPPER', 'MOUNTAINDEW', '...
Ross Bush's user avatar
  • 567
0 votes
0 answers
243 views

SSDT building error saying "unresolved reference to Login"

We retrieved source code from a SQL Server 2016 database and made a Visual Studio 2015 SSDT (SQL Server Data Tool) project. The source code contains a file Security\foglight5.sql with the below ...
James's user avatar
  • 149
0 votes
0 answers
38 views

How to throw an error in SSDT Post-Deploy script to fail it's execution?

I'm using Microsoft SSDT to version DB schema. I have a script to autogenerate some triggers and i need to make sure that we don't forget to run it when tables change. I added a part to PostDeploy ...
Ivan Koshelev's user avatar
0 votes
1 answer
228 views

SSDT db-project compare throws "source schema drift detected" for members of sysadmin

We are using SSDT database projects where changes will be done in the dev-DB, then a compare is done between the devDB and the VS-DBproject and selected changes is updated into the VS-project. Lately (...
GHauan's user avatar
  • 603
5 votes
1 answer
11k views

How to register CLR assembly as trusted in SSDT deployment

I have CLR assembly in SSDT and to deploy that it has to be trusted. What I understand there are 4 options how to do that First option, use TRUSTWORTHY EXEC sp_configure 'clr enabled', 1; ...
Muflix's user avatar
  • 1,109
2 votes
2 answers
2k views

DACPAC drops existing users and permissions from database when upgrading through SSMS

I'm developing my database through a database project in visual studio, and whenever I need to upgrade the schema of my database I like to use SSMS to do this using a DACPAC. But whenever I use my ...
Emil Skovmand's user avatar
3 votes
1 answer
239 views

SQL Server Data Tools -- Stopped connecting to Azure SQL DB

All updates are installed on VS 2017 (15.9.47) and 2019 (16.11.13) I am trying to perform a data comparison between two DB's via Microsoft’s tool , On Prem and Azure SQL managed instance Everything ...
TSide's user avatar
  • 31
1 vote
0 answers
356 views

Prevent Dacpac dropping columns via ALTER_TABLE trigger in TSQL

We're using SSDT to deploy database updates to clients, but sadly they will soon have the ability to add their own columns to tables in their databases. Obviously these won't be included in our model, ...
Tirinst's user avatar
  • 11
3 votes
1 answer
713 views

How to tell SqlPackage.exe to create object existence check script?

I have this code that creates a migration script for me: First Part (Extract) sqlpackage /a:extract /of:true /scs:"Data Source =.; Initial Catalog = Accounts; user id = ...
Ali EXE's user avatar
  • 215
0 votes
2 answers
2k views

Which install kit installs sqlcmd.exe?

I recently had to uninstall Visual Studio 2012 and DTS, then install VS 2017 and DTS again. Afterwards, The directory containing sqlcmd.exe was not in my PATH variable. I installed the following. ...
lit's user avatar
  • 231
0 votes
0 answers
256 views

Visual Studio Data Tools (SSDT) schema comparison tool does not match database objects

I am using Visual Studio Pro 2019 (16.9.4) and SQL Server Data Tools (16.0.62103.10080) but this happens since the beginning of Visual Studio 2019. I use the Schema Comparison tool to compare ...
Alex's user avatar
  • 101
1 vote
1 answer
3k views

Visual Studio Data Tools (SSDT) schema comparison tool seems like it's not working correctly

I recently started using the SSDT schema and data and comparison tools. For years I was using Red Gate's SQL Compare and SQL Data Compare, but I figured the VS tools are probably enough and figured I ...
tnk479's user avatar
  • 143
0 votes
0 answers
960 views

How can I DENY a SQL Server Database User from seeing tables in a specific schema? VIEW DEFINITION doesn't seem to work

This is somewhat of an X/Y Problem: my original problem is that I want to use SSDT to maintain my database design, with the catch that my database design uses two schemas: The dbo schema that ...
Dai's user avatar
  • 593
0 votes
1 answer
249 views

Why does SQLPackage include my table-valued function in deployments when it hasn't changed?

I'm using SQLPackage in TFS to automate an SSDT project/DACPAC deployment with SQL Server 2014. I have a table-valued function that appears in the deployment report and script with every deployment, ...
John Hurtubise's user avatar
1 vote
2 answers
933 views

Optimizing syncing of large dataset between 2 servers with SSIS

I have two DBs on different servers, Server A and Server B. Both are SQL Servers. I need to keep second DB in sync with the first one. DB schema and tables are identical, but Server A periodically ...
Anonymous's user avatar
  • 143
-1 votes
1 answer
972 views

Excluding database objects from a DacPac database deployment?

As I understand it, a sql server DacPac (visual studio data-tier app) applies to the entire database that it's deployed in. However, for my particular database, I have some tables and stored procs ...
datadawg2000's user avatar
0 votes
1 answer
648 views

SSIS Excel Import - First row getting skipped

I am using VS 2017(SSDT) with SSIS(2012). I'm trying to import an Excel file, but the issue is that the first row is getting skipped. I've unchecked First row has column names in Excel Connection ...
Mithun John Jacob's user avatar
1 vote
1 answer
1k views

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 ...
Definite's user avatar
  • 145
4 votes
2 answers
4k views

Exclude certain schema along with unnamed constraints in SSDT

Task Automate database deployment (SSDT/dacpac deployment with CI/CD) The database is a 3rd party database It also includes our own customized tables/SP/Fn/Views in separate schemas Should exclude ...
Santhoshkumar KB's user avatar
1 vote
1 answer
2k views

What is the best way to include a large amount of configuration data in a SQL project?

I have a BIDS SQL Server Database Project with a database that I publish dynamically to various servers (SQL2016). It initially starts blank and everything is parameterized. Everything is in Azure ...
Alex K's user avatar
  • 175
1 vote
1 answer
3k views

In a DacPac deployment, if a stored procedure that is called by another stored procedure is updated, is the calling stored proc also redeployed?

I noticed that, in my DacPac, after a deployment, two stored procs were given a modified date on sys.objects, almost to the same millisecond. However, I would only expect one of them to have this, ...
datadawg2000's user avatar
0 votes
0 answers
20 views

SSIS/SSDT Censor Characters in Specific Positions With Flat File

I'm developing an ETL process via SSIS/SSDT, which works with Fixed-Width Flat Files. It has 3 record types, and one of them I need to censor specific characters at a certain position. Does anyone ...
David's user avatar
  • 1
1 vote
0 answers
628 views

SSDT Schema Compare shows same objects to add and delete in destination

When using schema compare, it shows the objects in dbo schema as objects to add in destination and same objects to delete as well. After inspecting the definitions, they appear to be the same. Why ...
cenko's user avatar
  • 31
7 votes
1 answer
2k views

SSIS Package Not Being Deployed

I have an issue with an SSIS package, that when deployed via VS2017 to the integration services catalog is not actually being deployed. This is an individual package deployment, and not a project ...
SteveG's user avatar
  • 71
2 votes
1 answer
1k views

Backup from dacpac file extracted using SSDT

I tried method given in this answer by Ramankant Dadhichi but deploying failed: Backup a database from Azure SQL Managed Instance and restore to on premise SQL Server I have my database in Azure SQL ...
Priya Sharma's user avatar
5 votes
1 answer
2k views

SQLPackage resets database recovery model

I am using SQLPackage.exe with database publish profiles to deploy database changes to DEV and QC instances. I have databases in simple recovery mode. But when I deploy changes using SQLPackage, it ...
JackLock's user avatar
  • 468
2 votes
2 answers
1k views

Error creating memory optimized filegroup in SSDT

I am attempting to add a memory optimized filegroup to a SQL server database project in SSDT. This is for SQL Server 2017, using Visual Studio 2017. However compiling the project (pressing F5 to ...
Definite's user avatar
  • 145
2 votes
1 answer
1k views

Error with Publish using publish profile -" DropExtendedPropertiesNotInSource conflicts with the selected DoNotDropObjectType ExtendedProperties"

As the title says, I get this error when trying to Publish using a publish profile XML: DropExtendedPropertiesNotInSource conflicts with the selected DoNotDropObjectType ExtendedProperties I have ...
seanw_pc's user avatar
4 votes
0 answers
901 views

Why is my SSIS package not getting updated?

I have a Integration Services project within SSDT that has multiple packages. When I right click a single package and choose deploy, SSDT says the operation completed successfully. However, when I ...
Mr.Brownstone's user avatar
9 votes
1 answer
1k views

Is there a way to easily validate that a SQL Script is compatible with a specific SQL Server version?

I am currently working on a project that needs to operate in different SQL environments. Most of my developers have SQL Server 2017. The application runs in SQL Azure, and the client has recently ...
Joe Brinkman's user avatar
4 votes
3 answers
2k views

How to Exclude Foreign Keys during Publish action in sqlpackage

I am trying to publish a .dacpac extract to a target database, I want to exclude just the foreign key constraints in all the tables. After going through the ExcludeObjectTypes options I do not find a ...
Shikhar Nahar's user avatar
4 votes
1 answer
4k views

DACPAC Deployment Rebuilding All Indexes

I'm in process of deploying a DACPAC provided by our ISV. The DACPAC is rebuilding every index in the database. We're testing in a Lab now, however, this will create a significant impact to our ...
Dennis Peterson's user avatar
1 vote
1 answer
440 views

Is there an underling difference between TIMESTAMP and ROWVERSION when creating columns with each?

I'm having problems with SSDT detecting a change to a "TIMESTAMP" column where both the target column and model.xml column definition are of type "TIMESTAMP". When this happens, the following alter ...
newcube's user avatar
  • 19
2 votes
1 answer
1k views

How to remove Alter, Drop, Create SQL from deployment script created in VS 2015 using SSDT 14.06

I need to remove the below auto-generated deployment script for my SQL project. I am using VS 2015 with SSDT 14.06. Would this be something that can be set in the Advanced Deployment settings under ...
Jason's user avatar
  • 69
1 vote
1 answer
125 views

SSDT Publish failing with an error without even running any statements

My publish attempts are failing with this error Msg 205, Level 16, State 1, Line 7572 All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in ...
Elaskanator's user avatar
4 votes
0 answers
848 views

SSDT Schema compare to compare only specific objects

Is it possible to use schema compare in SSDT to compare only specific objects? Not object types, specific objects like 3 tables and 2 stored procedures instead of all tables and all stored procedures?
Victor Barajas's user avatar
3 votes
1 answer
1k views

SqlPackage.exe unnecessarily drops and recreates indexed view with schemabinding

Currently SqlPackage.exe used in conjunction with BitBucket for the deployment of the database schema changes. Recently a pretty large indexed view has been created based on 3 source tables, which ...
Thomas Costers's user avatar
0 votes
1 answer
69 views

Does MS provide a way to query a SSDT project

Is there a way to query a SSDT project to get a list of tables or foreign keys? Similar to what you can do with the actual database?
jmoreno's user avatar
  • 1,065
1 vote
1 answer
141 views

Ms VS2017 with SSDT has the 'User with Password' template but thinks it's wrong

In my database project when I choose the 'User with Password' template in the Add New Item dialog, code is generated, kind of CREATE USER [u2] WITH PASSWORD = 'jbniq_ushfbwc=l5nAxu8aapmsFT7_&...
Serg's user avatar
  • 1,266
3 votes
1 answer
2k views

Publishing database changes with SSDT that include adding columns to existing table to the target database through Project

Publishing database changes with SSDT that include adding columns to the existing table in the target database(which was previously created through SSDT).Instead of creating a new SSDT database. ...
Koushik Reddy's user avatar
10 votes
1 answer
2k views

Storing CREATE EXTERNAL DATA SOURCE in a SSDT project without exposing the DATABASE SCOPED CREDENTIAL or MASTER KEY secret;

I have a SSDT project that deploys to Azure SQL DB. Recently I added an external table. This required an external data source as follows: CREATE EXTERNAL DATA SOURCE [data_warehouse] WITH ( ...
Justin Dearing's user avatar
3 votes
1 answer
2k views

What does @RC mean in SSDT SQL Server Unit Testing?

In Sql Server Unit Testing, SSDT, what does @RC stand for? Does it mean Row Count, or Row Column? Team wants to understand, we have been using it for long time, good tool which works, just curious.
user avatar
0 votes
1 answer
4k views

Install SSDT for VS 2017 - Missing Integration Services

I have downloaded and installed SSDT from Microsoft docs. The install was successful and I now have SSDT for VS 2017 installed - version 15.8.9 (as per screenshot) However, I cannot create an SSIS ...
Kevin's user avatar
  • 533
2 votes
1 answer
1k views

Visual Studio SSDT Ignore Query Store Options in Publish Profile

Is there any way in Visual Studio SSDT Publish Profile, to Totally Ignore Query Store Options Completely? It keeps asking us to Rerun publish code below. I go to Database advanced settings, and turn ...
user avatar
3 votes
1 answer
379 views

SQL Visual Studio Find and Replace in Publish Profile

We have source control in Visual Studio Database Projects (SSDT). To make cross database references, we utilize something like this below in Project References. We need Project references, since ...
user avatar
5 votes
1 answer
626 views

Reinitialize Table Values in SQL SSDT Unit Testing

I am creating SQL Server Unit Tests. We are testing various stored procedures. In Unit testing principles, it is good practice to setup a small database table, populate values, and tear down (...
user avatar
3 votes
1 answer
769 views

Make SQL Server SSDT Database Project Reference Different in Publish Profile

In a SSDT Database project, I know how to publish the Self-Database to different locations. I can change eg, the Customer database name in the Publish profile. Example In: Dev Environment: will ...
user avatar
1 vote
1 answer
911 views

View with OPENQUERY won't build in SSDT project

I have an existing database that I'm trying to set up as a database project. I'm not able to get the following statement to build: CREATE view [dbo].[VW_SOMEVIEW] as SELECT * FROM OPENQUERY ([...
Vickey C's user avatar
1 vote
1 answer
1k views

How to read data from multiple Excel files with SQL Server Integration Services(New Senario)

Hope you're doing well. I have a scenario here. As you can see I have a table with two main columns : What I need is to read each "FilePath" which is "Active" right now and then read the Excel files ...
user avatar