Sutha's SQL BI Blog

Moving my blog site - beeii

I have decided to move my blog to http://www.beeii.com/

Thanks
Sutha

Dependency Analyser

I have been watching 3 videos put together by SSIS team on Dependency Analyser. I am happy Microsoft have made an effort to do something about it. I have been asking for this feature since SQL 2005 TAP. Still a long way to go before they catch up with competetion.

It looks like it currently supports SSIS and SQL dependencies. Release note states its still in development and support only limited sets of tasks. You still would not know the impact of dropping a column on cubes, reports etc. I hope that changes by the time RTM comes out.

I am struggling to understand why it is impossible to do it across the solution. Ideally if you have a BI solution which holds DB, SSIS, SSAS, SSRS & PPS projects I would like to right click and execute a dependency analyser, stating what impact would have if I drop / rename a specific column.

Has anyone else looked at it and what are your thoughts?

Thanks
Sutha

Denali SSIS Videos

There are number of videos posted on SSIS team blog regarding SSIS enhancements and features in Denali.

Thanks
Sutha

Denali AdventureWorks OLTP & DW Data Files

You can download the AdventureWorks OLTP and DW data files from here.

Sutha

Analysis Services – Roadmap for SQL Server “Denali” and Beyond

Following Chirs's blog entry, TK has blogged AS Roadmap.

Analysis Services – Roadmap for SQL Server “Denali” and Beyond.

Both entries make an interesting reading.

Sutha

No More DTSConfig File

SSIS Package Configuration option has disappeared (off BIDS - SSIS menu) in Denali.

This has been replaced by great feature called "Project Parameters".

Jamie got a blog posting on Parameters with all the details.

Sutha

What’s Coming Next In SSIS

Matt Mason & Steve Swartz from MS have presented the above topic at SQLPass and TechEd Europe.

You can download the slides from TechEd and watch them now from here.

Steve did present SSIS 2008 Tips & Tricks at TechEd

Thanks
Sutha

Denali SSIS Designer Enhancements

SSIS seems to have had a huge make over in Denali.

Some of the enhancements are:

1. Connection Assistant (Some limitations exist. Check Release Notes)
2. UI Changes. All components got new look.
3. Undo / Redo Support (About time).
4. Custom / Favourites Tool Box

Current CTP runs VS2008 BIDS, but I am sure it will be upgraded to VS2010 by the RTM is out. I am not 100% about this.  

Sutha

Project / Package Protection Level

Packages inherit Protection Level from SSIS Project properties.

Default protection level is set to "EncryptSensitiveWithUserKey" at Project level, therefore packages will inherit the same protection level.

If you change the package protection level to "DontSaveSensitive" and try to build the project you will get the following error message.
Error 1 Project consistency check failed. The following inconsistencies were detected:  Package.dtsx has a different ProtectionLevel than the project.   0 0 
 
SSIS Project and Packages need to have same Protection Level before the project can be built successfully.

Thanks
Sutha

Denail SSIS - Will NOT execute without deployment file

Historically we develop the package and execute it to test. For example I just had an empty DFT on the package.

When you click on Execute you will get the message "There were build errors. Would you like to continue and run the last successful build?".

If you say "Yes", you get the following error message.
A project deployment file (.ispac extension) is required in order to execute containers or packages. Build the project and try again. (Microsoft.DataTransformationServices.VsIntegration)

To fix this you have to "Build" the project.Once the project is Built successfully you can execute the package. You have to re-build the solution successfully everytime you add a new package.

You can see how to Build a solution on Jamie's Blog.

Sutha