Since SSIS does not like being merged nicely I was wondering how to have a big SSIS package split up. With the idea of having multiple devs working on this simultaneously, and a minimal chance of having merge conflicts. In SSIS 2016 I found these options: - ***package parts***, but apparently they don't share connection managers. I don't want to have 100 different connection managers. - ***subpackages***, still this doesn't look very clean and I also wonder if this is what it is intended for. Also, the debugger goes crazy too opening the subpackages while running. Any other drawbacks I should know of? I can't be the only person with this problem. Is there an other way to achieve this?

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 (truncate/delete) the database tables, and resetup for each test. This way every unit tests will have a clean environment to validate sprocs which insert, select, update, delete, etc, Does anyone where or how to reinitialize the tables values in Sql Unit Testing? Resources are pretty new for unit testing in SQL SSDT VS 2017, so I think lot of people are trying to figure out and understand. Feel free to show or add pictures below. http://www.sqlservercentral.com/articles/Unit+Testing/155651/ http://www.erikhudzik.com/2017/08/23/writing-sql-server-unit-tests-using-visual-studio-nunit-and-sqltest/ Pictures in Visual Studio SSDT: Also, trying to review this class in SQLDatabaseSetup.cs: [TestClass()] public class SqlDatabaseSetup { [AssemblyInitialize()] public static void InitializeAssembly(TestContext ctx) { // Setup the test database based on setting in the // configuration file SqlDatabaseTestClass.TestService.DeployDatabaseProject(); SqlDatabaseTestClass.TestService.GenerateData(); } } } using Microsoft.Data.Tools.Schema.Sql.UnitTesting;

Has anyone successfully connected SnowFlake/DataView to SSMS whether it was through VS or other means, if so, may I please know how you did it? Essentially, I am trying to get the SnowFlake data into SSMS. I have read their articles and they helped some, I talked to their techs and they told me they cannot help with my VS issue. See, I have been able to bring in some tables into our server via VS package. The problem is that when I have SSMS run the package I have to run it in 32bit instead of 64bit. I have the driver for both, please correct me if I am wrong, but I think SSMS only supports 32bit? This change is making the small amount of tables I am bringing in take over 5 hours to run, and this is after the MANY creative modifications I do before bring in the tables so that the package does not time out. Another way I have tried was through a linked server; However, in the catalog section, the databases do not show up. It looks like it connected but if I cannot access the database, is it really connected? you know what I mean? I also tried to talk to their techs about this and they directed me to an article where they said they do not support linked servers. Now I have ran out of options and need you guy's help. If you want to know why I want to bring in the data into SSMS, it is bc Snowflake SQL is VERY limiting making it difficult to query the data, which is not the issue with SSMS.

We heavily use BIML in our data projects for the development of SSIS packages, so BimlExpress is important to us. So far, it looks like BimlExpress does not provide an installation for Visual Studio 2022 yet. For Visual Studio 2019, it can install but will get an error saying "This extension was not loaded because it uses deprecated synchronous autoload APIs". See the screenshot below. **Our Question:** Is there a way we can still use Biml with Visual Studio, better to be one of the recent versions, e.g., 2022 or 2019. **Screenshot:**

I am maintaining and publishing a DBA tools database using a Visual Studio 2017 Database Project. Now I am trying to add a SQL Server agent job using a post deployment script. The job is scripted out by SSMS and is parsing/executing just fine there. It is created with the option "Script (not in build)" and called by the post-deployment script (":r /path/file.sql"). Several other scripts are called like this and the build is working like that. VS is parsing the script in the editor as well until I try to build. The build fails with: > 72006: Fatal scripting error: Incorrect syntax was encountered while parsing '$(Escape_SQUOTE('. The lines it fails on is: @command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d MSSYS -Q "EXECUTE dbo.IndexOptimize @Databases = ''USER_DATABASES'', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @UpdateStatistics = ''ALL'', @OnlyModifiedStatistics = ''Y'', @MaxDOP = 8, @LogToTable = ''Y''" -b', @output_file_name=N'$(ESCAPE_SQUOTE(SQLLOGDIR))\StatisticUpdate_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt', So it tries to parse something as code that is actually supposed to be just a Unicode sting. I tried to put both strings into a variable ending up with the same error in the SET statements. The full code for your reference: USE [msdb] GO BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance' IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'MSSYS StatisticUpdate - USER_DATABASES', @enabled=1, @notify_level_eventlog=2, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'Source: https://ola.hallengren.com ', @category_name=N'Database Maintenance', @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'StatisticUpdate - USER_DATABASES', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'CmdExec', @command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d MSSYS -Q "EXECUTE dbo.IndexOptimize @Databases = ''USER_DATABASES'', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @UpdateStatistics = ''ALL'', @OnlyModifiedStatistics = ''Y'', @MaxDOP = 8, @LogToTable = ''Y''" -b', @output_file_name=N'$(ESCAPE_SQUOTE(SQLLOGDIR))\StatisticUpdate_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt', @flags=0 IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'MSSYS StatisticUpdate - USER_DATABASES', @enabled=0, @freq_type=8, @freq_interval=62, @freq_subday_type=1, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=1, @active_start_date=20171010, @active_end_date=99991231, @active_start_time=190000, @active_end_time=235959 IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO

