SSIS Talk

I know, this isn’t “breaking” news or anything, but what is new is the white paper detailing how Microsoft was able to achieve this record breaking speed using SSIS. Check it out below as its a very interesting read, and it may help generate some new ideas for your implementations.

http://blogs.msdn.com/sqlperf/archive/2009/03/03/an-etl-world-record-revealed-finally.aspx

Simon Sabin, fellow SQL MVP, has posted on the topic of formatting SQL.

For me, I align my queries vertically like so:

select track,
       level,
       title,
       Name,
       ss.length 

  from ConferenceSession cs 

  join session ss
    on ss.sessionId = cs.SessionId 

  join Speaker sp
    on sp.SpeakerId = ss.ownerId 

 where cs.Approved = 1
   and cs.ConferenceId = 4 

 order by length,
          title,
          cs.SessionId desc

If I have multiple join predicates, I continue the indention pattern:

select track,
       level,
       title,
       Name,
       ss.length

  from ConferenceSession cs 

  join session ss
    on ss.sessionId = cs.SessionId
   and (ss.approved  = 1
        or ss.track  = 'DBA')  

  join Speaker sp
    on sp.SpeakerId = ss.ownerId 

 where cs.Approved = 1
   and cs.ConferenceId = 4

order by length,
         title,
         cs.SessionId desc

What is your SQL formatting style?

I had a little “snafu,” if you will, regarding my domain name provider which caused this site to be unavailable for a few days over the weekend… This has been resolved….

Sorry for the inconvenience!

Happy New Year to all of the readers of SSISTalk.com!!

You can look forward to reading about the SSIS API using real world examples, a review of Itzik Ben-Gan’s new book, “Microsoft SQL Server 2008 T-SQL Fundamentals,” more blog posts about tough SSIS concepts, news from the dev team (that I can share!), and much more! Stay tuned!

From the SSIS development team, Matt Masson has posted a few blog posts on how to use the SSIS API. The posts use references to the 2008 version of SSIS, but to modify them for 2005 requires a simple change in most cases – Upgrading custom SSIS 2005 components to 2008.

You can find the blog posts here: http://blogs.msdn.com/mattm/archive/2008/12/30/samples-for-creating-ssis-packages-programmatically.aspx

Also, there is a post on using a new API framework for SSIS 2008, titled EzAPI: http://blogs.msdn.com/mattm/archive/2008/12/30/ezapi-alternative-package-creation-api.aspx

I haven’t looked into the EzAPI yet, but it certainly sounds interesting.

Let me know what you think about the new posts and if you’d like to see any thing else from the dev team and I’ll pass it along.

The MSDN & TechNet Search team have made some improvements to their search engine.

Here’s a quick walk-through….
(more…)

Please ensure that if you fall under the affected operating systems in MS08-067, patch your system immediately. Basically, if you are on Windows 2000, XP, or Server 2003, Microsoft has put you in the “critical” bucket. Vista and Server 2008 are in the “important” bucket.

In short, avoid using mapped network drives inside your SSIS package for things like file paths, control flow settings, log file locations, etc… Instead use UNC path notations. (\\server\share\directory)

Why? Well, if you’re always running an SSIS package by hand using dtexec.exe, or through Business Intelligence Development Studio (BIDS), dtexecui.exe, etc… you don’t really have to worry about this. However, if you’re running the package via a scheduler such as SQL Agent, you’ll run into perceived permission problems, or reports that files cannot be found. The issue with schedulers such as Agent is that they run under a “service account” that does not have access to mapped network drives.

Mapped network drives are set up for your user-session when you log on to a computer. Even if you log in with the same account that the SQL Agent service uses, the service will still not have access to any drives you map because the service does not have access to the drives in *your* session.

This is a common issue that I see with users who are trying to repoint the default behavior of SSIS to a network share, or that are trying to read/write files on a share.

SQL Server 2008 brings with it the ability to change the color of the status bar in Management Studio per your choosing. This is especially good to distinguish between production and non-production machines, or if you simply wish to have a different color other than the default gray.

However, the only way to do this is in the properties of a server when registering it under Registered Servers (Ctrl-Alt-G). Add a new server and in the connection properties, select the color you wish for this particular server.

I have a short video demonstrating it:

• Flash video
• iPod video

For those of you that have access to the SQL Server 2008 distribution via the various channels, please pay attention to the Microsoft KB article which will likely apply to your installation experience until Visual Studio 2008 (Orcas) SP1 has shipped: http://support.microsoft.com/kb/956139

Basically, if you want to install SQL Server 2008, go ahead. If you want to install SSIS, SSRS, SSAS, and a few other tools, you’re going to have to wait until VS 2008 SP1 has shipped and is installed on your machine before going through the SQL Server 2008 setup. Also note, that the warning in the KB article appears quite late in the SQL Server 2008 setup process, so you have been warned!