Creating a linked server called “RPT” that connect into SERVER01… Note: When generating the linked server from a script from an existing database server, the rmtpassword parameter will not be set. After the linked server has been created, go to properties of the linked server and set the security context.
Error: The filegroup "xxxx" has no files assigned to it. Tables, indexes, text columns, ntext columns, and image columns cannot be populated on this filegroup until a file is added. Fix: Sources: https://forums.asp.net/t/1397838.aspx?The+filegroup+comn_data+has+no+files+assigned+to+it+sql
This topic describes the compression of SQL Server backups, including restrictions, performance trade-off of compressing backups, the configuration of backup compression, and the compression ratio. Backup compression is supported on SQL Server editions: Enterprise, Standard, and Developer. Every edition of SQL Server 2008 and later can restore a compressed backup. Benefits Because a compressed backup... » read more
Error: No global profile is configured. Specify a profile name in the @profile_name parameter Fix: 1. Open database mail configuration by logging into SQL Server Management studio and expanding Management and right clicking on DatabaseMail, then choosing configure: 2. Choose Manage Profile Security. 3. Configure your profile to be the default profile: Sources:
Error: Connecting to the Integration Services service on the computer “xxxx” failed with following error: “Access is denied” Resolution: Open up SSMS via “Run as Administrator”
Grant EXEC permission on Data Type
I’ve tended to use filesystem which has the advantages of:- 1) Not bloating MSDB if you have thousands of packages. 2) Ease of deployment of packages – DTSX files can simply be copied into the filesystem using Windows Explorer. 3) Multi-user security – using NTFS permissions within the filesystem it’s possible to grant rights to... » read more
The SSIS Catalog database was introduced in SQL Server 2012 and prior to that users had the following three options to deploy their SSIS packages: Users could deploy the packages to a directory on the file system Users could deploy the packages to the package store which ultimately stores the packages on the file system... » read more
SSISDB Catalog Database In earlier versions of SQL Server Integration Services (SSIS), we used to keep SSIS packages either on a file share or in the MSDB database and configuration parameters such as connection strings and sensitive data either in an XML file or in a SQL Server table. The downside to this was that... » read more
In 2008R2, I would always using BIDS to deploy packages to MSDB. All permissions were then controlled through SQL Server. In 2014, I see that you can still save to the file system or MSDB, but that there is now the SSISDB that you create as an Integration Services Catalog. This method clearly allows a... » read more