Visual Studio with the SSDT add-in has schema comparison tools for relational databases that allow you to compare a repository to a target database and generate a .dacpac for deployment. Is there similar out-of-the-box functionality (with or without an add-in) for comparing/deploying SSAS cubes and tabular models? If so, how does one access it?

A user.sql file which contains USE [database] GO -- Legacy users cleanup DROP USER IF EXISTS [dw]; etc... is located at the repository: https://company.visualstudio.com/Database/_git/Azure?path=etc ... I have a powershell script running on a VM that restores the database and I need it to remove users from the newly restored db using the above user.sql file. Is there a way to access the file directly from the .ps1 script, read the code from it and execute it? If not, what would be the steps to make it work? Thanks and regards.

Currently I have a simple SSIS project inside of a server that only allow one person to use. I am looking for an alternative for other people to collaborate with that SSIS project. I have done my research to try using Azure DevOps and Github. But my manager wants me to use Active Directory, to allow users with valid username to access the project. How can I add new user inside of the SSIS project? Is there any way I can just add new user through visual studio so that they can also collaborate? I have added a new user inside of my SSISB folder. But still I have not found any solution.

I have created and tested a report in Visual Studio and it returns data from a SQL server database. Once uploaded to SSRS, the report returns old data. What would I need to check as it appears the data source has cached? Update: I have created a table in the SQL database, and can query it from VS and report builder, but not from a report in SSRS.

