All Questions
Tagged with ssdt or sql-server-data-tools
2,076 questions
0
votes
0
answers
65
views
How can I add a new table, populate it with reference data, and add a foreign key constraint to an existing table at the same time?
Using a .sqlproj, I want to do the following:
Add this table:
CREATE TABLE [dbo].[ref_newtable] (
[id] [dbo].[dtNUMBER] NOT NULL,
CONSTRAINT [pk_ref_newtable] PRIMARY KEY ...
- 3,754
0
votes
1
answer
65
views
How can I instruct SSDT to ignore generated constraint names during deployment?
My database contains a table defined like this
CREATE TABLE [dbo].[MyTable]
(
[TradeBlockId] [bigint] NOT NULL,
...
[LastUpdatedDate] [datetime] NULL,
CONSTRAINT [...
- 3,571
0
votes
0
answers
38
views
How to ignore tables of a name pattern in SSDT deployments?
In SSDT, I know I can ignore any predefined script by setting its "build action" property to "None".
What about tables I don't know the exact name but following a particular ...
- 3,571
0
votes
0
answers
116
views
Can't create a Report Server Project in VS 2022
This is likely something that has an obvious solution, but I cannot find it for the life of me. I cannot create a Report Server Project in Visual Studio 2022.
VS2022 was just updated to v17.11.4. I ...
0
votes
0
answers
78
views
Renaming SSIS packages and deploying issue
I had an SSIS package that was deployed to the SSIS catalog called package.dtsx. I renamed this package (In SSIS Data Tools by right clicking on the package and selecting rename) to Client1.dtsx and ...
- 35
2
votes
0
answers
72
views
sqlproj build error about computed column referencing an always-encrypted column, but it works on the DB
I have this table
CREATE TABLE [dbo].[LawFirm]
(
[LawFirmId] INT IDENTITY (1, 1) NOT NULL,
[EntityId] INT NOT NULL,
[Name] VARCHAR (100) NOT NULL,
[...
- 21
0
votes
0
answers
24
views
How to Use a Dynamic Connection String During SSIS Package Validation in Visual Studio 2017 Package Deployment Model?
Is there any event handler or task that can dynamically change the connection during the validation phase of a package when executed through the Execute Package utility?
I have two variables in my ...
- 11
-1
votes
1
answer
186
views
SSDT Installation issue on VS2022
I am facing an issue with SSDT. I need the tool for my SSIS project. I have followed the steps mentioned in the following link.
As I launch VS Installer I see this.
To me it means SSDT is already ...
0
votes
1
answer
108
views
vstest with workload identity federation
I'm using DevOps Pipelines to run my sql unit tests.
This works well with the vstest task and a service connection with ActiveDirectoryServicePrincipal.
But our platforming guy changed the ...
- 34
0
votes
0
answers
78
views
Issue with Building .sqlproj Project on macOS Due to Missing SSDT References
I'm currently working on a .NET 6 project. It includes a .sqlproj file for database schema management.
Problem faced
When trying to build the project with the .sqlproj file on macOS, I get build ...
0
votes
1
answer
158
views
Unable to open Sql Database project in VS 2022 or 2019
I have VS.Net solution in which multiple projects are there, which all are opening fine, but the SQL Project where we have SQL Files is not opening, if I am trying to open it in VS 2022, it is giving ...
- 561
0
votes
2
answers
283
views
How to fix "The installation of this Visual Studio instance is not completed" for Visual Studio professional 2022
After installing Visual Studio Professional 2022, I was trying to install data tools for SSIS development and suddenly this message appeared "The installation of this Visual Studio instance is ...
0
votes
0
answers
28
views
Source database does not show in drop down ever after selecting it vanishes
I am trying to compare the schema of DEV server to GIT project. When i connect to source DB it does not show in the dropdown after selection it simply vanishes. This is not allowing me to do schema ...
0
votes
0
answers
116
views
Error SSIS OLEDB Source/Target Editor, using Visual Studio 2022, Oracle DB
I am unable to preview data when using OLEDB Provider with oracle DB TNS name. oracle 12c 64 bit client and VS 2022(recently updated to version 17.8.6)
When I execute the task, it can fetch records, ...
0
votes
1
answer
137
views
Azure DevOps Ubuntu Agent SqlPackage Missing
I am attempting to run a SqlPackage /Action:Publish ... command in a PowerShell pwsh task on an Ubuntu agent. I installed the tool using dotnet tool install --global microsoft.sqlpackage. However, ...
- 1,552
-1
votes
1
answer
57
views
SSIS - Help for integrating external dll
I am planning to rebuild legacy SSIS projects and, in the new version, I have integrated ScriptTask type components in order to overcome some limitations of the framework.
Some of these scripts simply ...
0
votes
1
answer
20
views
Visual Studio SSDT Publish fails
Using Visual Studio 2022 v17.10.1
Using a project with multiple SSDT databases, need to publish fairly frequently.
Most of the time it doesn't work: after clicking Publish, I set the publish ...
- 1,393
0
votes
0
answers
54
views
The following error has occurred: Upgrade Failed due to the following Error. The error code is:-2147467259.Message: Unspecified error
I am trying to install Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2013 [https://www.microsoft.com/en-us/download/details.aspx?id=42313]
I tried to install it, and then ...
- 1
0
votes
0
answers
42
views
SQL Server Data Tools SSIS Delay Notification
I was working on a project using SQL Server Data Tools, and I don't know what happened exactly since I didn't perform any stupid things as I can remember, but it keeps freezing and open a dialog that ...
- 21
0
votes
0
answers
60
views
.sqlproj getting error when database having multiple schema - Error SQL71501: Table: [schema].[table] has an unresolved reference to Schema [schema]]
i have SQL Server database, with objects defined under two schemas,
Schema 1 - dbo.
Schema 2 - pr.
Tables - dbo.person, dbo.orders, pr.person , pr.orders, pr.sales
SP= dbo.getpersons, dbo.getdata, pr....
- 5
1
vote
0
answers
89
views
SSDT database script sets DATA_COMPRESSION per partition
When I generate database script in Visual Studio, the CREATE INDEX statement specifies DATA_COMPRESSION for each single partition:
CREATE CLUSTERED INDEX [ExampleIndex]
ON [dbo].[ExampleTable]([ID]...
- 43
0
votes
0
answers
66
views
SQLPackage.exe errors for OutputPath parameter in some environments
when I try to execute SqlPackage.exe with the OutputPath parameter, on some servers it reports - Illegal characters in path.
Although it seems to work on other server environments.
I checked the ...
- 670
0
votes
0
answers
58
views
Pass variable / data from pre-test to test in SQL Server Unit Test
How can I pass a variable from my pre-test to my test in a SQL Server Unit Test?
From reading the MSFT documentation, it seems like they didn't really solve this. We can't directly pass variables/...
- 16k
0
votes
0
answers
26
views
SSDT unit testing of scripts
Our DB solution has scripts that are not part of the schema (tables, procedures, etc.)
Generally, these scripts mostly select (part of BI)
What is the correct approach unit test these scripts?
I ...
- 21
0
votes
1
answer
873
views
SSIS.ReplacementTask after upgrading VS2019 to VS2022
We have upgraded from VS2019 to VS2022.
We have hundreds of packages, tested a lot of them.
One of the older ones now does not open.
When opening a package in this solution we first get three errors ...
- 1,610
0
votes
0
answers
89
views
Run SSIS package through Server its working fine but that package I am running on my local system its not working
I am using SSMS 2016 client and Visual Studio 2017 on my local system.
I created a package in Visual Studio 2017, this package is based on package deployment model, I'm running this package through ...
- 11
0
votes
0
answers
50
views
How to use Project Level connection Managers in SSIS Script Task
I've a Project level connection managers which are used across multiple packages when I'm trying to use the same connection manager in SSIS Script tasks like below but getting null value in ...
- 844
0
votes
0
answers
10
views
Performance difference depending on attribute order when using SSAS for data import in Excel pivot table
Using a pivot table in Excel 2019, I imported data from the MDB SSAS server, placed 3 attributes on the row axis, and placed 3 measures on the row too.
A1 and A2 are in dimension A, and B1 is in ...
0
votes
1
answer
280
views
How to pass a variable value in SSIS to Python Script : Execute Process task
I've a execute process task which executes python Script
I want to pass root variable value in python from SSIS when I give the project parameter value in Python script it is throwing an error how do ...
- 844
0
votes
0
answers
198
views
Cant get ManagedDTS version 15 folder even after installing SSDT for VS 2017
I want to have SSDT for Visual studio 2017 to work with sql server 2019, so I will get manageddts version 15 at path
C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS
I ...
- 1,004
0
votes
1
answer
269
views
SSIS Package Execution Fails with COM Class Factory and IClassFactory Errors in SQL Server 2019
I'm encountering two distinct errors when attempting to manually execute or schedule SSIS packages on my SQL Server 2019. Despite thorough checks on permissions and configurations, these errors ...
- 25
0
votes
0
answers
29
views
How to pre-process (handle blank "" values - not NULL) in Excel Source for SSIS package?
Rather new to SSIS Development and needing assistance. I have a simple SSIS package designed that will take data from an Excel (.xlsx) source data file and push to Oracle DB by:
Reading a directory ...
- 3,934
-1
votes
1
answer
352
views
Authentication using AccessToken / ServicePrincipal
Am having a SSDT unit test project with EntraID authentication to my azure database.
This SSDT unit test project shall be executed (see image below):
Locally in VS Code using the connection string +
...
- 428
0
votes
0
answers
32
views
$(ProjectDir) in post deployment SQL files
I want to use $(ProjectDir) the one available under macro in pre- and post-deployment scripts instead of hard coding path.
I tried
:setvar tpath '$(ProjectDir)'
Print $(tpath)
This is just printing $(...
- 1
0
votes
1
answer
153
views
Multiple CSV files to Multiple tables with different schema in SSIS
I have a Master table which has SourceName(Ex:- CSV File Name) and DestinationName(Ex:- Sql Table Name) as I know the source and destination; do we have any approach in SSIS to load files from source ...
- 844
0
votes
0
answers
62
views
Visual Studio SSDT Database Project - add database reference without inheriting sqlcmd variables
I have several related SSDT SQL Database (.sqlproj) projects in a Visual Studio Solution.
Each project has a set of SQLCMD variables and we use the Database Reference feature to enable cross-project/...
- 2,021
1
vote
0
answers
286
views
SSDT SQL Project for Synapse Set Scoped Configuration DW_COMPATIBILTY_LEVEL
I have a Visual Studio 2022 SSDT SQL Project used for the design and deployment of a Synapse SQL Dedicated Pool. The project is targeting the platform MS Azure SQL Data Warehouse.
The database should ...
- 1,102
0
votes
0
answers
52
views
How disabling the code analysis at design time for a database project - Visual Studio 2022 plus SSDT extension
I'm developing a database project to implement a db versus a Synapse dedicated SQL pool, by using Visual Studio Enterprise 2022 vers. 17.8.3 plus SSDT extension.
After some script created, I've ...
- 29
0
votes
1
answer
143
views
Getting the Last Day of the Previous Week SSIS Expression
Could you please help me to find the last day (Sunday) of previous week?
I need to convert it to string because I need to have file name like "Test_2023_01_07".
I need to do it using an ...
0
votes
0
answers
99
views
Columns missing on Objects from DACPAC
I am using Visual Studio 2022 for developments in a database project (SSDT).
I have referenced multiple DACPAC files from all reqired databases from our MS SQL Servers from our organization network. ...
- 491
0
votes
0
answers
227
views
Fix "Unresolved Reference" errors using sys.columns in a different database
I have an SSDT Solution with two database projects, let's call them MainDb and OtherDb. I'm trying to create a view on the MainDb database which surfaces some information about related objects in the ...
- 4,665
0
votes
1
answer
319
views
Ignoring SQL 70001 Build Error in Azure CI Pipeline for SSDT Project
How can I configure my Azure CI pipeline for a SQL Server Data Tools (SSDT) project to ignore the SQL 70001 build error and allow the pipeline to succeed even when this error is present during the ...
- 369
0
votes
1
answer
207
views
ERROR: This version of Visual Studio is unable to open the following projects
Today, when I opened my Visual Studio Enterprise to work, the packages were having issues.
I tried uninstalling Visual Studio and reinstalling it, and I did the same with SQL Server Data Tools, but ...
- 23
0
votes
1
answer
113
views
Creating SSIS Custom Component - PreExecute method cannot get connection manager
I had trying my hands on creating a SSIS custom component to retrieve data from Salesforce. I having the custom connection manager project working and verified, and the custom source works and ...
- 249
0
votes
1
answer
166
views
Using SqlPackage .NET 6 edition. How to specify Data/Log file paths?
I am forced to use the .Net 6 version of SqlPackage.exe to import a bacpac file.
I have looked over the documentation here https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-import?view=...
- 396
0
votes
1
answer
19
views
Visual Studio SQL Deployment Project
I am trying to generate a script from Visual Studio --> Publish-->Generate script. it is failing for on server with an error "Incorrect Syntax Near *=" and same is getting generated ...
0
votes
1
answer
399
views
SQLPackage Deployment Failure
I'm using an Azure DevsOps Release Pipeline, which has the Deploy DacPac step. It's failing with no useful error. Where do I even start trying to debug something like this?
2023-10-31T11:52:53....
- 11
1
vote
1
answer
167
views
Illegal characters in path - dacpac deployment via SqlPackage.exe
I'd like to call SqlPackage.exe using splatting but I'm doing something wrong as I get an error:
Illegal characters in path.
$FilePath = 'C:\some path\SqlPackage.exe'
$ArgumentList = @(
'/Action:...
- 16k
0
votes
1
answer
442
views
How to update a database on server using a DACPAC in Azure Pipeline without dropping and recreating the database
I have a SSDT (SQL Server Data Tools) database project, and when I deploy it, it drops and recreates the database/tables/stored procedures etc.
My scenario is that I want it not to drop the database ...
- 369