Connecting to Remote SSIS Server

If you get the “Access is denied” error while connecting to remote SSIS server, you may have to perform the steps outlined in the following link

http://msdn.microsoft.com/en-us/library/aa337083.aspx

Additional steps to perform which are not in the MSDN page

1. Add the user to the Distributed COM Users group on the server
2. Restart the SSIS service after making changes
3. At the minimum, add the user to the MSDB db_dtsltduser role to edit their own packages. Check this link for more permissions on the msdb database

http://msdn.microsoft.com/en-us/library/ms141053.aspx

SSIS 2008 New Features

This is a neat article which outlines the new SSIS features in SQL Server 2008

http://www.simple-talk.com/content/print.aspx?article=541

SQL Server 2008 Top 10 T-SQL features

Here is the list of new T-SQL features that I really like in SQL Server2K8.

1.Intellisense in SQL Server Management Studio – It is easy to learn the database schema and the objects on the fly with this feature

2.Inline Variable Assignment – Inline variable initialization is possible now

DECLARE @i INT = 2

SELECT @i AS VALUE

GO

3.Compound Assigment of variables

DECLARE @i INT = 3

SELECT @i += 1

SELECT @i

GO

4.Filtered Indexes - Filtered index is used to index a portion of rows in a table. It is an optimized nonclustered index which can improve query performance and reduce index cost.

5.Table Valued Parameters – These are new parameter types in SQL Server 2008 which allows to send mulitple rows of data to a T-SQL statement or routine.


6.Row Constructors – These allow to insert multiple rows with a single INSERT statement

7.Merge statement – It is a new DML statement which allows to perform Insert/Update/Delete operations on one table using single scan.

8.New datatypes – SQL 2K8 has various new datatypes like spatial datatypes, datetime datatypes (DATE, TIME, DATETIME2) and Hierarchy datatypes. Of all FileStream Data type interests me the most (we don’t have to be creative inventing ways to store unstructured data anymore).

9.Grouping sets – This clause allows us to easily specify combinations of groupings to see different levels of aggregated data.

10.Sparse Columns – Columns created with this clause takes less space than the regular columns when it contains zero or null. These also allow us to create tables with more than 1024 columns.

SQL Server 2008 – What happened to Surface Area Configuration tool?

Have you ever wondered what happened to the Surface Area Configuration (SAC) tool in SQL Server 2008? It has been removed in SQL Server 2008. SQL Configuration Manager or SSMS can be used to configure settings, options and component features.

Here is an article from BOL

http://msdn.microsoft.com/en-us/library/cc281850.aspx