List All SSIS Packages Deployed On Your Integration Server

One way of checking wich SSIS packages you've deployed on your integration server and wich version build numbers they have is by running a TSQL query against the MSDB database on the server where your integration services  is running.

I found the following blogpost with a very usefull TSQL script:

http://blog.hoegaerden.be/2010/01/10/list-all-ssis-packages-deployed-on-your-integration-server/

NOTE: the query was written for SQL Server 2008. For SQL Server 2005: sysssispackagefolders => sysdtspackagefolders90 and sysssispackages => sysdtspackages90.

SSMS - Saving changes is not permitted

When you design a table in a SQL Server database and then try to make a change to a table structure that requires the table to be recreated, SSMS (SQL Server Management Studio) will not allow you to save the changes.

The error you will get is:
“Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.”

Disable “Prevent saving changes that require the table re-creation” 
In SSMS go to Tools | Options | Designers | Tables and Designers as shown in the below snippet. Under Table Options you need to uncheck “Prevent saving changes that require the table re-creation” option and click OK to save changes. Now SSMS will work like expected.

Important
Microsoft recommends that you do not work around this problem by turning off the Prevent saving changes that require table re-creation option. For more information about the risks of turning off this option, see the "More information" section in the kb article from Microsoft on : http://support.microsoft.com/kb/956176

SSMS – Change Select Top 1000 Rows or Edit Top 200 Rows

In SQL Server Management Studio (SSMS) it is possible to select the Top 1000 or edit the Top 200 rows in a table or view. When you right on a table or view in SSMS, you will see the Context menu below.

In this Context menu you can:
1. Select Top 1000 Rows
2. Edit Top 200 Rows

The idea behind this is for performance reasons. But what if you want to display all the records, or edit row 250 for example? In SSMS it is possible to change these default settings. To modify the “Select Top 1000 Rows” or “Edit Top 200 Rows”  settings do the following:

1. In SSMS click on  Tools | Options
2. Select SQL Server Object Explorer . Now you should be able to see the Table and View Options
3. To select/edit All records from a table or view, specify a value of 0 to return all rows.

SSMS Tools Pack (free)

Recently I wanted to export a configuration table with data from Microsoft SQL Server 2008 R2, through generating "INSERT INTO" scripts. Because it was a big (and frustating) search in SQL Server Management Studio (SSMS) to get this done, I went looking for a tool that could simplify this.

After some googling, I found SSMS Tools Pack. SSMS Tools Pack is an add-in for Microsoft SQL Server Management Studio (SSMS) 2005, 2008, 2008 R2, Denali CTP3 and their respective Express versions. It is a very useful tool, and free to use.

Download
Go to http://www.ssmstoolspack.com/Download

Features
Click here to view all the features of SSMS Tool Pack.

ShellRunas – Launch Programs with Different Account Credentials

ShellRunas is free utility that integrates into the Windows context menu to launch programs as a different user. It provides similar functionality to that of the command line based RunAs utility. The command-line Runas utility is handy for launching programs under different accounts, but it’s not convenient if you’re a heavy Explorer user. ShellRunas provides functionality similar to that of Runas to launch programs as a different user via a convenient shell context-menu entry.

Screenshot

At BI Architects we are using ShellRunas a lot for logging into SSMS (SQL Server Management Studio) with different credentials. If you want more information about ShellRunas or if you want to download the utility, click here