SSIS Talk

Here are some highlights of things that will be offered with SQL Server 2008:

• Activity Monitor for the DBA: Finally a way to see, in an easy to use UI, information related to active sessions, wait states, file I/O, long running queries, etc… Oh, and you can launch Profiler from here as well.
• Object Search: Users can now enter full or partial search strings to match on in the current database and results will be displayed in the new Object Explorer Details pane.
• Intellisense: We all know what this is… It’s coming to Management Studio to aid in writing queries with the red squiggles and all!
• Customizable Tabs: The query editor’s results can now have their tabs set with customized names
• Launch Profiler: The option is now available to launch Profiler from within a query editor results window and it will automatically tie Profiler to the SPID of the query.
• Permissions editing: The screens have been reworked to reduce the amount of clicks to set permissions
• Performance Studio: A new offering that tracks historical performance metrics from SQL Server and the operating system by storing results in a repository that offers drill-through reporting.
• Register servers once in a central location
• Management Studio adds a new GUI to manage/create table partitions.
• And finally (as far as my list and this article is concerned)….. A T-SQL Debugger in Management Studio.

Microsoft has communicated a bug in all CTPs of SQL Server 2008 that is caused by “today”, February 29th, 2008:

From Microsoft:
We have recently discovered an issue with SQL Server 2008 CTPs that result
in SQL Server 2008 not starting or installing on Feb 29 GMT only. We
recommend that you do not run, install or upgrade this CTP on Feb 29 GMT to
minimize any impact in your environment. You can install starting on March 1
GMT. If you have an immediate issue that cannot wait until march 1st GMT
contact sqlbeta@microsoft.com before taking any further steps.

UPDATE: This bug is no longer in effect as we are now onto March 1st, 2008. For what it’s worth, the SQL team have checked the bug fix into the SQL Server 2008 code stream.

The latest and greatest public CTP of SQL Server 2008 has been released. Visit http://connect.microsoft.com/sqlserver to get it.

Also, when visiting, please enroll in the Bug Bash contest for your chance to win, among other prizes, an Xbox 360.

Matt Masson from the SSIS team has posted some new/improved features of SSIS in SQL Server 2008. Check it out here.

A bit late in reporting this, but Matt has added a part two: http://blogs.msdn.com/mattm/archive/2008/01/22/what-s-new-in-sql-server-2008-for-ssis-part-two.aspx

So, one of the pain points in SSIS is that the Lookup Component performs CaSE sensitive matching, as well as retaining any trailing spaces that may be in the data. So be forewarned that when using the lookup component, it may behoove you to use a derived column upstream to TRIM([SSISColumn]) your data to get rid of any leading/trailing whitespaces and to perform the same (if needed) on the SQL Server side via an SQL statement - ltrim(rtrim(SQLServerColumn)). Also, if you have differing CaSEs among the incoming data and the reference data, you may need to convert one or the other, or both to UPPER/lower case before performing the lookup.

If you’d at least like to see CaSE INsensitive searches, please visit my submission and lets see if we can get this feature added to SSIS in SQL Server 2008.

One workaround for you, should you want to use it, is to use the Fuzzy Lookup Component instead. This has its own problems, as I’ve made mention to in the above feature request submission.

Edit: Jamie Thomson submitted an identical request before me but I’ve made reference to that submission in mine. The idea was to get it in SSIS 2008. Never-the-less, Michael Entin claims this feature request has been dropped due to lack of time.

The November CTP (CTP5) has been released. Go get it and start looking into some major new features added to this release.

Officially, the only change for SSIS are to the lookup components. I’ll have to get my hands dirty to see if there are any other changes that they didn’t comment on. One thing to note, is that C# is the default language type for SSIS Script Tasks/Components in SQL Server 2008.

I’m looking forward to some non-SSIS changes, though, like T-SQL IntelliSense, Transparent Data Encryption, and FILESTREAM support.

Many users have issues when trying to use a stored procedure in an OLE DB Source component. Here are some tips:

• Ensure that there is a final select statement at the end of the stored procedure to ensure the correct resultset is returned
• At the top of the stored procedure as part of the procedure code, add the following line: SET NOCOUNT ON
• At the top of the OLE DB Source SQL statement and before you issue the EXEC YourSproc line, add the following: SET FMTONLY OFF.

This should ensure that when you click on the column mapping tab, the columns are correctly displayed.

Also another approach is to use a table variable in the stored proc. Then at the end, select from that table variable.

Jamie Thomson has posted four SSIS lookup component feature requests that have been recently closed as “fixed” by Microsoft. Hopefully these will make it into the next CTP of SQL Server 2008. Take a look because they all have very high potentials to increase your productivity and useability of SSIS.

See them here: http://blogs.conchango.com/jamiethomson/archive/2007/08/21/SSIS_3A00_-Big-improvements-to-Lookup-in-SQL-Server-2008.aspx

The July CTP for SQL Server 2008 (Katmai) has been released.

At the moment, in the upper-right hand corner it states that the June CTP is the current version, but if you click on it, you’ll get to the July release.

Over here, Oren posted a list of 15 issues with SSIS. Some are founded, most are not. Read on for more:

(more…)