I am trying to use Visual Studio 2022 to successfully load and build an SSIS package that targets SQL Server 2016. Documentation suggests that this is indeed supported. Per the online docs (https://learn.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver16) , I have used the visual studio installer to add in the SQL Server Data Tools. Then I have installed the SSIS extension. I am getting an error about OLEDB. More research suggests that I need the msoledbsql driver installed. However, the installer for that complains that I need a visual c++ redistributable. I downloaded that from here and installed it without issue: https://learn.microsoft.com/en-us/cpp/windows/latest-supported-vc-redist?view=msvc-170 I have in fact installed that as shown in my add/remove programs list. However, the msoledbsql installer refuses to install. It still insists that I need to install the visual c++. Can anyone advise a way forward?

At work we have an old server and we're moving everything from there to our new server. We're a very small team, and one of my team-members says without visual studio 2015 in the new server he can not transfer/create SSIS packages there. Any thoughts on this?

We have a SQL Server Database Project in Visual Studio 2015, synchronized with a production database server. And, we want to publish the project to a blank testing database server of SQL Server 2016 to create a development environment. So, we opened "Schema Compare...", and selected the source code project as the "Source" on the left, and the testing database server as the "Target" on the right. Then, we made a compare, and the diff shows a series of "+" addition actions to add entities to the blank testing database. However, the "Update" button is greyed out. And, at the buttom of the Compare, the message says: "Comparison complete. Differences detected. Project errors may caused th ecomparison to be incomplete or inaccurate. See Error List for details." And, in the Error List, we see errors of "SQL71501: User [xxx] has an unresolved reference to Login [xxx]". We guess, the "Update" button was disabled due to the build-time errors. Please point out if we got it wrong. So, we manually created those SQL Server users by a script like the following:

CREATE LOGIN [xxx] WITH PASSWORD = 'StrongPassword123', CHECK_POLICY = OFF;

Then, we tried building the project again, but got the same error as before. We are new to SQL Server Database Project in Visual Studio, and we are trying to make a development environment for upgrade testing. So, we highly appreciate any hints and suggestions. For anything, please let us know. ### Additional Information With more testing, we discovered that the Visual Studio Data-tier Application project, we also called it "SQL Server Database Project", only imports objects from the referenced database. Specifically, its "Compare" functionality will retrieve the user definitions from the reference Source database, but not the Login that the user refers to, because: - The user's definition is in context SQL Engine > Databases > the_database > Security > Users. however, - the user's referred login is in the context of SQL Engine > Security > Logins, and "Compare" does not refer to this context. For this reason, the imported source code will always fail on build anyway. With the build-time errors, there is no way to publish the project to a new blank database. We are not sure if we misunderstood something within Visual Studio, or whether it is a minor flaw. As a workaround: - Modify the settings of "Compare", in the context of "Compare > Gear sign > Schema Compare Options > Application-scoped", uncheck "Role Membership" and "Users". - Also, if any schema is owned by a custom-defined user other than the default dbo, we must change the owner to dbo. Without importing the Users, the schema ownership will cause other build-time errors. Then, the data-tier application can successfully build. We still need help because the above workaround means omitting important information in database configuration.

After recovering and re-importing a backup of a SQL Server 2019 instance none of my SSIS packages will deploy from Visual Studio. Whenever I try and deploy I get the error =================================== An error occurred during decryption. (.Net SqlClient Data Provider) ------------------------------ Server Name: SSRS02\HEALTHAWARE Error Number: 15466 Severity: 16 State: 9 Line Number: 1 I have tried to reimport the backup of the encryption key that I have through the Report Server Configuration Manager, and have run the following and have seen it succeed, so I am assuming that my master key is correct, and I have the correct decryption password for the master key. OPEN Master KEY DECRYPTION BY PASSWORD = 'xxxxxxxxxxx'; ALTER Master KEY DROP ENCRYPTION BY SERVICE MASTER KEY; ALTER Master KEY ADD ENCRYPTION BY SERVICE MASTER KEY; CLOSE MASTER KEY; GO I cannot however figure out what I need to do in order to get the SSIS Packages to deploy from Visual Studio

I need to export data from Oracle to SQL Server. To do this, I have built a dtsx package in SQL Server 2019. I am using Visual Studio 2019 (64bit) and an SSIS solution. On Oracle I use "Oracle in OraClient 11g_home1" (also 64bit). There I use the "Execute Package Task" to run the package. When I try to preview a table (any table!), I get the following message: > Arithmetic operation resulted in an overflow (System.Data) On other systems, where everything was 32bit, this worked fine. Does this have something to do with the 64bit? where is the problem?

We have a Visual Studio Database Project consisting of about 129 Tables. It is the primary database for our Internal Web Based CRM/Call Centre product, which is still under active development. We use the SSDT Publish from within VS to deploy to instances as changes are made. We develop locally via SQL Express (2016), also have a LAB environment for performance and load tests running SQL 2014, a UAT environment running 2012 and finally a deploy to production which is running SQL 2016. All environments (Except Production) the script generated on publish is very good, only does the changes. The production script does a massive amount more work. Seems to drop and recreate a lot more tables, that I know have not changed (37 tables last deploy). Some of these tables have rows in the millions, and the whole publish is taking upwards of 25mins. If I repeat the publish to production, it again drops and recreates 37 tables. The production DB does have replication which I have to disable before deployments (unsure if that's a factor). I don't understand what the Production publish always wants to drop and recreate tables even though nothing has changed. I'm hoping to get some advice as to where to look to establish why SSDT thinks these need to be re-created. using Visual Studio Professional 2017 V 15.5.5 and SSDT 15.1

SQL Server DB Projects - when created generates the SQL Users scripts, which aren't associated with any SQL Logins but only to DB, without the condition 'WITHOUT LOGIN' and so leading Build error as below, > SQL71501: User: [user1] has an unresolved reference to Login [user1]. Took some days to find this. Is there any settings we can adopt while Importing DB for creating SQL Server DB Project to include accurate Create SQL Users scripts, either with VS / ADS?

I am using a script to copy data from a subset of tables in one server to a second server, such that the second server has fewer tables than the original. Data should otherwise be identical. I recently learned there is a DB compare tool in recent versions of VS, so I installed it and restarted VS. I then pointed the tool to the two servers and ran a table compare. This returned immediately with "0 tables and/or views were compared." There seemed to be only three steps, select the two databases, select what you want to compare (tables) and then click Finish. There are no errors, dialogs, warnings or output in Output or Immediate. I know it is connecting to the two DB's because I can pop-up the table list successfully, which I couldn't when I forgot to check the trust cert. Did I miss something?

#### Links This takes up: - [Using temporary tables in SSIS flow fails - Stack Overflow](https://stackoverflow.com/questions/37945772/using-temporary-tables-in-ssis-flow-fails) - [Is it possible to use a temp table in a data flow source?](https://dba.stackexchange.com/questions/73132/is-it-possible-to-use-a-temp-table-in-a-data-flow-source) ; answer is: > 'No', since it cannot be passed without being dropped right away. *(Mind that even the working answer below cannot fix this problem of the Data Source item that does not give you a choice whether you want to close or release the connection, it always closes the connection, and with that, the temp table is dropped.)* - [Do I lose my temporary tables when changing tasks in an SSIS package? - Stack Overflow](https://stackoverflow.com/questions/25558513/do-i-lose-my-temporary-tables-when-changing-tasks-in-an-ssis-package) - [How does SSIS manage closing connections? Can I force it? - Stack Overflow](https://stackoverflow.com/questions/13733692/how-does-ssis-manage-closing-connections-can-i-force-it) - [Is it possible to use OleDbConnections with the Script Component?](https://stackoverflow.com/questions/15792632/its-possible-to-use-oledbconnections-with-the-script-component) From this, I thought I should test keeping up the connection between two DFT items so that the temporary table does not get dropped. #### Control Flow connection manager does not fix it With a connection manager for the "tempdb" database, I can make temporary tables in the Control Flow that survive the next step inside the Control Flow, see [Use Temp Table in SSIS?](https://wenleicao.github.io/Use-Temp-Table-in-SSIS/) . While you might think that [How to create a temporary table in SSIS control flow task and then use it in data flow task?](https://stackoverflow.com/a/78196761/11154841) already answers this question, I found that the answer there does not fix the problem of dropped temp tables after one step in the DFT. #### ADO.NET connection manager does not fix it I tested the DFT with a ADO.NET connection manager and the temp table was still dropped after the Script Component. #### Test setup I tested it on a Script Component that I put after another: #### Setting RetainSameConnection to True does not fix it I set RetainSameConnection to True as in the other links, but this did not fix it. #### Code tricks I tried it by not making a new connection in the second Script Component but just by acquiring it, to no avail: - In Script Component 1, both base.PostExecute(); and conn.Close(); are commented out in the hope that this keeps alive the temp table, the code is taken from [How do I create and fill a temporary table with incoming data from a Data Source item without leaving the SSIS Data Flow Task C# Script Component?](https://dba.stackexchange.com/a/338130/212659) :

public override void PostExecute()
    {
        //base.PostExecute();
        // here we would bulk copy data into the temp table
        // you may need to batch this operation or handle it differently based on your situation
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
        {
            // Now write that DataTable to the database 
            bulkCopy.DestinationTableName = "##tmpTable";
            bulkCopy.WriteToServer(dt);
        }
        //conn.Close();
    }

- In Script Component 2, the same connection is just fetched again with (SqlConnection)Connections.Connection.AcquireConnection(Transaction);:

public override void AcquireConnections(object Transaction)
    {
        base.AcquireConnections(Transaction);
        conn = (SqlConnection)Connections.Connection.AcquireConnection(Transaction);
    }

The temporary table is dropped already after the end of the first Script Component since the connection seems to be lost by default even if I do not close it. #### Quest for a trick that allows me to work with temporary tables in many DFT items and throughout the package/project I hope to find an answer that keeps alive the temporary table with some sort of trick. It must all work only from SSIS, I cannot just make the temporary table in SSMS only to keep it alive for SSIS. If the DFT drops temporary tables after leaving any DFT item, how can I keep the temporary table alive within the working connection manager, or what is the workaround for it? #### Further work I made a full scale check of the given answer and found out that I could not build a temporary table in one Script Component and read it in the next without losing the chance of passing it to a destination table at the very end. I made this a new question at [How do I feed the output arrow (Input0_ProcessInputRow()) of a second Script Component with data from a temp table of the first Script Component?](https://dba.stackexchange.com/q/339257/212659) I guess that this question was not asked yet since developers put all of the code in just one Script Component. If you do the calculations in the PreExecute() method, you *can* pass the data from the temporary tables to the output arrow to fill a regular destination table. I try to split the C# script into many C# scripts so that the data flow becomes as clear as it would be without C#. But this is not the aim of anyone else I guess. Perhaps they do not need to split up their large C# code, it makes it too complicated then. They just code everything in one go, then you do not need to ask the question.

When working with SQL files in a VB.Net project, how can the connection parameters be saved for easier development? There are SQL files in the project, to hold queries for use in the app. When one of the files is opened for editing & testing, the credentials & properties must be entered again - every time! See screenshot below. Anything in the Connect and Advanced Properties windows is lost each time the file is closed. The credentials are very long, so they must be copied & pasted, and some connection properties need to be set. To redo these every time is annoying & inconvenient. How can the credentials & properties be saved in the project? (if possible) Sidenote: I do understand credentials should not be hard-coded into the project, so that need not be discussed. This is just for development, and eventually I will harden it up so the credentials will not be in the project.

This takes up [How to debug a script component in SSIS - Stack Overflow](https://stackoverflow.com/questions/6446619/how-to-debug-a-script-component-in-ssis) which was asked for the 2008 version and is now outdated. That is why this question needs to be revived for the versions of nowadays. I put a breakpoint in two Script Components in an SSIS Data Flow Task. Only the first Script Component gets debugged, as soon as I reach the second, the script is run to its end without stopping at any breakpoint. How can I debug the second Script Component so that the debugger stops at the breakpoints there?