SSIS Talk

For those of you that wish to execute maintenance plans on a server and do not wish to have SSIS installed on said server, you will run into issues if you are on an RTM version of 2008 (version 10.0.1600).

The specific error message is “The SQL Server Execute Package Utility requires Integration Services to be installed by one of these editions of SQL Server 2008: Standard, Enterprise, Developer, or Evaluation. To install Integration Services, run SQL Server Setup and select Integration Services. The package execution failed. The step failed.”

This constraint has been lifted in Service Pack 1 and Cumulative Update 3, each. See: http://support.microsoft.com/kb/961126/

Starting with SQL Server 2005 RTM and through the latest SQL Server 2008 build at the time of this writing, there is a pretty nasty bug with respect to the COUNT(expression) function and its rule that when using an expression, it filters out NULLs in its count results.
(more…)

I just wanted to help dispell the rumors that some have about the Bulk Insert task in SSIS. This task is NOT marked for deprecation. It will be around for quite a while. At this time, rest assured that if you are using the Bulk Insert task, you will be supported for quite some time.

Please visit http://www.codeplex.com/SQLSrvIntegrationSrv/ for a nice collection of SSIS samples ranging from XML destinations to BizTalk integrations to EzAPI. Please be sure to visit the Integration Services CodePlex site if you haven’t already.

Thanks to my friend, Doug, we now know the top list of SSIS knowledgebase articles! Thanks Doug!

http://dougbert.com/blogs/dougbert/archive/2009/03/17/find-the-top-knowledge-base-articles-for-ssis.aspx

Fellow MVP Aaron Bertrand has blogged about the MRU (Most Recently Used) server list in SSMS under SQL Server 2008 and how hard (and unsupported) it is to clean it up versus how it was under SQL Server 2005. Please give that a read and head over to Connect to vote for his suggestion as you see fit.

(it’s easy [but still undocumented] under SQL Server 2005 – just delete the mru.dat file…)

Connect item: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=424800

Just a quick tip… If you’re having issues debugging the Execute Process Task, ensure that you have a variable created and mapped to each of the process output properties of the Execute Process Task. That is, the StandardOutputVariable and the StandardErrorVariable properties. The StandardOutputVariable will capture any standard output of the process (the text output normally displayed on the screen). The StandardErrorVariable will capture any error output from the specified process.

Doing the above and then setting an OnPostExecute breakpoint on the Execute Process Task, you can use the Locals window to inspect the value of the variables after the process was executed, but before SSIS clears out their values once you’re done debugging.

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!